Table Meta

CREATE  view [dbo].[vw_TablePrimaryKeys]
AS
/*
----------------------------------------------------------------------------
 View:		[vw_TablePrimaryKeys]

 Description:	Returns PK information for all tables
----------------------------------------------------------------------------
 Who					When		Why
 ---------				---------	---------
 Arendt -dynamit		8/11/04		Created.
-----------------------------------------------------------------------------
*/

SELECT DISTINCT C.Table_Schema, K.TABLE_NAME, C.CONSTRAINT_TYPE,  K.COLUMN_NAME, K.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS C
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS K
ON C.TABLE_NAME = K.TABLE_NAME
AND C.CONSTRAINT_CATALOG = K.CONSTRAINT_CATALOG
AND C.CONSTRAINT_SCHEMA = K.CONSTRAINT_SCHEMA
AND C.CONSTRAINT_NAME = K.CONSTRAINT_NAME
WHERE C.CONSTRAINT_TYPE = 'PRIMARY KEY'
GO


SELECT DISTINCT
s.name [Schema],
t.name [Name],

c.name [ColumnName],
ty.name [SqlType],
c.is_nullable [IsColumnNullable],
c.is_identity [IsColumnIdentity],

-- CASE WHEN pk.[COLUMN_NAME] IS NOT NULL THEN 1 ELSE 0 END [IsPk],

CASE WHEN o2.name IS NOT NULL THEN 1 ELSE 0 END [IsFk],
s2.name [RelatedSchema],
o2.name [RelatedName],
c2.Name [RelatedColumnId],
c.max_length [ColumnMaxLength],
c.[precision] [ColumnPrecision],
c.scale [ColumnScale]




FROM sys.columns c
INNER JOIN sys.tables t ON c.[object_id] = t.[object_id]
INNER join sys.objects o ON o.[object_id] = t.[object_id] 
INNER join sys.schemas s ON o.[schema_id]=S.[schema_id] 
INNER JOIN sys.types ty ON c.system_type_id = ty.system_type_id
LEFT JOIN sys.foreign_key_columns fk ON fk.parent_object_id = t.[object_id] AND fk.parent_column_id = c.column_id
LEFT JOIN sys.tables t2 ON fk.referenced_object_id = t2.[object_id]
LEFT JOIN sys.columns c2 ON fk.referenced_object_id = c2.[object_id] AND fk.referenced_column_id = c2.column_id 
LEFT JOIN sys.objects o2 ON o2.[object_id] = t2.[object_id] 
LEFT JOIN sys.schemas s2 ON o2.[schema_id] = s2.[schema_id] 
--LEFT JOIN [dbo].[vw_TablePrimaryKeys] pk ON pk.[Table_Schema] = s.name and pk.[TABLE_NAME] = t.name AND pk.[COLUMN_NAME] = c.name
WHERE ty.name <> 'sysname' 
ORDER BY s.name ,
t.name ,c.name