SQL queries

Search table by column name

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

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

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

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

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

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;

Leave a Reply

Your email address will not be published. Required fields are marked *