Search code examples
sql-serverstored-proceduressql-server-2000

Retrieve names columns of all tables in a SQL Server database

Does there exist a system stored procedure that lists the names of all tables in a SQL Server 2000 database and per table the names of all columns in that table? I want to export this data to a file for documentation.

Solution

  • I don't know if a system stored procedure exists, but I use this:

    SELECT   SysObjects.[Name] as TableName,   
    SysColumns.[Name] as ColumnName,   
    SysTypes.[Name] As DataType,   
    SysColumns.[Length] As Length   
FROM   
    SysObjects INNER JOIN SysColumns   
ON SysObjects.[Id] = SysColumns.[Id]   
    INNER JOIN SysTypes  
ON SysTypes.[xtype] = SysColumns.[xtype]  
WHERE  SysObjects.[type] = 'U'  
ORDER BY  SysObjects.[Name]