T-SQL List Tables, Columns

Question

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.

Have a look at the INFORMATION_SCHEMA views or sysobjects, syscolumns etc. — Martin Smith
– Martin Smith, Commented May 15, 2013 at 19:23

Tim Lehner · Accepted Answer · 2022-01-12 20:56:14Z

78

Please check out the information schema.

select *
from MyDatabaseName.information_schema.columns
order by table_name, ordinal_position

edited Jan 12, 2022 at 20:56

answered May 15, 2013 at 19:24

Tim Lehner

15.3k4 gold badges61 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user1804387 Over a year ago

Thanks Tim I appreciate this. Is there a way to add in what database the tables are assigned to? I see the Catalog is master.

Tim Lehner Over a year ago

use MyDatabaseName;, then query. MSDN: Selecting a Database. Alternatively, add the DB name to your from clause in normal 4-part fashion (I've added this to the answer).

user1804387 Over a year ago

I had just found that. Thanks again

Kin Shah · Accepted Answer · 2013-05-15 20:18:37Z

Many ways to do it. Below are couple of ways that you can list :

Option 1:

SELECT db_name() as DATABASE_NAME, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION,
       COLUMN_DEFAULT, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
       NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE,
       DATETIME_PRECISION
FROM INFORMATION_SCHEMA.COLUMNS

Option 2:

DECLARE @SQL VARCHAR(8000)
SELECT  @SQL = ''
SELECT  @SQL = @SQL + 'UNION
SELECT ''' + name + ''' [DataBaseName], t.name [TableName], c.name [ColumnName], st.name [DataType] FROM ' + name + '..sysobjects t INNER JOIN ' + name + '..syscolumns c ON t.id = c.id INNER JOIN '+ name + '..systypes st on c.xtype = st.xtype where t.xtype = ''U'''
FROM master..sysdatabases
WHERE name IN ('databasename1', 'databaseName2')  -- change here !!
SELECT @SQL = RIGHT(@SQL, LEN(@SQL)-5) + ' ORDER BY 1, 2, 3'
EXEC(@SQL)

Option 3:

select db_name() as database_name
    ,table_name = sysobjects.name
    ,column_name = syscolumns.name
    ,datatype = systypes.name
    ,length = syscolumns.length
from sysobjects
inner join syscolumns on sysobjects.id = syscolumns.id
inner join systypes on syscolumns.xtype = systypes.xtype
where sysobjects.xtype = 'U'
order by sysobjects.name
    ,syscolumns.colid

Collectives™ on Stack Overflow

T-SQL List Tables, Columns

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related