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

Using a .NET assembly in SQL Server 2008

Many times you fnd yourself in a situation where you have to do something that seems too complex to implement in TSQL or you just want to use some feature from the .NET framework, or you already have something implemented in an assembly and you want to use it in your stored procedure or function. While TSQL is very powerfull and there are lots of things you can accomplish the answer to the above is to just use a .NET assembly in your TSQL code.

Continue reading “Using a .NET assembly in SQL Server 2008”

Using a .NET assembly in SQL Server 2008