Link to home
Start Free TrialLog in
Avatar of Star Gazr1
Star Gazr1Flag for United States of America

asked on

T-SQL Columns

In T-SQL (SQL Server 2000).   How can I list all tables and columns in a database?
Also, in a separate query is there a way to list all columns along with data type and constraints (NULLS, etc).   Thanks.
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Give this a whirl...

FROM sys.tables t
      JOIN sys.columns c ON t.object_id = c.object_id
WHERE t.type_desc='USER_TABLE'

You can explore 'SELECT * FROM sys.columns' to include other column properties.
Avatar of Star Gazr1


getting error valid object name 'sys.tables'.
Sorry, SQL 2000, didn't notice that.   I gave you the SQL 2012 answer.

I don't have SQL 2000 on my box, so I'll withdraw from the question to encourage other experts to respond.
I found this on another site, this bring back Table, Column and Datatype. But it doesn't list
which User Database, the table and the columns are in (query below). Any ideas on how to add i the database info?  

select *
from information_schema.columns
order by table_name, ordinal_position
Avatar of Aneesh
Flag of Canada image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
That worked.  Thanks so much for the help.