Tags: MSSQL

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

Tags: MSSQL