Database details - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Database details

Author

Topic

mmalaka
Starting Member

33 Posts

Posted - 2011-11-25 : 12:06:25

Experts

I am working on SQL 2008 server and I want to generate some kind of a report (perhaps Excel report) for a database to show the design of each table.

So for Example I want to show something like:

Table1: Field 1 (int) PK
Field 2 (nvarchar(50))

Table2: Field 1 (int) PK
Field 2 (datetime)

...etc

Any advice please?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-25 : 12:08:32

Have a look at the information_schema views.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

vikki.seth
Yak Posting Veteran

66 Posts

Posted - 2011-11-25 : 13:05:05

Check this:

SELECT t.name AS table_name,
SCHEMA_NAME(t.schema_id) AS schema_name,
c.name AS column_name ,st.name As Datatype
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
Inner Join Sys.types st on c.user_type_id=st.user_type_id
ORDER BY schema_name, table_name;

Kristen
Test

22859 Posts

Posted - 2011-11-26 : 06:31:59

Rather more complicated than Nigel's suggestion - which just requires:


SELECT TABLE_NAME, TABLE_SCHEMA, COLUMN_NAME, DATA_TYPE
FROM	INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'SomeTable'

plus there are a host of other useful columns in that View.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources