Gather table meta data and present it as a result set. Need TSQL code to do this.

Something like this:

(How do I get info on whether columns are primary keys, or if they allow nulls?)

SELECT      u.name + '.' + t.name AS [table],
            td.value AS [table_desc],
            c.name AS [column],
            cd.value AS [column_desc]
FROM        sysobjects t
INNER JOIN  sysusers u
    ON      u.uid = t.uid
LEFT OUTER JOIN sys.extended_properties td
    ON      td.major_id = t.id
    AND     td.minor_id = 0
    AND     td.name = 'MS_Description'
INNER JOIN  syscolumns c
    ON      c.id = t.id
LEFT OUTER JOIN sys.extended_properties cd
    ON      cd.major_id = c.id
    AND     cd.minor_id = c.colid
    AND     cd.name = 'MS_Description'
WHERE t.type = 'u'
and u.name + '.' + t.name = 'dbo.AML'
ORDER BY    t.name, c.colorder

Select allOpen in new window

This is about as close as I've gotten, so far.  I still need the "friendly" version of DataType, FK Name (column name from table where this column is a primary key), FK Table (name of the table for which this column is a primary key), and whether or not there are user constraints on the column.


--source code for the above:

SELECT      '' as 'PK'
			,c.name AS [Name]
			,cd.value AS [Description]
			,'' as 'Data Type'
			,'' as 'FK Name'
			,'' as 'FK Table'
			,c.isnullable as 'N'
			,'' as 'UC'
			            
FROM        sysobjects t
INNER JOIN  sysusers u
    ON      u.uid = t.uid
LEFT OUTER JOIN sys.extended_properties td
    ON      td.major_id = t.id
    AND     td.minor_id = 0
    AND     td.name = 'MS_Description'
INNER JOIN  syscolumns c
    ON      c.id = t.id
LEFT OUTER JOIN sys.extended_properties cd
    ON      cd.major_id = c.id
    AND     cd.minor_id = c.colid
    AND     cd.name = 'MS_Description'
WHERE t.type = 'u'
and u.name + '.' + t.name = 'dbo.AML'
ORDER BY    t.name, c.colorder

Select allOpen in new window

Thank you