SQL Tips by Namwar Rizvi

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.

April 5, 2009

How to create Custom Database Role and Manage its permissions

Filed under: How To,Query,SQL Server 2005,SQL Server 2008,TSQL — namwar @ 8:50 PM
Tags: , , ,

Custom database roles is the best way to manage object permission for the given database in structured way. Following script will demonstrate the steps necessary to create a new role and manage object permissions through it

--- Step 1 -------- Create Role ------------
USE [MyTestDB]
GO
CREATE ROLE [TestRole1] AUTHORIZATION [dbo]
Go

--- Step 2 -------- Assign Permissions to role  ------------
USE [MyTestDB]
GO
GRANT ALTER,EXECUTE, SELECT,INSERT,UPDATE,DELETE
ON SCHEMA ::dbo
TO TestRole1
Go

USE [MyTestDB]
GO
GRANT BACKUP DATABASE,BACKUP LOG To [TestRole1]
Go

--- Step 3 ---- Add user to our role --------------
USE [MyTestDB]
GO
EXEC sp_addrolemember N'TestRole1', N'SPUser7'
GO

-- ******  Create user *********************

--- Step 1 ---- Create Windows Login For SQL Server ------------
USE [master]
GO
CREATE LOGIN [TestDomain\SPUser7] FROM WINDOWS WITH DEFAULT_DATABASE=[MyTestDB]
GO

--- Step 2 ---- Create Database User for our database --------------
USE [MyTestDB]
GO
CREATE USER [SPUser7] FOR LOGIN [TestDomain\SPUser7]
GO

January 12, 2009

TSQL Function to convert decimal to Hex, Octal or any other base

Frequently I see the questions in newsgroups about a function to convert integer value to other bases like base 2 (binary), base 8 (octal) and base 16(hex). Following TSQL function, which was orginally mentioned by Itzik Ben-Gan in his book Inside Microsoft SQL Server 2005:TSQL Querying, provides you the ability to convert a given integer into any target base. I have just updated the function with more meaningful names and added some comments to clear the logic.

Here it is:

USE [AdventureWorks]
GO;

IF EXISTS
(
SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[fn_decToBase]')
AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')
)
DROP FUNCTION [dbo].[fn_decToBase]
Go
Create function [dbo].[fn_decToBase]
(
@val as BigInt,
@base as int
)
returns varchar(63)
as
Begin
/* Check if we get the valid base */
If (@val<0) OR (@base < 2) OR (@base> 36) Return Null;

/* variable to hold final answer */
Declare @answer as varchar(63);

/* Following variable contains all
possible alpha numeric letters for any base
*/
Declare @alldigits as varchar(36);
Set @alldigits='0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'

/* Set the initial value of
final answer as empty string
*/
Set @answer='';

/* Loop until your source value is greater than 0 */
While @val>0
Begin
Set @answer=Substring(@alldigits,@val % @base + 1,1) + @answer;
Set @val = @val / @base;
End

</code>

<code>/* Return the final answer */
return @answer;
End

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

January 2, 2009

Two quickest ways to find file information of a given database

If you want to check the name of the files and their respective sizes for the given database then following are two quick tsql scripts:

1. Use sp_helpfile

This stored procedure returns the file information for the selected database e.g.

USE AdventureWorks;

GO

EXEC sp_helpfile;

GO

It will return the resultset similar to following

sp_helpfile

By default, this view returns a column called size but this is actually the size in 8KB pages therefore, to get the exact size in KB, as returned by sp_helpfile, multiply it by 8 as mentioned in above query.

November 10, 2008

Generate rows for missing dates

Filed under: SQL Server 2005,tips,TSQL — namwar @ 9:16 PM
Tags: ,

For all those reports or queries where you want to list a row for each day such that if no data is available for that day then you need zero against that date then you can use the Calendar table for the missing dates.

For generating a calendar table efficiently, you can refer to my earlier post here

Assuming you have a calendar table, use the following query to generate the required result

Use tempdb
Go

/*Create a sample table*/
drop table sampleData
go
Create table sampleData
(
    id int IDENTITY(1,1) NOT NULL,
    transactionDate datetime,
    Qty int

)

Insert into sampleData (transactionDate,Qty) values ('20081101',2)
Insert into sampleData (transactionDate,Qty) values ('20081101',3)

Insert into sampleData (transactionDate,Qty) values ('20081104',1)
Insert into sampleData (transactionDate,Qty) values ('20081104',2)
Go

Select C.CalendarDate,Isnull(A.TotalQty,0) as TotalQty
from
Calendar C
left outer join
(
    Select transactionDate,Sum(Qty) as TotalQty
    from sampleData
    group by transactionDate
) A
on C.CalendarDate=A.transactionDate
Where C.CalendarDate between '20081101' AND '20081130'

September 14, 2008

Quick technique to retrieve alphanumeric values by using regular expression in TSQL

Filed under: Query,tips,TSQL — namwar @ 12:33 AM
Tags: , ,

If you have a column in a database which contains numeric, alphanumeric and only aplha values and you just want to find all those rows which have alphanumeric values i.e which contains atleast one numeric alongwith anynumber of alphabets then you can use the power of Like operator and a little regular expression technique. Following is a query for Name column in Production.Product table of AdventureWorks database. This query retrieves all rows which contains alphanumeric values:

Use AdventureWorks

Go

Select * from Production.Product Where Name like ‘%[0-9]%’

Go

August 26, 2008

Deleting ununsed primary keys

Filed under: Query,tips,TSQL — namwar @ 10:35 PM
Tags: , ,

Sometimes we need to make sure that all keys in the primary key table has atleast one corresponding row in child table or in other words every key has been used atleast once as a foreign key. This is a typical scenario we face during data import where data integrity can not be guarnteed 100% due to various reason. Following script demonstrate a simple technique to delete all those rows in the master table which are not used in child table.

Use tempdb;

/* Create sample tables and insert sample data */

Create table tblMaster

(

masterId int,

masterName varchar(50)

)

Go

Create table tblChild

(

childId int NOT NULL IDENTITY (1, 1),

masterId int,

childName varchar(50)

)

Go

Insert into tblMaster (masterId,masterName) values (1,‘Master 1’);

Insert into tblMaster (masterId,masterName) values (2,‘Master 2’);

Insert into tblMaster (masterId,masterName) values (3,‘Master 3’);

Insert into tblMaster (masterId,masterName) values (4,‘Master 4’);

Go

Insert into tblChild (masterId,childName) values (1,‘Child 1’);

Insert into tblChild (masterId,childName) values (1,‘Child 2’);

Insert into tblChild (masterId,childName) values (1,‘Child 3’);

Insert into tblChild (masterId,childName) values (2,‘Child 4’);

Go

/* Master table before deletion */

Select * from tblMaster

Go

/* Delete unused rows */

Delete tblMaster

where

not exists

(

Select masterId

from tblChild

where

tblChild.masterId=tblMaster.masterId

)

Go

/* Master table after deletion */

Select * from tblMaster

Go

/* Clean up */

drop table tblMaster;

Go

drop table tblChild;

Go

July 18, 2008

How SQL Server Index Statistics works – Part 2

Filed under: Information,SQL Server 2005,TSQL,Tuning — namwar @ 11:36 PM
Tags: ,

In my last article,

How SQL Server Index Statistics works – Part 1

I described the concept behind SQL Server index statistics. Today, I will present the real world example from AdventurWorks database. Launch Management Studio and execute the following script

Use AdventureWorks

Go

DBCC SHOW_STATISTICS (‘Sales.SalesOrderDetail’,‘IX_SalesOrderDetail_ProductID’ )

Go

You will get the result similar to this:

The fist resultset tells you about the basic details of statistics collected for the index IX_SalesOrderDetail_ProductI You can check when was the statistics last updated. If it is too old then you need to update the statistics.

Second resultset guides you about the density of frequent values. Frequent value means the value which appeared more than once in the step. The All density considers frequent values and Density considers non-frequent values only.

Third resultset is actually the most intersting section. This is the histogram of values of the indexed column of the index or the first column of the composit index. Description of its columns is as follows:

 

Column

Details

RANGE_HI_KEY

Upper bound value of a histogram step i.e. the highest value in the step. The first step is the lowest value for the column in the table.

RANGE_ROWS

Number of rows from the sample that fall within a histogram step, excluding the upper bound. By definition, this must be zero in the first sample.

EQ_ROWS

Number of rows from the sample that are equal in value to the upper bound of the histogram step.

DISTINCT_RANGE_ROWS

Number of distinct values within a histogram step, excluding the upper bound.

AVG_RANGE_ROWS

average number of duplicate values within a histogram step, excluding the upper bound. This is defined as:

(RANGE_ROWS / DISTINCT_RANGE_ROWS

for DISTINCT_RANGE_ROWS > 0)

By using this histogram, SQL Server determines the expected no. of rows to be returned for a given query. For example: in the above if you see the highlighted section, you will noticed that in the 80th step we have

RANGE_HI_KEY=819

RANGE_ROWS=218

EQ_ROWS= 195

Firstly, it means that for range of ProductID>815 and ProductID<819 we will get 218 rows. This can be verified by executing the following query:

Select count(*) from Sales.SalesOrderDetail where ProductID>815 and ProductID<819

Result is 218, which confirms the RANGE_ROWS statistics value

Secondly, it also means that for ProductID=819 we will get 195 rows. This can be verified by executing the following query:

Select count(*) from Sales.SalesOrderDetail where ProductID=819

Result is 195, which confirms the EQ_ROWS statistics value.

I hope by seeing these examples, you have got the good idea how SQL Server forecasts the total number of rows to be returned and how they are used in analyzing the cost of index usage.

Next Page »

Create a free website or blog at WordPress.com.