Getting Table Definition - 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 2000 Forums

SQL Server Development (2000)

Getting Table Definition

Author

Topic

vmurali
Yak Posting Veteran

88 Posts

Posted - 2006-11-26 : 04:32:23

Hi, Is there any way to get the table definition (Table columns, Table Columndatatype , Columndatasize). Can we get it using a single sql statement specifying database, table name.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-26 : 04:33:47

select * from information_schema.columns where table_name = '<your table name here>'

Peter Larsson
Helsingborg, Sweden

vmurali
Yak Posting Veteran

88 Posts

Posted - 2006-11-26 : 04:41:56

Thanks for your reply. Will this work for if i have to get values from tables present in different databases. I have a source1 and source 2 database and I have to get details with respect to individual databases.

quote:
Originally posted by Peso

select * from information_schema.columns where table_name = '<your table name here>'

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-26 : 04:43:16

prefix information_schema with database name like this:

select * from db1.information_schema.columns where table_name = '<your table name here>'

Harsh Athalye
India.
"Nothing is Impossible"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-26 : 23:49:58

Other method is to use sp_help

Dbname..sp_help 'table name'

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources