View of table and column names in a DB using 2005  
Author Message
refreshe





PostPosted: Thu May 17 02:32:13 CDT 2007 Top

SQL Server Developer >> View of table and column names in a DB using 2005

Using sql 2005

I would like to create a listing of all the tables, column names, and column
types in a database.

It does not look like I can build a view using the system tables any more
(as was done in 2000) ?

Thanks !

SQL Server142  
 
 
Uri





PostPosted: Thu May 17 02:32:13 CDT 2007 Top

SQL Server Developer >> View of table and column names in a DB using 2005 Rob
For more deatils please take a look at the BOL

select sc.name as colname ,

object_name(sc.id)as tablname from sys.syscolumns sc

join sys.tables st on st.object_id=

sc.id





> Using sql 2005
>
> I would like to create a listing of all the tables, column names, and
> column types in a database.
>
> It does not look like I can build a view using the system tables any more
> (as was done in 2000) ?
>
> Thanks !
>


 
 
Razvan





PostPosted: Thu May 17 09:22:06 CDT 2007 Top

SQL Server Developer >> View of table and column names in a DB using 2005 Hello, Rob

If you also want the columns from the views (not only from tables),
you can simply use:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

If you want only columns from tables you can use:

SELECT C.* FROM INFORMATION_SCHEMA.COLUMNS C
INNER JOIN INFORMATION_SCHEMA.TABLES T
ON C.TABLE_SCHEMA=T.TABLE_SCHEMA AND C.TABLE_NAME=T.TABLE_NAME
WHERE T.TABLE_TYPE='BASE TABLE'

Razvan

 
 
Razvan





PostPosted: Thu May 17 10:07:35 CDT 2007 Top

SQL Server Developer >> View of table and column names in a DB using 2005 If you want the columns of tables and views, you can simply use:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

If you only want the columns of tables, you can use:

SELECT C.* FROM INFORMATION_SCHEMA.COLUMNS C
INNER JOIN INFORMATION_SCHEMA.TABLES T
ON C.TABLE_SCHEMA=T.TABLE_SCHEMA AND C.TABLE_NAME=T.TABLE_NAME
WHERE T.TABLE_TYPE='BASE TABLE'

Razvan