CODEWITHSUNDEEP
sql-serverschemadiagram
cdub
cdub

Reputation: 25711

SQL Server Schema Output

How do I output the schema of my database? I want it to output the design of the database.

Something like this might work:

 SELECT TABLE_TYPE, TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
 FROM INFORMATION_SCHEMA.COLUMNS
 ORDER BY TABLE_TYPE, TABLE_NAME, COLUMN_NAME

But I can't get it to run correctly. A excel file with table names, their columns, types, primary keys, etc. is what I want.

Upvotes: 1

Views: 2532

Answers (3)

Siva
Siva

Reputation: 2811

Below posts would be useful for generating data dictionary

Database Documentation - http://deepakrangarajan.blogspot.com/2011/03/database-documentation.html

Generating a Database Data Dictionary - http://sqlserverdiaries.com/blog/index.php/2011/02/generating-a-database-data-dictionary/

Upvotes: 1

adam.kuyrkendall
adam.kuyrkendall

Reputation: 157

SELECT * FROM INFORMATION_SCHEMA.TABLES;

... sounds like what you are looking for, sans any formatting you want to do.

Upvotes: 1

cdonner
cdonner

Reputation: 37668

select 
t.type_desc,
t.name as [table], 
c.name as [column],
y.name,
c.max_length
from sys.tables t inner join
sys.columns c on c.object_id = t.object_id inner join
sys.types y on c.system_type_id = y.system_type_id
where y.name <> 'sysname'
order by 
t.type_desc,
t.name, 
c.name

Upvotes: 1

Related Questions