C# Tutorials and offshore development in India
    Tutorials   Resources   Forum   Communities   Interview   Jobs   Projects   Offshore Development    
Silverlight Tutorials | Mentor | Code Converter | Articles | Code Factory | Computer Jokes | Members | Peer Appraisal | IT Companies | Bookmarks | Revenue Sharing |


Prizes & Awards
My Profile



Active Members
TodayLast 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
Author: Tony JohnMember 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 2008Member 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 2008Member 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

Contact Us    Privacy Policy    Terms Of Use