Need Query to fetch the following columns

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

Transact-SQL (2000)

Need Query to fetch the following columns

Author

Topic

kanagarajkumar
Starting Member

1 Post

Posted - 2006-05-18 : 09:02:17

Dear All,

I need to capture the following in a single query. though i know the following to List all columns in all the tables
Select
so.name "Table Name",
sc.name "Field Name",
st.name "Data Type",
sc.length "Size",
'Is Null' =
Case
When sc.isnullable = 1 then 'Null'
Else 'Not Null'
End
From
syscolumns sc,
sysobjects so,
systypes st
Where
so.type = 'U' and
sc.id = so.id and
sc.xtype = st.xtype and
so.status > 0 and
st.xusertype <> 256

Table Name,Column name,Data Type & ( Size ),Index,Constraint Name,Constraint Type, Constraint Condition,Reference -Table / Stored procedure / Function / Triggers

Thanks in Advance.

Regards

Kanagaraj Kumar v
---------------------------------------
Kanagaraj Kumar Vinoth Kumar
" A moment will come after which every moment will be MINE, and I am sure about it"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-05-18 : 09:08:07

Take a look at the INFORMATION_SCHEMA views, especially:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
SELECT * FROM INFORMATION_SCHEMA.TABLES
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

It does all the joins and such for you, and it is a standard method. Querying system tables directly is ill-advised, as their structures can change or their access restricted/prevented.

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2006-05-18 : 10:46:30

(Moved to the Transact-SQL forum)

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources