Now a days I'm working lot on database. I took switch for a while to learn basics of database. For my convenience I created Microsoft SQL Server Stored Procedure that helps us to find all constraints on table in one shot.
Microsoft SQL Management Studio also provide same data but display in multiple tables. I merged all that data in one single table so that data can be easily copied to Microsoft Excel or Google sheet. It also provide option to find column level constraints.
Execute below Stored Procedure in your Microsoft SQL Server... Or You can download same SQL script from
here
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
IF(OBJECT_ID('usp_TableSchema') IS NOT NULL)
DROP PROCEDURE usp_TableSchema;
GO
CREATE PROCEDURE usp_TableSchema
@TableName varchar(200),
@ColumnName varchar(200)
AS
BEGIN
/**
* @author javaQuery
* @date 7th May, 2014
* www.javaquery.com
*/
/* Variable declaration */
DECLARE @CURSOR_LOOP_ON_COLUMN CURSOR;
DECLARE @GET_COLUMN_NAME VARCHAR(200);
DECLARE @GET_COLUMN_DATA_TYPE VARCHAR(200);
DECLARE @CURSOR_LOOP_ON_INDEX CURSOR;
DECLARE @GET_INDEX_NAME VARCHAR(200);
DECLARE @GET_INDEX_INCLUDED VARCHAR(200);
DECLARE @COUNT INT = 1;
DECLARE @CURSOR_LOOP_ON_FK CURSOR;
DECLARE @GET_FK_TABLE_NAME VARCHAR(200);
DECLARE @GET_FK_COLUMN_NAME VARCHAR(200);
DECLARE @GET_FK_CONSTRAINT_NAME VARCHAR(200);
DECLARE @GET_INDEX_COUNT INT;
DECLARE @GET_FK_COUNT INT;
DECLARE @CURRENT_FK_INDEX INT;
/* Temporary table to hold final records */
CREATE TABLE #TABLE_SCHEMA
(
COLUMN_NAME VARCHAR(200),
COLUMN_TYPE VARCHAR(200),
PK VARCHAR(5),
PK_CONSTRAINT_NAME VARCHAR(200),
UNIQUE_KEY VARCHAR(5),
UNIQUE_KEY_NAME VARCHAR(200),
DF_CONSTRAINT_NAME VARCHAR(200),
DF_CONSTRAINT_VALUE VARCHAR(200),
FK_CONSTRAINT_ON_COLUMN VARCHAR(200),
FK_REFERENCE_TABLE VARCHAR(200),
FK_REFERENCE_COLUMN VARCHAR(200),
COLUMN_AS_FK_ON_TABLE VARCHAR(200),
COLUMN_AS_FK_ON_TABLE_COLUMN VARCHAR(200),
COLUMN_AS_FK_CONSTRAINT_NAME VARCHAR(200),
IDX_NAME VARCHAR(200),
IDX_COLUMN_INCLUDED VARCHAR(5),
SEARCH_COLUMN VARCHAR(200)
);
/* Temporary table to hold 'PRIMARY KEY CONSTRAINTS' */
CREATE TABLE #PK_CONSTRAINT
(
PK_COLUMN_NAME VARCHAR(200),
PK_CONSTRAINT_NAME VARCHAR(200)
);
/* Fetch all Primary keys on table */
INSERT INTO #PK_CONSTRAINT(PK_COLUMN_NAME,PK_CONSTRAINT_NAME)
SELECT COLUMN_NAME, tc.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
ON tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
AND tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
AND kcu.TABLE_NAME = @TableName
ORDER BY kcu.ORDINAL_POSITION
/* Temporary table to hold 'UNIQUE KEY CONSTRAINTS' */
CREATE TABLE #UNIQUE_KEY
(
UN_COLUMN_NAME VARCHAR(200),
UN_CONSTRAINT_NAME VARCHAR(200)
);
/* Fetch all Unique keys on table */
INSERT INTO #UNIQUE_KEY(UN_COLUMN_NAME, UN_CONSTRAINT_NAME)
SELECT COLUMN_NAME, tc.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
ON tc.CONSTRAINT_TYPE = 'UNIQUE'
AND tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
AND kcu.TABLE_NAME = @TableName
ORDER BY kcu.ORDINAL_POSITION
/* Temporary table to hold 'DEFAULT CONSTRAINTS' */
CREATE TABLE #DF_CONSTRAINT
(
DF_COLUMN_NAME VARCHAR(200),
DF_CONSTRAINT_NAME VARCHAR(200),
DF_CONSTRAINT_VALUE VARCHAR(200)
);
/* Fetch all default constraints on table */
INSERT INTO #DF_CONSTRAINT (DF_COLUMN_NAME, DF_CONSTRAINT_NAME, DF_CONSTRAINT_VALUE)
SELECT col.name, df.name, definition
FROM sys.columns col
LEFT JOIN sys.default_constraints df
ON col.default_object_id = df.object_id
WHERE OBJECT_NAME(col.object_id) = @TableName
AND OBJECT_NAME(df.parent_object_id) = @TableName;
/* Temporary table to hold 'FOREIGN KEY CONSTRAINTS' */
CREATE TABLE #FK_CONSTRAINT_CURRENT_COLUMN
(
COLUMN_NAME VARCHAR(200),
FK_CUURENT_TABLE_NAME VARCHAR(200),
FK_CURRENT_COLUMN_NAME VARCHAR(200),
FK_CURRENT_CONSTRAINT_NAME VARCHAR(200)
);
/* Find all referencing Foreign key constraints on table */
INSERT INTO #FK_CONSTRAINT_CURRENT_COLUMN (COLUMN_NAME, FK_CUURENT_TABLE_NAME,FK_CURRENT_COLUMN_NAME, FK_CURRENT_CONSTRAINT_NAME)
SELECT col1.name, tab2.name, col2.name, obj.name
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.columns col1
ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2
ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2
ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id
WHERE tab1.name = @TableName
/* Temporary table to hold 'FOREIGN KEY CONSTRAINTS' */
CREATE TABLE #FK_CONSTRAINT
(
ID numeric(19, 0) IDENTITY(1,1) NOT NULL,
COLUMN_NAME VARCHAR(200),
FK_TABLE_NAME VARCHAR(200),
FK_COLUMN_NAME VARCHAR(200),
FK_CONSTRAINT_NAME VARCHAR(200)
);
/* Find all referencing Foreign key constraints on table */
INSERT INTO #FK_CONSTRAINT (COLUMN_NAME, FK_TABLE_NAME,FK_COLUMN_NAME, FK_CONSTRAINT_NAME)
SELECT col2.name, tab1.name, col1.name, obj.name
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.columns col1
ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2
ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2
ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id
WHERE tab2.name = @TableName
/* Temporary table to hold 'INDICES' */
CREATE TABLE #COLUMN_INDICES
(
ID numeric(19, 0) IDENTITY(1,1) NOT NULL,
INDEX_COLUMN_NAME VARCHAR(200),
INDEX_NAME VARCHAR(200),
INDEX_COLUMN_INCLUDED VARCHAR(5)
);
/* Loop through each column name */
SET @CURSOR_LOOP_ON_COLUMN = CURSOR FOR SELECT COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
ORDER BY ORDINAL_POSITION;
OPEN @CURSOR_LOOP_ON_COLUMN
FETCH NEXT
FROM @CURSOR_LOOP_ON_COLUMN INTO @GET_COLUMN_NAME, @GET_COLUMN_DATA_TYPE
WHILE @@FETCH_STATUS = 0
BEGIN
/* Fetch all indices on column */
INSERT INTO #COLUMN_INDICES (INDEX_COLUMN_NAME, INDEX_NAME, INDEX_COLUMN_INCLUDED)
SELECT DISTINCT c.name, ind.name, CASE WHEN ic.is_included_column = 0 THEN 'NO' WHEN ic.is_included_column != 0 THEN 'YES' END
FROM sys.indexes ind
RIGHT JOIN sys.tables t
ON ind.object_id = t.object_id
RIGHT JOIN sys.columns c
ON t.object_id = c.object_id
RIGHT JOIN sys.index_columns ic
ON ind.index_id = ic.index_id
AND c.column_id = ic.column_id
WHERE ind.is_primary_key = 0
AND ind.is_unique_constraint = 0
AND t.is_ms_shipped = 0
AND ind.name is not null
AND ind.is_unique in (0,1)
AND t.name = @TableName
AND c.name = @GET_COLUMN_NAME
AND OBJECT_NAME(ic.object_id) = @TableName;
/* Fill up record table */
INSERT INTO #TABLE_SCHEMA
(COLUMN_NAME, COLUMN_TYPE, PK, PK_CONSTRAINT_NAME, UNIQUE_KEY, UNIQUE_KEY_NAME
,DF_CONSTRAINT_NAME, DF_CONSTRAINT_VALUE, FK_CONSTRAINT_ON_COLUMN, FK_REFERENCE_TABLE, FK_REFERENCE_COLUMN,
COLUMN_AS_FK_ON_TABLE, COLUMN_AS_FK_ON_TABLE_COLUMN, COLUMN_AS_FK_CONSTRAINT_NAME, IDX_NAME, IDX_COLUMN_INCLUDED,
SEARCH_COLUMN)
SELECT @GET_COLUMN_NAME,
@GET_COLUMN_DATA_TYPE,
CASE WHEN (SELECT COUNT(*) FROM #PK_CONSTRAINT WHERE PK_COLUMN_NAME = @GET_COLUMN_NAME) > 0 THEN 'YES' END,
(SELECT PK_CONSTRAINT_NAME FROM #PK_CONSTRAINT WHERE PK_COLUMN_NAME = @GET_COLUMN_NAME),
CASE WHEN (SELECT COUNT(*) FROM #UNIQUE_KEY WHERE UN_COLUMN_NAME = @GET_COLUMN_NAME) > 0 THEN 'YES' END,
(SELECT UN_CONSTRAINT_NAME FROM #UNIQUE_KEY WHERE UN_COLUMN_NAME = @GET_COLUMN_NAME),
(SELECT DF_CONSTRAINT_NAME FROM #DF_CONSTRAINT WHERE DF_COLUMN_NAME = @GET_COLUMN_NAME),
(SELECT DF_CONSTRAINT_VALUE FROM #DF_CONSTRAINT WHERE DF_COLUMN_NAME = @GET_COLUMN_NAME),
(SELECT FK_CURRENT_CONSTRAINT_NAME FROM #FK_CONSTRAINT_CURRENT_COLUMN WHERE COLUMN_NAME = @GET_COLUMN_NAME),
(SELECT FK_CUURENT_TABLE_NAME FROM #FK_CONSTRAINT_CURRENT_COLUMN WHERE COLUMN_NAME = @GET_COLUMN_NAME),
(SELECT FK_CURRENT_COLUMN_NAME FROM #FK_CONSTRAINT_CURRENT_COLUMN WHERE COLUMN_NAME = @GET_COLUMN_NAME),
(SELECT TOP 1 FK_TABLE_NAME FROM #FK_CONSTRAINT WHERE COLUMN_NAME = @GET_COLUMN_NAME),
(SELECT TOP 1 FK_COLUMN_NAME FROM #FK_CONSTRAINT WHERE COLUMN_NAME = @GET_COLUMN_NAME),
(SELECT TOP 1 FK_CONSTRAINT_NAME FROM #FK_CONSTRAINT WHERE COLUMN_NAME = @GET_COLUMN_NAME),
(SELECT TOP 1 INDEX_NAME FROM #COLUMN_INDICES WHERE INDEX_COLUMN_NAME = @GET_COLUMN_NAME),
(SELECT TOP 1 INDEX_COLUMN_INCLUDED FROM #COLUMN_INDICES WHERE INDEX_COLUMN_NAME = @GET_COLUMN_NAME),
@GET_COLUMN_NAME;
SELECT @GET_FK_COUNT = COUNT(*) FROM #FK_CONSTRAINT WHERE COLUMN_NAME = @GET_COLUMN_NAME;
SELECT TOP 1 @CURRENT_FK_INDEX = ID FROM #FK_CONSTRAINT WHERE COLUMN_NAME = @GET_COLUMN_NAME;
SELECT @GET_INDEX_COUNT = COUNT(*) FROM #COLUMN_INDICES WHERE INDEX_COLUMN_NAME = @GET_COLUMN_NAME;
/* Below logic is to fiil up Foreign key and index in one row */
SET @COUNT = 1;
WHILE (@COUNT < @GET_INDEX_COUNT)
BEGIN
SET @GET_FK_TABLE_NAME = NULL;
SET @GET_FK_COLUMN_NAME = NULL;
SET @GET_FK_CONSTRAINT_NAME = NULL;
SELECT TOP 1 @CURRENT_FK_INDEX = ID, @GET_FK_TABLE_NAME = FK_TABLE_NAME,
@GET_FK_COLUMN_NAME = FK_COLUMN_NAME, @GET_FK_CONSTRAINT_NAME = FK_CONSTRAINT_NAME
FROM #FK_CONSTRAINT
WHERE ID > @CURRENT_FK_INDEX AND COLUMN_NAME = @GET_COLUMN_NAME;
INSERT INTO #TABLE_SCHEMA
(COLUMN_NAME, COLUMN_TYPE, COLUMN_AS_FK_ON_TABLE, COLUMN_AS_FK_ON_TABLE_COLUMN, COLUMN_AS_FK_CONSTRAINT_NAME
,IDX_NAME, IDX_COLUMN_INCLUDED, SEARCH_COLUMN)
SELECT TOP 1 '', '', @GET_FK_TABLE_NAME, @GET_FK_COLUMN_NAME, @GET_FK_CONSTRAINT_NAME
,INDEX_NAME, INDEX_COLUMN_INCLUDED, @GET_COLUMN_NAME
FROM #COLUMN_INDICES
WHERE ID > @COUNT
AND INDEX_COLUMN_NAME = @GET_COLUMN_NAME;
SET @COUNT = @COUNT + 1;
END
IF(@GET_FK_COUNT > @GET_INDEX_COUNT)
BEGIN
SET @COUNT = 1;
WHILE(@COUNT < @CURRENT_FK_INDEX)
BEGIN
SET @COUNT = @CURRENT_FK_INDEX;
INSERT INTO #TABLE_SCHEMA
(COLUMN_NAME, COLUMN_TYPE, COLUMN_AS_FK_ON_TABLE, COLUMN_AS_FK_ON_TABLE_COLUMN, COLUMN_AS_FK_CONSTRAINT_NAME, SEARCH_COLUMN)
SELECT TOP 1 '', '', FK_TABLE_NAME, FK_COLUMN_NAME, FK_CONSTRAINT_NAME, @GET_COLUMN_NAME
FROM #FK_CONSTRAINT
WHERE ID > @CURRENT_FK_INDEX
AND COLUMN_NAME = @GET_COLUMN_NAME;
SELECT TOP 1 @CURRENT_FK_INDEX = ID
FROM #FK_CONSTRAINT
WHERE ID > @CURRENT_FK_INDEX AND COLUMN_NAME = @GET_COLUMN_NAME;
END
END
/* Flush all records from #COLUMN_INDICES table */
TRUNCATE TABLE #COLUMN_INDICES;
FETCH NEXT
FROM @CURSOR_LOOP_ON_COLUMN INTO @GET_COLUMN_NAME, @GET_COLUMN_DATA_TYPE
END
CLOSE @CURSOR_LOOP_ON_COLUMN
DEALLOCATE @CURSOR_LOOP_ON_COLUMN
IF(@ColumnName IS NOT NULL AND @ColumnName != '')
BEGIN
SELECT COLUMN_NAME, COLUMN_TYPE, PK, PK_CONSTRAINT_NAME, UNIQUE_KEY, UNIQUE_KEY_NAME
,DF_CONSTRAINT_NAME, DF_CONSTRAINT_VALUE, FK_CONSTRAINT_ON_COLUMN, FK_REFERENCE_TABLE, FK_REFERENCE_COLUMN,
COLUMN_AS_FK_ON_TABLE, COLUMN_AS_FK_ON_TABLE_COLUMN, COLUMN_AS_FK_CONSTRAINT_NAME, IDX_NAME, IDX_COLUMN_INCLUDED
FROM #TABLE_SCHEMA
WHERE SEARCH_COLUMN = @ColumnName;
END
ELSE
BEGIN
SELECT COLUMN_NAME, COLUMN_TYPE, PK, PK_CONSTRAINT_NAME, UNIQUE_KEY, UNIQUE_KEY_NAME
,DF_CONSTRAINT_NAME, DF_CONSTRAINT_VALUE, FK_CONSTRAINT_ON_COLUMN, FK_REFERENCE_TABLE, FK_REFERENCE_COLUMN,
COLUMN_AS_FK_ON_TABLE, COLUMN_AS_FK_ON_TABLE_COLUMN, COLUMN_AS_FK_CONSTRAINT_NAME, IDX_NAME, IDX_COLUMN_INCLUDED
FROM #TABLE_SCHEMA;
END
END
Execute usp_TableSchema
usp_TableSchema accepts two arguments
@TableName, @ColumnName
- To get table level constraints fire this command exec usp_TableSchema 'table_name', ''
- To get constraints on particular column fire this command exec usp_TableSchema 'table_name', 'column_name'
 |
| Click to enlarge |
Microsoft SQL Studio Management Shortcut
Select TableName and press
Alt + F1
