Pete T (1/5/2009)

---

I want to create a text file summary of the schematics for a database. Essentially, I want to have what is output for sp_help objectname, but for every object in the database. I can generate the script for the entire database, and that might work, but I don't really want a SQL script, just a summary of the schema (table/object names, field and column names, and datatypes and lengths. Keys and indexes would be nice, too).

A diagram isn't really what I'm after, either, as I think that would be unwieldy, plus I don't really want to have to drag and arrange every single table image in the diagram to get it to look right. I'm thinking a text file would be best.

I thought maybe I could create a cursor and loop through the objects or something, but don't really know what/how the output of running sp_help objectname would work - can I output to text somehow?

Maybe there's another tool or script that would do what I want? Thanks for any info or help!

RBarryYoung (1/5/2009)

---

This should do it:

Declare @sql Nvarchar(MAX)
Set @sql = N''

Select @sql = @sql + N'
EXEC sp_help ''['+TABLE_SCHEMA+N'].['+TABLE_NAME+N']'';'
 From INFORMATION_SCHEMA.TABLES
 
Print N'Executing:
'+@sql+'
'
EXEC(@sql)