SQL queries

Search table by column name

1
2
3
4
5
SELECT t.name TableName,c.name ColumnName
FROM SYS.TABLES t
INNER JOIN SYS.COLUMNS c
ON t.OBJECT_ID=c.OBJECT_ID
WHERE c.name LIKE '%column_name%' ORDER BY c.name;

List table primary key, column and constraint name

1
2
3
4
5
SELECT COLUMN_NAME,TABLE_NAME,TABLE_SCHEMA,CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'dbo'
AND OBJECTPROPERTY(
OBJECT_ID(CONSTRAINT_SCHEMA+'.'+CONSTRAINT_NAME),'IsPrimaryKey')=1

Search constraints by column or table name

1
2
3
4
5
6
SELECT COLUMN_NAME,TABLE_NAME,TABLE_SCHEMA,CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME like '%table_name%'
OR COLUMN_NAME like '%column_name%'
OR CONSTRAINT_NAME like '%constraint-name%'

Query Database for Stored Procedures

1
2
3
4
SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%%'

Create Stored Procedure (SP) if not already exist

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
IF EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'dbo.GetExampleSP')
AND type IN ( N'P', N'PC' ))
DROP PROCEDURE dbo.GetExampleSP
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ============================================
-- Description: SP description
-- ============================================
CREATE PROC dbo.GetExampleSP
@startDate DATETIME,
@endDate DATETIME,
@maxRowOnly INT = 0
AS
BEGIN
SET NOCOUNT ON;
IF (@maxRowOnly = 1)
BEGIN
SELECT COUNT(1) MaxRows FROM dbo.SomeTable (NOLOCK)
WHERE Column1 BETWEEN @startDate AND @endDate;
END ELSE
BEGIN
SELECT
Column1 AS Column1Dt
CAST (CASE WHEN Column2 NOT NULL THEN Column2 ELSE 'NULL' END AS VARCHAR) AS Column2,
ISNULL(Column3,1) AS Column3
FROM dbo.SomeTable (NOLOCK)
WHERE Column1 BETWEEN @startDate AND @endDate
END
END
GO
/*
exec [dbo].[GetExampleSP]
'2014-10-12 00:00:00.000',
'2016-02-14 00:00:00.000',
0
*/

Create table with Covering Index

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE dbo.NewSQLTable
(
Id BIGINT IDENTITY(1,1),
Column1 DATETIME,
Column2 DATETIME
CONSTRAINT PK_NewSQLTable_Id PRIMARY KEY CLUSTERED(Id),
);

IF NOT EXISTS(
SELECT 1 from sys.indexes i WHERE name = 'IX_NewSQLTable_searchdates'
AND i.object_id = OBJECT_ID('dbo.NewSQLTable'))
BEGIN
CREATE NONCLUSTERED INDEX IX_NewSQLTable_searchdates
ON dbo.NewSQLTable(Column1 DESC, Column2 DESC)
END;