C# Tutorials and offshore development in India

Prizes & Awards

My Profile

AdSense Revenue

Active Members
Today

Last 7 Days

more...

New Feature: Community Sites: Create your own .NET community website and start earning from Google AdSense ! It's Free !

Get list of all columns from all tables in SQL Server

Posted Date: 20 Aug 2008 Resource Type: Code Snippets Category: SQL Schema

Posted By: Tony John Member Level: Gold
Rating:

Points: 7

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]

Responses

Author: Yordan Georgiev 10 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: Geetha 10 Sep 2008 Member Level: Gold Points : 1

hi

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

Regards,
Geetha.

Feedbacks

Popular Tags

What are tags ?

Search Tags

SQL columns . Get all columns .

Post Feedback

This is a strictly moderated forum. Only approved messages will appear in the site. Please use 'Spell Check' in Google toolbar before you submit.
You must Sign In to post a response.

	Next Resource: How to create default constraint over column in a Table
	Previous Resource: How to get the Count of StoredProcedures and Functions?
	Return to Discussion Resource Index
	Post New Resource
	Category: SQL Schema

Post resources and earn money!

Related Resources

dotNet Slackers BizTalk Adaptors Web Design

web conferencing services