Category: SQL

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: or or Advertisements

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 […]

Get all stored procedures related to a table

To get all stored procedures related to a table: Option 1: SELECT DISTINCT FROM syscomments sc INNER JOIN sysobjects so ON WHERE sc.TEXT LIKE ‘%tablename%’ Option2: SELECT DISTINCT, o.xtype FROM syscomments c INNER JOIN sysobjects o ON […]

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 […]

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 AS ColName, AS TableName FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id WHERE […]