Find all stored procedures containing cursors

SELECT Object_name(object_id) as [Procedure Name],
       Object_definition(object_id) as [Definition]
FROM   sys.procedures
WHERE  Object_definition(object_id) LIKE '%cursor%'
Order by [Procedure Name]


CursorSPs
Advertisements

Create an Active Directory Parser based on Organisational Units (OUs)

LDAP queries look like this:

("CN=Dev-UK,OU=Distribution Groups,DC=gp,DC=gl,DC=google,DC=com");

What it means:

  • CN = Common Name
  • OU = Organizational Unit
  • DC = Domain Component

These are all parts of the X.500 Directory Specification, which defines nodes in a LDAP directory.

Each = pair is a search criteria.

With the query

("CN=Dev-UK,OU=Distribution Groups,DC=gp,DC=gl,DC=google,DC=com");

In effect the query is:

From the com Domain Component, find the google Domain Component, and then inside it the glDomain Component and then inside it the gp Domain Component.

In the gp Domain Component, find the Organizational Unit called Distribution Groups and then find the the object that has a common name of Dev-UK.

In order to list all users in this OU, you can write the following function:

public ArrayList EnumerateOU(string domainController, string OuDn, string username, string password)
 {
 ArrayList alObjects = new ArrayList();
 try
 {
 DirectoryEntry directoryObject = new DirectoryEntry("LDAP://" + domainController + "/" + OuDn);
 directoryObject.Username = username;
 directoryObject.Password = password;
 directoryObject.AuthenticationType = AuthenticationTypes.Secure;
 
 foreach (DirectoryEntry child in directoryObject.Children)
 {
 string childPath = child.Path.ToString();
 alObjects.Add(childPath.Remove(0, 7));
 //remove the LDAP prefix from the path
child.Close();
 child.Dispose();
 }
 directoryObject.Close();
 directoryObject.Dispose();
 }
 catch (DirectoryServicesCOMException e)
 {
 Console.WriteLine("An Error Occurred: " + e.Message.ToString());
 }
 return alObjects;
 }

An alternative to the Count(*) function.

Sometimes rapid code development doesn’t always produce the most efficient code.
Take the age old line of code

SELECT COUNT(*) FROM MyTable.

Obviously this will give you the row count for a table, but at what cost?

Doing any SELECT * from a table will ultimately result in a table or clustered index scan.

ExecutionPlan

Turning on STATISTICS IO on reveals 4534 logical reads just to return the row count of 1,133,242.

Table 'tblERPInvoiceData'. Scan count 1, logical reads 4534, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Starting with SQL Server 2005, Microsoft introduced a DMV, sys.dm_db_partition_stats, that provides you with the same information at a fraction of the cost. It requires a little more coding, but once you turn on STATISTICS IO, you will see the performance benefit.

USE [db_name];
SELECT
s.name AS 'SchemaName'
,o.name AS 'TableName'
,SUM(p.row_count) AS 'RowCount'
FROM sys.dm_db_partition_stats p
JOIN sys.objects o ON o.object_id = p.object_id
JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE p.index_id < 2 AND o.type = 'U'
AND s.name = 'dbo'
AND o.name = 'tblERPInvoiceData'
GROUP BY s.name,o.name
ORDER BY s.name,o.name;
GO

Since we’re querying a DMV, we never touch the base table. We can see here we only need 16 logical reads to return the same row count of 1,133,242, and the tblERPInvoiceData¬†table is nowhere in our execution plan.
Capture

Table 'sysidxstats'. Scan count 1, logical reads 9, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysschobjs'. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysclsobjs'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

By using the DMV, we have improved the query performance and reduced the total I/O count by nearly 100%. Another added benefit of using the DMV, is we won’t need locks on the base table and therefore will avoid the possibility of blocking other queries hitting that table.