Get Info about table columns
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.