Table Structure - 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

General SQL Server Forums

New to SQL Server Programming

Table Structure

Author

Topic

asm
Posting Yak Master

140 Posts

Posted - 2007-03-10 : 00:04:56

Hi

How to write a query for table structure which looks like
SQL Design table.

thanks

asm

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-10 : 00:17:06

sp_help table_name ?

asm
Posting Yak Master

140 Posts

Posted - 2007-03-10 : 00:45:20

but this is for single table but i want to print all tables in database

thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-10 : 00:47:51

make use of use INFORMATION_SCHEMA.COLUMNS

asm
Posting Yak Master

140 Posts

Posted - 2007-03-10 : 01:41:32

but this is for single table but i want to print all tables in database

thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-10 : 01:44:18

No it is not. INFORMATION_SCHEMA.COLUMNS contains information for ALL tables & views in a database.

try select * from INFORMATION_SCHEMA.COLUMNS

asm
Posting Yak Master

140 Posts

Posted - 2007-03-10 : 01:45:35

but it will not display like design table

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-10 : 01:47:36

what do you mean by "like design table" ? ? ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-10 : 01:51:50

if you mean some graphical presentation of the table, you can't do that in a query. You have to use some case tool that can reversed engineer your table structure from the database.

asm
Posting Yak Master

140 Posts

Posted - 2007-03-10 : 02:21:06

I do't want the graphical design .. i want only ColumnName, DataType and Length of a table.
result like sp_help but in sp_help i have to give single table .. but i want whole database
table to be shown with a single command

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-10 : 02:24:24

then just select the required column from the INFORMATION_SCHEMA.COLUMNS. You can refer to the BOL for more information on INFORMATION_SCHEMA.COLUMNS

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-10 : 06:09:13

asm, follow the expert. As what KH said use INFORMATION_SCHEMA.COLUMNS

-Ron-

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-10 : 07:44:13

[code]
select TABLE_NAME, COLUMN_NAME,
COLUMN_DEFINITION = DATA_TYPE +
case when DATA_TYPE in ('datetime', 'smalldatetime', 'int', 'bigint', 'money', 'text', 'ntext', 'image')
then ''
when DATA_TYPE in ('char', 'varchar', 'nchar', 'nvarchar')
then '(' + convert(varchar(10), CHARACTER_MAXIMUM_LENGTH) + ')'
when DATA_TYPE in ('decimal', 'numeric')
then '(' + convert(varchar(10), NUMERIC_PRECISION) + ',' + convert(varchar(10), NUMERIC_SCALE) + ')'
end
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME not in ('sysconstraints', 'syssegments', 'dtproperties')
order by TABLE_NAME, ORDINAL_POSITION
[/code]

asm
Posting Yak Master

140 Posts

Posted - 2007-03-12 : 03:41:37

thanks its working properly which i want but it show the view structre also but i want only for table

thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-12 : 03:49:53

[code]select T.TABLE_NAME, C.COLUMN_NAME,
COLUMN_DEFINITION = C.DATA_TYPE +
case when C.DATA_TYPE in ('datetime', 'smalldatetime', 'int', 'bigint', 'money', 'text', 'ntext', 'image')
then ''
when C.DATA_TYPE in ('char', 'varchar', 'nchar', 'nvarchar')
then '(' + convert(varchar(10), C.CHARACTER_MAXIMUM_LENGTH) + ')'
when C.DATA_TYPE in ('decimal', 'numeric')
then '(' + convert(varchar(10), C.NUMERIC_PRECISION) + ',' + convert(varchar(10), C.NUMERIC_SCALE) + ')'
end
from INFORMATION_SCHEMA.COLUMNS c JOIN INFORMATION_SCHEMA.TABLES as t
On C.TABLE_NAME = T.TABLE_NAME AND T.TABLE_TYPE = 'BASE TABLE'
where T.TABLE_NAME not in ('sysconstraints', 'syssegments', 'dtproperties')
order by T.TABLE_NAME, C.ORDINAL_POSITION[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-12 : 03:52:59

you can make use use OBJECTPROPERTY() to filter for TABLE only.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources