List Table Foreign Keys, Primary key and indexes

,

This procedure let you list [optional] by table:
-Foreign keys
-Primary key
-Indexes

I recomend create this procedure in all your databases

The procedure use 4 parameters:

@Table_name , specify your table name or use % to list all tables data.

@fk :  1 = list foreign keys ; 0 = skip fk list

@pk : 1 = list primary key data ; 0 = skip pk data

@idx : 1 = list indexes ; 0 = skip indexes list

Enjoy

/*------------------------------------------------------                  
Written : Martin Collazos   
martin.collazos@outlook.com
Date : 05/01/2014  
Test : EXEC SP_TABLE_INFO '<Your Table Name>' ,1,1,1
Fil 4:13
------------------------------------------------------*/  
  
CREATE PROCEDURE [dbo].[SP_TABLE_INFO]  
    (@table_name varchar(250), 
     @fk bit, -- 1 = activate fk list
     @pk bit, -- 1 = activate pk data
     @idx bit -- 1 = activate index list    
    )  
  
AS  
BEGIN  
  
/* Foreign key list */
IF @fk = 1
BEGIN
	SELECT OBJECT_NAME(a.parent_object_id) AS [FK.parent_table_name]
		,b.NAME AS [FK.parent_column_name]
		,OBJECT_NAME(a.referenced_object_id) AS [FK.referenced_table_name]
		,c.NAME AS [FK.referenced_column_name]
		,OBJECT_NAME(a.constraint_object_id) AS [FK.constraint_name]
		,d.create_date AS [FK.create_date]
	FROM sys.foreign_key_columns a
	INNER JOIN sys.columns b
		ON a.parent_object_id = b.[object_id]
			AND a.parent_column_id = b.column_id
	INNER JOIN sys.columns c
		ON a.referenced_object_id = c.[object_id]
			AND a.referenced_column_id = c.column_id
	INNER JOIN sys.foreign_keys d
		ON a.constraint_object_id = d.[object_id]
	WHERE OBJECT_NAME(a.parent_object_id) LIKE @table_name
	ORDER BY 1
		,2
		,4
END

/* Primary key data */
IF @pk = 1
BEGIN
	SELECT a.TABLE_NAME AS [PK.table_name]
		,a.ORDINAL_POSITION AS [PK.ordinal]
		,a.COLUMN_NAME AS [PK.column_name]
		,a.CONSTRAINT_NAME AS [PK.constraint_name]
		,b.[type] AS [PK.Type]
		,b.type_desc AS [PK.type_desc]
		,b.create_date AS [PK.create_date]
	FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE a
	INNER JOIN sys.key_constraints b
		ON a.CONSTRAINT_NAME = b.NAME
	WHERE OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1
		AND table_name LIKE @table_name
	ORDER BY 1
		,2
END

/* Index list */
IF @idx = 1
BEGIN	
	SELECT a.index_id AS [IDX.index_id]
		,a.NAME AS [IDX.name]
		,a.type_desc AS [IDX.type_desc]
		,a.is_primary_key AS [IDX.is_primary_key]
		,b.index_column_id AS [IDX.index_column_id]
		,b.column_id AS [IDX.column_id]
		,c.NAME AS [IDX.column_name]
		,b.key_ordinal AS [IDX.key_ordinal]
	FROM sys.indexes a
	INNER JOIN sys.index_columns b
		ON a.[object_id] = b.[object_id]
	INNER JOIN sys.columns c
		ON a.[object_id] = c.[object_id]
			AND b.column_id = c.column_id
	WHERE OBJECT_NAME(a.[object_id]) LIKE @table_name
	ORDER BY 1
		,5
END

END

Rate

4.43 (7)

Share

Share

Rate

4.43 (7)