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

• anonymous2009

  Ten Centuries

  Points: 1164

  More actions

  December 8, 2011 at 5:14 pm

  #251773

  Hello,

  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?

  - I have 10 databases within the same instance.

  How to get the table names, column names, and column lengths for a particular database (eg: XYZ) alone?

  Thanks!

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  December 8, 2011 at 6:08 pm

  #1419020

  For those objects within a single database ... check BOL (Books On Line) subject

  SQL Server 2008 Books Online (July 2009)

  sys.all_objects (Transact-SQL)

  or on line at:

  http://msdn.microsoft.com/en-us/library/ms178618.aspx

  Then link to sys.all_columns, again in BOL

  or on line at:

  http://msdn.microsoft.com/en-us/library/ms190324.aspx

  Or use something like this: (Note this does not give the size of VARCHAR, CHAR, NCHAR etc. columns)

  SELECT TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME

  , ORDINAL_POSITION , COLUMN_DEFAULT

  , IS_NULLABLE , DATA_TYPE

  FROM INFORMATION_SCHEMA.Columns

  WHERE TABLE_NAME NOT LIKE ('dt%') AND TABLE_NAME NOT LIKE ('sys%')

  ORDER BY TABLE_NAME, ORDINAL_POSITION

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]