How to print out list of user tables and fields 3

[tomatdeepwater]

I am new at this and need to capture info about my database.

Could anyone recommend an easy way to use T-SQL to print out a list of User Tables and the Fields contained in them?

Thanks!

 

[druer]

Will get you a list of tables
select * from INFORMATION_SCHEMA.tables

Will get you a list of columns for a table
SELECT *
FROM Northwind.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Customers'

The following link will show you everything you ever wanted to know, and then some more:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ia-iz_4pbn.asp

 

[ShankarJ]

Here is a snippet of code from somebody's posting on this group (it may have been modified a bit) :

select isnull(B.Name, '') as TableName, A.Name as ObjectName, A.XType, X.ObjectType
from sysobjects A
left outer join sysobjects B on A.Parent_Obj = B.ID OR A.Parent_Obj = NULL
inner join
( select 1 as Pos, 'U' as XType, 'User table' as ObjectType union
select 2, 'D', 'DEFAULT Value' union
select 3, 'PK', 'PRIMARY KEY constraint' union
select 4, 'UQ', 'UNIQUE constraint' union
select 5, 'F', 'FOREIGN KEY constraint' union
select 6, 'C', 'CHECK constraint' union
select 7, 'TR', 'Trigger' union
select 8, 'P', 'Stored Procedure'
) X on X.XType = A.XType
where ('U' in (A.XType, B.XType) OR X.XType = 'P') AND A.Name NOT LIKE 'dt%' AND (B.Name IS NULL OR B.Name NOT LIKE 'dt%')
order by isnull(B.Name, A.Name), X.pos

HTH