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%'
inner join sys.procedures p on p.object_id = c.object_id
p.name like '%name_of_proc%'
routine_name like '%name_of_proc%'
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.
To get all stored procedures related to a table:
SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%tablename%'
SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%tablename%'
Here’s a code snippet to remove duplicate rows in a table:
WITH CTE (Column1, Column2, DuplicateCount)
ROW_NUMBER() OVER(PARTITION BY Column1,Column2 ORDER BY Column1) AS DuplicateCount
WHERE DuplicateCount > 1
More on Common Table Expressions(CTE) can be found here:
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.