Get Info about table columns Samples and examples

Get Info about table columns

Posted Date: 27-Jul-2012	Category: SQL DBA
Author: Pritom Nandy	Member Level: Gold	Points: 10

The following snippet will help the developers to get the column info of a particular table or database. Often when we work with the database we may need to get the total number of columns in the table and also when we do sort of analysis on any table then we need to get the complete tables/coumns information of a schema.database. These queries will help to do so.

Hi,
Developers may need to get the info on database tables. Here is a code that helps the developers to get the count of columns of a table.


SELECT COUNT(*) 
FROM yourdatabase.sys.columns
WHERE object_id = OBJECT_ID('yourdatabase.dbo.tablename')

Example :


SELECT COUNT(*) 
FROM eVAT.sys.columns
WHERE object_id = OBJECT_ID('eVAT.dbo.Registration')

Now the developers may also need to get the name of columns of a particular table.


SELECT COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_CATALOG='databasename' AND TABLE_NAME='tablename'

Example:


SELECT COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_CATALOG='eVAT' AND TABLE_NAME='Registration'

If anyone needs complete info of all the tables in a database then try this


SELECT *
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_CATALOG='databasename'

Example:


SELECT *
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_CATALOG='eVAT'

Executing the above code without the 'TABLE_CATELOG' will list all the database table's information in the schema.


SELECT *
FROM INFORMATION_SCHEMA.COLUMNS

Example:


SELECT *
FROM INFORMATION_SCHEMA.COLUMNS

Hope it may help all.

Post Comment:

Notify me by email when others post comments to this article.

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:	Sign In to fill automatically.
Email:	(Will not be published, but required to validate comment)
	Type the numbers and letters shown on the left.

Online Members

Get Info about table columns

Post Comment:

Email subscription