SQL Tips by Namwar Rizvi

March 29, 2010

Search anything in your Database schema right from SSMS

In my one of earlier articles here I wrote about a way to find any value in any database column. Today, I found a great free tool, from RedGate, to find dependencies of any object. The tool “SQL Search” is available here. This can be used for impact analysis, improving the performance by replacing “Select *” with actual column name etc.

The best part is that it installs as an SSMS plugin, making it very easy to use it.

I recommend every SSMS user to give it a try.

August 30, 2009

How to Query Active Directory (AD) from SQL Server

There can be a scenario where you will want to query Active Directory, directly from your stored procedure. This can be achieved by creating a linked server to your target Active Directory and then querying it through OPENQUERY functionality. Following is an example to achieve this:

Step 1: Add Linked Server for Active Directory

sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource'

Step 2: Query the Active Directory

SELECT * FROM OpenQuery
(
ADSI,   --Name of the linked server for Active directory, created in step 1
'SELECT title, displayName, sAMAccountName, givenName, telephoneNumber, facsimileTelephoneNumber, sn   --Fields you want to retreive
FROM ''LDAP://DC=TestDomain''    --Name of your Active Directory Domain
where objectClass = ''User'''         --Type of objects you want to query e.g. User, Person etc...
)

NOTE: The order in which columns will return will be exactly reversed of what you mentioned in your query. For example, in above query we have mentioned 
title, displayName…….,facsimileTelephoneNumber, sn but the resultset will return as
sn,facsimileTelephoneNumber……,displayName,title.
There is no reason of this behaviour and it is just how Active Directory OLEDB Provider works.

January 8, 2009

Search all columns of all tables for a particular value

Filed under: How To,Object Search,tips,TSQL — namwar @ 7:30 PM
Tags: , , , , ,

Several times I have came across with the question that how can I search all columns of all tables in a SQL Server database for a particular value. Unfortunately, you can not perform this query directly by any already provided system table or object. 

There are some fundamental points to remember:

 

  1. You have to iterate the list of tables and for each table you have to iterate each column and perform an exist query against it. 
  2. You will need three types of queries due to the data types i.e. string, number and date and time.
I have found an excellent article here which provides a good explanation along with the necessary sql script. I would recommend every reader to read this article.

 

January 3, 2009

How to find relationship between tables

As a DBA or even as a SQL Server Programmer, you may need a quick script to find relationship between tables. I found a useful script to do that in a SQL Server newsgroup. With some minor update, here is the script which lists the name of the parent table, child table, Referred table, foreing key table and name of the foreign key constraint. I hope you will find it useful. All credit goes to original author.

Select

object_name(rkeyid) Parent_Table,

object_name(fkeyid) Child_Table,

object_name(constid) FKey_Name,

c1.name FKey_Col,

c2.name Ref_KeyCol

From

sys.sysforeignkeys s

Inner join sys.syscolumns c1

on ( s.fkeyid = c1.id And s.fkey = c1.colid )

Inner join syscolumns c2

on ( s.rkeyid = c2.id And s.rkey = c2.colid )

Order by Parent_Table,Child_Table

September 3, 2007

Conditionally add a column in the table

Sometimes we need to add a column in an already existing table but we need to make sure that this column should not be present already. This conditional column creation requires a way to check column existence in the given table. SQL Server 2005 INFORMATION_SCHEMA.COLUMNS view is an standard way to do this. Following script gives you the ability to conditionally add a column in the table, if that column is not already present.

Use tempdb
Go

Create table testTable (id int, city varchar(50))
Go

—Check for column existence
If not exists
(
Select * from INFORMATION_SCHEMA.COLUMNS
Where
TABLE_CATALOG=‘tempdb’
AND TABLE_SCHEMA=‘dbo’
AND TABLE_NAME=‘testTable’
AND COLUMN_NAME=‘id’
)
Begin
–Add new column
ALTER Table dbo.testTable Add id int Null
Print ‘Column added successfuly’

End
Else
Begin

–Column already exists. leave the table as it is.
Print ‘Column already exists.’

End
Go

Drop table testTable
Go

August 18, 2007

Finding tables which have Identity column

Filed under: Information,New Features,Object Search,SQL Server 2005,tips — namwar @ 11:04 PM

Following is a quick trick to find all the tables which have identity column.

USE Adventureworks;
GO

SELECT SCHEMA_NAME(schema_id) AS [Schema],name AS [Table]
FROM sys.tables
WHERE OBJECTPROPERTY(object_id,‘TableHasIdentity’) = 1
ORDER BY [Schema], [Table];
GO

Please note that if you want to find all those tables which do not have identity column then just change the where clause as follows:

WHERE OBJECTPROPERTY(object_id,‘TableHasIdentity’) = 0

June 12, 2007

Retrieve current user permissions in SQL Server 2005

If you want to retrieve the permissions available for the currently logged in user then you can use the function called fn_my_permissions in SQL Server 2005. Following are examples of its usage:

Use AdventureWorks
Go

—Return Caller’s permission on the current server
SELECT * FROM fn_my_permissions(NULL, ‘SERVER’);
Go

—Return Caller’s permission on the current database
SELECT * FROM fn_my_permissions(NULL, ‘DATABASE’);
Go

—Return Caller’s permission on the given object
SELECT * FROM fn_my_permissions(‘Sales.vIndividualCustomer’, ‘OBJECT’);
Go

For further details please refer to Technet help here

May 28, 2007

Retreive current user permissions in SQL Server 2005

If you want to retrieve the permissions available for the currently logged in user then you can use the function called fn_my_permissions in SQL Server 2005. Following are examples of its usage:

Use AdventureWorks
Go

—Return Caller’s permission on the current server
SELECT * FROM fn_my_permissions(NULL, ‘SERVER’);
Go

—Return Caller’s permission on the current database
SELECT * FROM fn_my_permissions(NULL, ‘DATABASE’);
Go

—Return Caller’s permission on the given object
SELECT * FROM fn_my_permissions(‘Sales.vIndividualCustomer’, ‘OBJECT’);
Go

For further details please refer to Technet help here

May 8, 2007

Retrieving DDL of SQL Server objects

Filed under: Object Search,Query,TSQL — namwar @ 10:18 PM

Following is just a quickest and simplest way to retrieve DDL or the SQL required to create a database object:

Select
OBJECT_Definition(Object_ID(
dbo.uspGetBillOfMaterials’))

Just replace “dbo.uspGetBillOfMaterials” with your required object name like table, stored procedure etc.

April 26, 2007

Search columns in SQL Server 2005 database

Filed under: Object Search,Search columns,SQL Server 2005,TSQL — namwar @ 11:27 PM

Since SQL Server 2005 Management Studio lacks the Object Search feature, here is the simple query to find any column in a database

Select O.name objectName, C.name ColumnName from sys.columns C inner join sys.objects O ON C.object_id=O.object_idwhere C.name like ‘%ColumntoFind%’order by O.name,C.name

This query works for SQL Server 20005. Just replace “ColumnToFind” with your required column name.

Blog at WordPress.com.