So what do my tables look like anyway (Part 2)

---

I'm a doctor, not an engineer." - Doctor Leonard McCoy, Star Trek, The Original Series

---

I've used the script from the last entry on and off over the years and have not yet had the need to do this in SQL Server.

I started trying to write my own code to perform the same task and thought to do a Google search and found the following:

http://devio.wordpress.com/2008/01/08/documenting-sql-server-tables-and-columns/

So, I'm replicating that code here, just so should I ever need it, I'll have it at my fingertips.

The one thing that this does that my Oracle variant does not, is list constraints as well.

SELECT 
  obj.NAME AS TableName
 ,col.NAME AS ColumnName
 ,sys.systypes.NAME + 
     CASE 
       WHEN systypes.NAME LIKE 'n%char' 
         THEN ' (' + CONVERT(NVARCHAR, col.length / 2) + ')' 
       WHEN systypes.NAME LIKE '%char%' 
         THEN ' (' + CONVERT(NVARCHAR, col.length) + ')' 
       ELSE '' 
     END AS DataType
 ,CASE col.isnullable 
    WHEN 0 
      THEN 'NOT NULL' 
    ELSE '' 
      END AS Nullable
 ,CASE 
    WHEN COLUMNPROPERTY(obj.id, col.NAME, 'IsIdentity') = 1 
      THEN 'IDENTITY ' 
    WHEN conobj.type = 'D' 
      THEN 'DEFAULT ' + syscomments.TEXT 
    WHEN conobj.type = 'F' 
      THEN 'REFERENCES ' + fkobj.NAME + '.' + fkcol.NAME 
    ELSE '' 
      END AS Constraints
FROM       sys.sysobjects AS obj
INNER JOIN sys.syscolumns AS col 
        ON obj.id = col.id
INNER JOIN sys.systypes 
        ON sys.systypes.xtype = col.xtype
LEFT JOIN  sys.sysconstraints AS con
INNER JOIN sys.sysobjects AS conobj 
        ON con.constid = conobj.id 
        ON con.id = obj.id AND con.colid = col.colid
LEFT JOIN  sys.syscomments ON conobj.id = sys.syscomments.id
LEFT JOIN  sys.sysforeignkeys AS fk
INNER JOIN sys.syscolumns AS fkcol 
        ON fk.rkeyid = fkcol.id AND fk.rkey = fkcol.colid
INNER JOIN sys.sysobjects AS fkobj 
        ON fkcol.id = fkobj.id 
        ON conobj.id = fk.constid 
       AND obj.id = fk.fkeyid 
       AND col.colid = fk.fkey
WHERE (obj.type = 'U') AND (sys.systypes.NAME <> 'sysname')
ORDER BY obj.NAME, col.colid