none
how to get count of number of columns from table RRS feed

  • Question

  • How to get number of count of columns in table using TSQL.

    I tried this but it returns 0

     

    SELECT count(*) 

    FROM information_schema.columns 

    WHERE table_name = 'mytable'


    arun
    Wednesday, June 1, 2011 2:16 PM

Answers

  • Make you have the needed security to query the information_schema.  If you do not, it will return nothing over an error.

    Also, ensure the collation on the SQL Server or DB is not causing the table name to be case sensitive and that is why it is not finding the actual table


    Ted Krueger Blog on lessthandot.com @onpnt on twitter
    Please click the Mark as Answer button if a post solves your problem!
    • Marked as answer by Deputy12 Wednesday, June 1, 2011 4:16 PM
    Wednesday, June 1, 2011 2:25 PM
    Moderator
  • Yes I checked the Db its correct. I think issue is with the security 
    arun

    Yes, in SQL 2005 or later, you won't be able to get this information unless your login either owns that table or you have been granted some permission on the table.  See, for example,

    http://msdn.microsoft.com/en-us/library/ms187113%28v=SQL.100%29.aspx

    Tom

    • Marked as answer by Deputy12 Wednesday, June 1, 2011 4:16 PM
    Wednesday, June 1, 2011 3:55 PM

All replies

  • The above should work assuming you run this query in the right database and the table with the 'mytable' does exist.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Wednesday, June 1, 2011 2:18 PM
    Moderator
  • I am running in the same database where table exists but it gives 0 as output instead of 29
    arun
    Wednesday, June 1, 2011 2:20 PM
  • Perhaps database is different

    use db

    run your select


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, June 1, 2011 2:25 PM
    Answerer
  • Make you have the needed security to query the information_schema.  If you do not, it will return nothing over an error.

    Also, ensure the collation on the SQL Server or DB is not causing the table name to be case sensitive and that is why it is not finding the actual table


    Ted Krueger Blog on lessthandot.com @onpnt on twitter
    Please click the Mark as Answer button if a post solves your problem!
    • Marked as answer by Deputy12 Wednesday, June 1, 2011 4:16 PM
    Wednesday, June 1, 2011 2:25 PM
    Moderator
  • did it but same result !
    arun
    Wednesday, June 1, 2011 2:26 PM
  • Try

    SELECT * from INFORMATION_SCHEMA.COLUMNS WHERE UPPER(TABLE_NAME) = UPPER('myTable')
    

    I want to exclude possible problems with case-sensitive SQL Server installation.

    Alternatively, try

    select * from sys.columns where object_id = object_id('myTable')


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    Wednesday, June 1, 2011 2:27 PM
    Moderator
  • Yeah I think may be security is the issue.
    arun
    Wednesday, June 1, 2011 2:27 PM
  • Naomi 

    It still gives 0. I think this is issue with the security.


    arun
    Wednesday, June 1, 2011 2:29 PM
  • Hi

    The query you have is right one...

    Check with this

    Select * from sys.tables where name = 'mytable'

    if this returns no results you are in wrong DB 


    Regards, Kris. If this post answers your question, please click "Mark As Answer". If this post is helpful please click "Mark as Helpful".
    Wednesday, June 1, 2011 2:30 PM
  • What happens if you do:

     

    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE UPPER(TABLE_NAME) = 'CUSTOMERS'
    

     

    Replace CUSTOMERS with your table name (all CAPS)

     


    Pradeep, Microsoft MVP (Visual Basic)
    http://pradeep1210.wordpress.com
    Wednesday, June 1, 2011 2:33 PM
  • Hi Pardeep

     

    It still gives 0


    arun
    Wednesday, June 1, 2011 2:34 PM
  • Did you also try to check with sys.tables and sys.columns? Also, add

    select DB_NAME() before the query to verify the DB. Are you running these queries in SSMS directly or from the application?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Wednesday, June 1, 2011 2:37 PM
    Moderator
  • Yes I checked the Db its correct. I think issue is with the security 
    arun
    Wednesday, June 1, 2011 2:39 PM
  • Yes I checked the Db its correct. I think issue is with the security 
    arun

    Yes, in SQL 2005 or later, you won't be able to get this information unless your login either owns that table or you have been granted some permission on the table.  See, for example,

    http://msdn.microsoft.com/en-us/library/ms187113%28v=SQL.100%29.aspx

    Tom

    • Marked as answer by Deputy12 Wednesday, June 1, 2011 4:16 PM
    Wednesday, June 1, 2011 3:55 PM
  • Thanks everyone for your replies. The issue was with the security permission.
    arun
    Wednesday, June 1, 2011 4:17 PM