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, 2022 in Database by Kithuzzz
• 38,020 points
744 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, 2022 by narikkadan
• 63,680 points

Related Questions In Database

0 votes
0 answers

What's the difference between a temp table and table variable in SQL Server?

We can build temp tables in SQL ...READ MORE

Feb 25, 2022 in Database by Vaani
• 7,070 points
613 views
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, 2022 in Database by Kithuzzz
• 38,020 points
786 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, 2022 in Database by Kithuzzz
• 38,020 points
436 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, 2022 in Database by Kithuzzz
• 38,020 points
809 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
773 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, 2022 in Database by Vaani
• 7,070 points
795 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, 2022 in Database by Vaani
• 7,070 points
519 views
0 votes
1 answer

What is a stored procedure?

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

answered Feb 4, 2022 in Database by Neha
• 9,020 points
1,027 views
0 votes
1 answer

Difference between numeric, float and decimal in SQL Server

use the float or real data types only if the precision provided by decimal (up ...READ MORE

answered Sep 11, 2022 in Database by narikkadan
• 63,680 points
2,225 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, 2022 in Database by narikkadan
• 63,680 points
2,441 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