cancel
Showing results for 
Search instead for 
Did you mean: 

command to select Table name and column names

Former Member
0 Kudos
7,072

HI,

SELECT table_name=sysobjects.name, column_name=syscolumns.name FROM sysobjects INNER JOIN syscolumns ON sysobjects.id = syscolumns.id inner join systypes on syscolumns.type=systypes.type where sysobjects.type='U' order by sysobjects.name,syscolumns.colid

which will give the below result.

Table_name column_name A xxx A xyz
………………

I need

Database_name table_name column_name

Accepted Solutions (0)

Answers (1)

Answers (1)

VolkerBarth
Contributor
0 Kudos

FWIW, with MS SQL Server, the following adds the name of the current database:

SELECT database_name = DB_NAME(), table_name=sysobjects.name, column_name=syscolumns.name
FROM sysobjects INNER JOIN syscolumns ON sysobjects.id = syscolumns.id inner join systypes on syscolumns.type=systypes.type
WHERE sysobjects.type='U'
ORDER BY sysobjects.name,syscolumns.colid

If you want to access different databases as well, I guess you will have to run the query on the master database and use sysdatabases (possibly in a join with the query above) to list those.