DMV to get all column names in the database along with its table name and column length


  I have a database xyz and need to get all the table names, its column names and its length.
  Which DMV should I use?
  Is there a DMV query that I can use for this?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sath350163Author Commented:
Want to add one more additional question to the above.
I have 10 databases in the same instance. How to get the table names, column names, and column lengths for a particular database (eg: XYZ) alone?

Anthony PerkinsCommented:
>>DMV to get all column names in the database along with its table name and column length<<
DMV (Dynamic Management Views) have nothing to do with the schema of the table.  All you need are the Catalog Views such as:
SELECT TableName, ColumnName,
        c.max_length ColumnLength
FROM    sys.tables t
        INNER JOIN sys.columns c ON t.object_id = c.object_id
Scott PletcherSenior DBACommented:
sys.objects include tables and other objects; I'm not aware of a sys.tables in SQL Server.
SELECT AS Table_Name, AS Column_Name, AS Data_Type,
    CASE WHEN LIKE '%char%' THEN 
        CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS varchar(5)) END
        ELSE CAST(c.precision AS varchar(5)) END AS Data_Length,
    c.scale AS Num_Decimals
FROM XYZ.sys.columns c
INNER JOIN XYZ.sys.objects o ON
    o.object_id = c.object_id
INNER JOIN XYZ.sys.types t ON
    t.system_type_id = c.system_type_id AND
    t.user_type_id = c.user_type_id
    o.type_desc = 'USER_TABLE'

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
But there *is* a sys.tables.  Looks like a system view extending sys.objects that already filters for only type_desc = 'USER_TABLE'.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.