?
Solved

Query the sys tables from SQL Server 2005

Posted on 2011-04-25
8
Medium Priority
?
301 Views
Last Modified: 2012-08-13

I want to query the following from the sys tables for all tables in a  SQL Server 2005 Database:

Table name, column name, Datatype, Extended description

Can someone help me write the query?
0
Comment
Question by:skaleem1
8 Comments
 
LVL 16

Expert Comment

by:Imran Javed Zia
ID: 35460835
Hi,

Please try following

select * From Information_Schema.Columns

if you need only Table info then use

select * From Information_Schema.Tables

Thanks
0
 
LVL 1

Author Comment

by:skaleem1
ID: 35461015
How can I get the extended description for each column?
0
 
LVL 6

Expert Comment

by:LCSandman8301
ID: 35461025
SELECT C.TABLE_NAME,C.COLUMN_NAME, C.DATA_TYPE, COALESCE(CONVERT(VARCHAR(10),CHARACTER_MAXIMUM_LENGTH), CONVERT(VARCHAR(5),NUMERIC_PRECISION)+','+CONVERT(VARCHAR(5),NUMERIC_SCALE), CONVERT(VARCHAR(10),DATETIME_PRECISION)) AS EXTDESC
FROM INFORMATION_SCHEMA.COLUMNS C

i didn't quite understand what you meant by extended description but i hope this helps.
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 1

Author Comment

by:skaleem1
ID: 35461359
By extended description I mean the custom description you can add for each column of a table when you go to the table design mode (enter the description in the lower half pane named Column properties -  right hand side column, for the description property)
0
 
LVL 6

Accepted Solution

by:
hyphenpipe earned 2000 total points
ID: 35461801
Here.
select  [table name] = object_name(c.object_id), 
        [column name] = c.name, 
        [description] = ex.value  
from sys.columns c  
left outer join sys.extended_properties ex on ex.major_id = c.object_id 
 and ex.minor_id = c.column_id

Open in new window

0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35464779
Select sys.objects.Name as Tablename, sys.columns.name as ColumnName, sys.systypes.name as DataType from sys.objects
inner join sys.columns on sys.objects.object_id =  sys.columns.object_id
inner join sys.systypes on sys.columns.system_type_id =  sys.systypes.xtype
where sys.objects.type = 'u'
0
 
LVL 1

Author Comment

by:skaleem1
ID: 35771902
This is what I came up with finally:

SELECT  [Table] = OBJECT_NAME(c.object_id),
            [Column] = c.name,  t.name AS [DataType],
            [Description] = ex.value  
      FROM  
            sys.columns c
      LEFT OUTER JOIN  
            sys.extended_properties ex
      ON  
            ex.major_id = c.object_id
            AND ex.minor_id = c.column_id  
            AND ex.name = 'MS_Description'  
      JOIN sys.types AS t ON c.user_type_id=t.user_type_id

      WHERE  
            OBJECTPROPERTY(c.object_id, 'IsMsShipped')=0  
0
 
LVL 1

Author Closing Comment

by:skaleem1
ID: 35771922
Thanks, yours is the one I finally utilized.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question