Wednesday, September 21, 2016

Documenting Foreign Keys in SQL 2012

SELECT F.name
     , PT.name AS FromTab
     , PC.name AS FromCol
     , CT.name AS ToTab
     , CC.name AS ToCol
FROM       sys.foreign_keys        F
INNER JOIN sys.foreign_key_columns FK ON FK.constraint_object_id = F.object_id
inner join sys.tables              PT ON PT.object_id            = FK.parent_object_id
inner join sys.tables              CT ON CT.object_id            = FK.referenced_object_id
                                                               
inner join sys.columns             PC ON PC.object_id            = FK.parent_object_id
                                     AND PC.column_id            = FK.parent_column_id
inner join sys.columns             CC ON CC.object_id            = FK.referenced_object_id
                                     AND CC.column_id            = FK.referenced_column_id

ORDER BY PT.name, PC.column_id


Tuesday, September 20, 2016

SQL documentation, updated

SELECT T.name AS TableName
    , C.colorder AS ColOrder
    , C.name AS ColName
    , CASE WHEN C.xtype =  34 THEN 'IMAGE'
           WHEN C.xtype =  35 THEN 'TEXT'
           WHEN C.xtype =  36 THEN 'UNIQUEIDENTIFIER'
           WHEN C.xtype =  40 THEN 'DATE'
           WHEN C.xtype =  48 THEN 'TINYINT'
           WHEN C.xtype =  52 THEN 'SMALLINT'
           WHEN C.xtype =  56 THEN 'INT'
           WHEN C.xtype =  58 THEN 'SMALLDATETIME'
           WHEN C.xtype =  59 THEN 'REAL'
           WHEN C.xtype =  60 THEN 'MONEY'
           WHEN C.xtype =  61 THEN 'DATETIME'
           WHEN C.xtype =  62 THEN 'FLOAT'
           WHEN C.xtype =  98 THEN 'SQL_VARIANT'
           WHEN C.xtype =  99 THEN 'NTEXT'
           WHEN C.xtype = 104 THEN 'BIT'
           WHEN C.xtype = 106 THEN 'DECIMAL(' + CONVERT(VARCHAR,C.xprec) + ',' + CONVERT(VARCHAR,C.xscale)+ ')'
           WHEN C.xtype = 108 THEN 'NUMERIC(' + CONVERT(VARCHAR,C.xprec) + ',' + CONVERT(VARCHAR,C.xscale)+ ')'
           WHEN C.xtype = 122 THEN 'SMALLMONEY'
           WHEN C.xtype = 127 THEN 'BIGINT'
           WHEN C.xtype = 165 THEN 'VARBINARY'
           WHEN C.xtype = 167 THEN
                CASE WHEN C.prec>0
                              THEN 'VARCHAR('   + CONVERT(VARCHAR,C.prec) + ')'
                              ELSE 'VARCHAR(MAX)' END
           WHEN C.xtype = 173 THEN 'BINARY('    + CONVERT(VARCHAR,C.prec) + ')'
           WHEN C.xtype = 175 THEN 'CHAR('      + CONVERT(VARCHAR,C.prec) + ')'
           WHEN C.xtype = 189 THEN 'TIMESTAMP'
           WHEN C.xtype = 231 THEN
                CASE WHEN C.prec>0
                              THEN 'NVARCHAR('   + CONVERT(VARCHAR,C.prec) + ')'
                              ELSE 'NVARCHAR(MAX)' END

           WHEN C.xtype = 239 THEN 'NCHAR('     + CONVERT(VARCHAR,C.prec) + ')'
           ELSE '??? ' + CONVERT(VARCHAR,C.xtype) + ' ???' END AS ColType
    , C.isnullable AS ColNullable
FROM sysobjects T
INNER JOIN syscolumns C On C.ID = T.ID

WHERE T.xtype = 'U'
ORDER BY T.Name, C.ColOrder