List of Fields in a SQL Database

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)

List of Fields in a SQL Database

Author

Topic

nowonda
Starting Member

1 Post

Posted - 2005-11-29 : 08:24:32

Hey guys,

I want to know if there's anything I can do to get a list of ALL the fields used in a SQL database and ALL the tables a certain field is being used in. So basically a list of all the fields (data fields, key fields) and the tables that contain each field.
Is this doable?

Thanks a lot!

m

Johnyalm
Starting Member

49 Posts

Posted - 2005-11-29 : 08:37:37

Try this!

SELECT sysobjects.Name as 'TableName',
syscolumns.Name as 'ColumnName',
syscolumns.isNullable,
syscolumns.Length,
systypes.Name as 'Type',
OBJECT_NAME(sysforeignkeys.rkeyid) as 'FKTable'

FROM syscolumns, sysobjects, systypes, sysforeignkeys

WHERE syscolumns.ID = sysobjects.ID
AND syscolumns.xusertype = systypes.xusertype
and syscolumns.id *= sysforeignkeys.fkeyid --outer join: there may not
and syscolumns.colid *= sysforeignkeys.fkey --be any foreign key
and sysobjects.status >= 0 --don't get system objects
and sysobjects.type = 'U' --user defined table

-- Order by this so the front end can perform a binary search
ORDER BY sysobjects.Name, syscolumns.Name

www.mirrorgate.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-29 : 08:38:40

Select * from information_schema.columns

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources