Get list of all columns from all tables in SQL Server

This code sample shows how to get the list of all columns from all tables in a given database in SQL Server:


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]


Comments

Author: Yordan Georgiev10 Sep 2008 Member Level: Bronze   Points : 2

SELECT c.name AS [COLUMN_NAME], sc.data_type AS [DATA_TYPE], [value] AS
[DESCRIPTION] , c.max_length as [MAX_LENGTH] , c.is_nullable AS [OPTIONAL]
, c.is_identity AS [IS_PRIMARY_KEY] FROM sys.extended_properties AS ep
INNER JOIN sys.tables AS t ON ep.major_id = t.object_id
INNER JOIN sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id
= c.column_id
INNER JOIN INFORMATION_SCHEMA.COLUMNS sc ON t.name = sc.table_name and
c.name = sc.column_name
WHERE class = 1 and t.name = 'tbGui_Links'

Author: Geetha10 Sep 2008 Member Level: Gold   Points : 1

hi

Very useful post yar,
Really good one.
Keep sharing knowledge with us.

Regards,
Geetha.

Author: jason rushing16 Sep 2009 Member Level: Bronze   Points : 0

Very useful post ... thanks very much :)

Guest Author: Prathibha16 Apr 2013

Very useful post. Thank you so much!



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: