Useful SQL Server Queries

SQL Server Useful Queries

Reset Table Identity Field

DBCC CHECKIDENT (yourtable, reseed, 1)

If table has to start with an identity of 11 with the next insert then table should be reseeded with the identity to 10.

 

Find Column From all tables of Database

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE ‘%EmployeeID%’
ORDER BY schema_name, table_name;

This command will find all the tables having the column name EmployeeID

List Column Count Per Table Within DB

SELECT t.Name, COUNT(c.name) AS TotalColumns
FROM sys.tables t INNER JOIN sys.columns c
ON t.object_id = c.object_id
GROUP BY t.Name

Get Records Count Of All Tables Within DB

SELECT [TableName] = so.name, [RowCount] = MAX(si.rows)
FROM sysobjects so, sysindexes si
WHERE so.xtype = ‘U’ AND si.id = OBJECT_ID(so.name)
GROUP BY so.name
ORDER BY 2 DESC

Query To Check Index On All Tables Within DB

SELECT * FROM sys.indexes
WHERE OBJECT_ID IN (SELECT OBJECT_ID FROM sys.objects)

Get Tables Without Primary Key In Within DB

SELECT SCHEMA_NAME(schema_id) AS SchemaName,name AS TableName
FROM sys.tables
WHERE OBJECTPROPERTY(OBJECT_ID,’TableHasPrimaryKey’) = 0
ORDER BY SchemaName, TableName;

Find All Columns Of A Particular DataType WITHIN DB

SELECT a.length, o.name AS TableName, a.name, o.type, a.id, o.object_id, o.schema_id
FROM sys.syscolumns AS a INNER JOIN sys.systypes AS b ON a.xtype = b.xtype
AND b.name = ‘nvarchar’
INNER JOIN sys.objects AS o ON a.id = o.object_id WHERE (o.type = ‘u’) AND (o.schema_id = 1)
ORDER BY a.length desc

Find All Columns Of A Particular DataType With Max Length Within DB

SELECT a.length, o.name AS TableName, a.name, o.type, a.id, o.object_id, o.schema_id
FROM sys.syscolumns AS a INNER JOIN sys.systypes AS b ON a.xtype = b.xtype
AND b.name = ‘nvarchar’ AND a.length = -1
INNER JOIN sys.objects AS o ON a.id = o.object_id WHERE (o.type = ‘u’) AND (o.schema_id = 1)
ORDER BY a.length desc

Find Within All Stored Procedures Within DB

SELECT DISTINCT o.name AS Object_Name,o.type_desc
FROM sys.sql_modules m
INNER JOIN sys.objects o
ON m.object_id=o.object_id
WHERE m.definition Like ‘%INSERT%’ ESCAPE ‘\’

Find Find Some Column By Name In All Tables Withing A DB

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE ‘%ColumnName%’
ORDER BY schema_name, table_name;

Check All Constraint Applied On A Db Table

SELECT OBJECT_NAME(object_id) AS ConstraintName,
SCHEMA_NAME(schema_id) AS SchemaName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE ‘%CONSTRAINT’ AND OBJECT_NAME(parent_object_id)=’TableName’

Get Stored Proc Creation Date And Modification Date Within DB

SELECT name, create_date, modify_date
FROM sys.objects
WHERE type = ‘P’

Add Default Value Constraint On Existing Column

ALTER TABLE country ADD CONSTRAINT
DF__Country__UseMail DEFAULT 1 FOR ColumnName

Alter Default Value For A Table If Already Has Default Constraint

— in the ex. below DF_Account_DailyMax is the name of the Default Constraint
ALTER TABLE Account DROP CONSTRAINT DF_Account_DailyMax;
ALTER TABLE Account ADD CONSTRAINT DF_Account_DailyMax DEFAULT(2500.00) FOR DailyMax;