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.
Also, in a separate query is there a way to list all columns along with data type and constraints (NULLS, etc). Thanks.
ASKER
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 don't have SQL 2000 on my box, so I'll withdraw from the question to encourage other experts to respond.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That worked. Thanks so much for the help.
ASKER
thanks
SELECT t.name, c.name
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
WHERE t.type_desc='USER_TABLE'
ORDER BY t.name, c.name
You can explore 'SELECT * FROM sys.columns' to include other column properties.