Find Stored Procedure by Name

There are several ways to find a stored procedure in the server by name: we can query sys.procedures, syscomments, or information_scema.routines. Respective examples would be:

select * from   sys.procedures
where    name like '%name_of_proc%'

or

select text 
from 
    syscomments c
    inner join sys.procedures p on p.object_id = c.object_id
where 
    p.name like '%name_of_proc%'

or

select * 
from
    information_schema.routines
where
    routine_name like '%name_of_proc%'
Find Stored Procedure by Name

Sql Server: datetime vs datetime2 recap

The MSDN documentation for datetime recommends using datetime2.

datetime2 has larger date range, a larger default fractional precision, and optional user-specified precision. Also depending on the user-specified precision it may use less storage.

Furthermore, datetime2 has a date range of “0001 / 01 / 01” through “9999 / 12 / 31” while the datetime type only supports year 1753-9999. If your need is precesion, datetime2 can be more precise in terms of time; datetime is limited to 3 1/3 milliseconds, while datetime2 can be accurate down to 100ns.

Both types map to System.DateTime in .NET – no difference there.

Sql Server: datetime vs datetime2 recap

Get all stored procedures related to a table

To get all stored procedures related to a table:

Option 1:

 SELECT DISTINCT so.name  
 FROM syscomments sc  
 INNER JOIN sysobjects so ON sc.id=so.id  
 WHERE sc.TEXT LIKE '%tablename%'  

Option2:

 SELECT DISTINCT o.name, o.xtype  
 FROM syscomments c  
 INNER JOIN sysobjects o ON c.id=o.id  
 WHERE c.TEXT LIKE '%tablename%'  
Get all stored procedures related to a table

Sql Server: Delete duplicate rows with CTE

Here’s a code snippet to remove duplicate rows in a table:

 WITH CTE (Column1, Column2, DuplicateCount)  
 AS  
 (  
 SELECT Column1,Column2,  
 ROW_NUMBER() OVER(PARTITION BY Column1,Column2 ORDER BY Column1) AS DuplicateCount  
 FROM TheTable  
 )  
 DELETE  
 FROM CTE  
 WHERE DuplicateCount > 1  
 GO  

More on Common Table Expressions(CTE) can be found here:

http://msdn.microsoft.com/en-us/library/ms190766(v=sql.105).aspx

Sql Server: Delete duplicate rows with CTE

Sql Server: Get all tables containing column with a specified name

A little helper query I came accross, very useful if you want to find all the tables that contain a specific column:

SELECT c.name AS ColName, t.name AS TableName
FROM sys.columns c
    JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE '%ColumnName%'

 

What it basically does is that it will do a join on sys.columns and sys.tables to get all tables that contains that particular column in your Sql Server instance.

Sql Server: Get all tables containing column with a specified name