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
SysObjects INNER JOIN SysColumns
ON SysObjects.[Id] = SysColumns.[Id]
ON SysTypes.[xtype] = SysColumns.[xtype]
WHERE SysObjects.[type] = 'U'
ORDER BY SysObjects.[Name]


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

SELECT 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 = sc.table_name and = sc.column_name
WHERE class = 1 and = 'tbGui_Links'

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


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


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: