SQL Server Extract Table Meta-Data description fields and their data types

0 votes
I'm looking for a technique to retrieve data from SQL Server about my tables (2008). The information I require must include the table's description (completed from the Description property in the Properties Window), a list of the table's fields, and a description of the various data types for each field.

Is there a way for me to obtain this meta information? I assume I must utilize a system service, but I'm not sure which one.
Sep 16 in Database by Kithuzzz
• 12,240 points
13 views

1 answer to this question.

0 votes

Unfortunately, you have to utilize sysobjects/syscolumns to retrieve the ids in order to get the description data:

SELECT      u.name + '.' + t.name AS [table],
            td.value AS [table_desc],
            c.name AS [column],
            cd.value AS [column_desc]
FROM        sysobjects t
INNER JOIN  sysusers u
    ON      u.uid = t.uid
LEFT OUTER JOIN sys.extended_properties td
    ON      td.major_id = t.id
    AND     td.minor_id = 0
    AND     td.name = 'MS_Description'
INNER JOIN  syscolumns c
    ON      c.id = t.id
LEFT OUTER JOIN sys.extended_properties cd
    ON      cd.major_id = c.id
    AND     cd.minor_id = c.colid
    AND     cd.name = 'MS_Description'
WHERE t.type = 'u'
ORDER BY    t.name, c.colorder

You can do it with info-schema, but you'd have to concatenate etc to call OBJECT_ID()

answered Sep 17 by narikkadan
• 20,880 points

Related Questions In Database

0 votes
0 answers
0 votes
0 answers

What is the difference between drop table and delete table in SQL Server?

What is the distinction between the following ...READ MORE

Aug 9 in Database by Kithuzzz
• 12,240 points
71 views
0 votes
0 answers

Backup a single table with its data from a database in sql server 2008

I want to use a script to ...READ MORE

Aug 29 in Database by Kithuzzz
• 12,240 points
20 views
0 votes
0 answers

Check if table exists and if it doesn't exist, create it in SQL Server 2008

I'm using SQL Server 2008 to create ...READ MORE

Sep 2 in Database by Kithuzzz
• 12,240 points
29 views
0 votes
1 answer

Calculate Time Intersection to Correlate Sequences of Independent Events

I think this solution requires a CROSS JOIN implementation. ...READ MORE

answered Oct 26, 2018 in Power BI by Upasana
• 8,620 points
330 views
0 votes
1 answer

How do I UPDATE from a SELECT in SQL Server?

MERGE INTO YourTable T USING ...READ MORE

answered Feb 3 in Database by Vaani
• 7,020 points
121 views
0 votes
0 answers

How do I UPDATE from a SELECT in SQL Server?

INSERT INTO Table (col1, col2, col3) SELECT col1, ...READ MORE

Feb 4 in Database by Vaani
• 7,020 points
45 views
0 votes
1 answer

What is a stored procedure?

A stored procedure is a set of ...READ MORE

answered Feb 4 in Database by Neha
• 8,920 points
216 views
0 votes
1 answer

Add a column with a default value to an existing table in SQL Server

Syntax: ALTER TABLE {TABLENAME} ADD {COLUMNNAME} {TYPE} {NULL|NOT ...READ MORE

answered Sep 12 in Database by narikkadan
• 20,880 points
63 views
0 votes
1 answer

Is there a Boolean data type in Microsoft SQL Server like there is in MySQL?

The BIT datatype can be used to ...READ MORE

answered Sep 15 in Database by narikkadan
• 20,880 points
56 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP