To get total number of columns in a table in sql

Question

I need a query in sql to get total columns in a table Can anybody help

User · Answer

This query gets the columns name  SELECT COLUMN NAME FROM INFORMATION SCHEMA Columns where TABLE NAME    YourTableName    And this one gets the count   SELECT Count    FROM INFORMATION SCHEMA Columns where TABLE NAME    YourTableName

User · Answer

Select Table Name  Count    As ColumnCount From Information Schema Columns Group By Table Name Order By Table Name   This code show a list of tables with a number of columns present in that table for a database    If you want to know the number of column for a particular table in a database then simply use where clause e g  where Table Name  name your table

User · Answer

Correction to top query above  to allow to run from any database  SELECT COUNT COLUMN NAME  FROM   database   INFORMATION SCHEMA COLUMNS WHERE  TABLE CATALOG    database  AND TABLE SCHEMA    dbo  AND TABLE NAME    table

User · Answer

You can try below query   select    count     from    all tab columns where    table name    your table

User · Answer

In my situation  I was comparing table schema column count for 2 identical tables in 2 databases  one is the main database and the other is the archival database  I did this  SQL 2012     DECLARE  colCount1 INT  DECLARE  colCount2 INT   SELECT  colCount1   COUNT COLUMN NAME  FROM MainDB INFORMATION SCHEMA COLUMNS WHERE TABLE NAME    SomeTable   SELECT  colCount2   COUNT COLUMN NAME  FROM ArchiveDB INFORMATION SCHEMA COLUMNS WHERE TABLE NAME    SomeTable    IF   colCount1     colCount2  THROW 5000   Number of columns in both tables are not equal  The archive schema may need to be updated    16    The important thing to notice here is qualifying the database name before INFORMATION SCHEMA  which is a schema  like dbo   This will allow the code to break  in case columns were added to the main database and not to the archival database  in which if the procedure were allowed to run  data loss would almost certainly occur

User · Answer

The below query will display all the tables and corresponding column count in a database schema  SELECT Table Name  count    as  No of Columns  FROM INFORMATION SCHEMA COLUMNS WHERE table schema    dbo  -- schema name group by table name

User · Answer

It can be done using -     SELECT COUNT COLUMN NAME   NO OF COLUMN  FROM INFORMATION SCHEMA COLUMNS WHERE TABLE NAME    Address

User · Answer

In MS-SQL Server 7    SELECT count    FROM INFORMATION SCHEMA COLUMNS WHERE TABLE NAME    mytable

User · Answer

SELECT COUNT COLUMN NAME   FROM INFORMATION SCHEMA COLUMNS  WHERE TABLE CATALOG    database  AND TABLE SCHEMA    dbo  AND TABLE NAME    table

[sql-server] To get total number of columns in a table in sql

Examples related to sql-server