Jan 2, 2014

Sql query to get the total number of columns of a specific table in sql server

Sql query or script to get or count the total number of columns of a given or specific user defined table in sql server

SELECT max_column_id_used AS TotalColumn
FROM sys.tables
WHERE name = 'TableName'

Note: In above query change the table name.
Also above query will return wrong data if any columns has been deleted after creation of table.

Here sys.tables is system view. sys.tables keeps only user defined tables.
max_column_id_used: It is into type column which store the total number of column in a table.

Another sql script:

SELECT COUNT(*) AS TotalColumn 
FROM sys.columns 
WHERE [object_id] = OBJECT_ID('TableName')

Or

SELECT COUNT(*) AS TotalColumn 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'TableName'
AND TABLE_SCHEMA = 'dbo'

Sys.columns and information_schema.columns are the system view which keeps the information about all the columns of a table.

Object_id: In sql server all the objects like tables, views, stored procedures etc. has the unique id which is known as object id. 

2 comments:

  1. this is not accurate, the column you are selecting is "Maximum column ID ever used by this table (http://msdn.microsoft.com/en-us/library/ms187406.aspx)".

    If columns are deleted, this number will stay the same.

    The correct query is:

    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name = 'TableNameHere'

    ReplyDelete
    Replies
    1. Yes exactly. I have modified the script. Thank you!!

      Delete