mw112009 (1/4/2012)
or can some one help me here with this antiquated query.This works fine. Just that it does not use sys.objects or sys.columns.
what i need is to be able to join with sys.schemas so that i can add the
schema name to the begining of the table name.
SELECT a.[name] as 'Table',
b.[name] as 'Column',
c.[name] as 'Datatype',
b.[length] as 'Length',
CASE
WHEN b.[cdefault] > 0 THEN d.[text]
ELSE NULL
END as 'Default',
CASE
WHEN b.[isnullable] = 0 THEN 'No'
ELSE 'Yes'
END as 'Nullable'
FROM sysobjects a
INNER JOIN syscolumns b
ON a.[id] = b.[id]
INNER JOIN systypes c
ON b.[xtype] = c.[xtype]
LEFT JOIN syscomments d
ON b.[cdefault] = d.[id]
WHERE a.[xtype] = 'u'
-- 'u' for user tables, 'v' for views.
--and a.[name]='table name'
AND a.[name] <> 'dtproperties'
ORDER BY a.[name],b.[colorder]
That's an SQL 2000 version, and it's a human-readable description of a table and its columns, not a script for the table. It will need to be updated for SQL 2005 and beyond, if a description is even what's needed.
Honestly, if you want human-readable table documentation, which is what that script tries to do, I'd use RedGate's SQL Doc product instead. A lot more features and uses.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon