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.
LVL 3
fjkaykr11Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
>But it doesn't list which User Database,
the database name will be the current one where you run the query

select DB_NAME() as Database_name, * from information_schema.columns
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Give this a whirl...

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.
0
 
fjkaykr11Author Commented:
getting error valid object name 'sys.tables'.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0
 
fjkaykr11Author Commented:
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
0
 
fjkaykr11Author Commented:
That worked.  Thanks so much for the help.
0
 
fjkaykr11Author Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.