How do I read meta-data from a SQL Server database table?

Question

I am a complete beginner at SQL Server. I was given a database that was in SQL Server backup file format. I figured out how to restore the databases, but now I am looking to export the tables (eventually to Stata .dta files)

I am confused how to view and extract any meta-data my SQL Server database might contain. For example, I have one column labeled Sex and the values are 1 and 2. However, I have no idea which number refers to male and which refers to female. How would I view the column description (if it exists) to see if there is any labeling that might be able to clarify this issue?

Edit: Quick question. If I use the Import/Export Wizard, will that automatically extract the meta-data?

score 2 · Accepted Answer · answered Apr 09 '14 at 14:54

This is by far the best post for exporting to excel from SQL:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

To export data to new EXCEL file with heading(column names), create the following procedure

create procedure proc_generate_excel_with_columns
(
    @db_name    varchar(100),
    @table_name varchar(100),   
    @file_name  varchar(100)
)
as

--Generate column names as a recordset
declare @columns varchar(8000), @sql varchar(8000), @data_file varchar(100)
select 
    @columns=coalesce(@columns+',','')+column_name+' as '+column_name 
from 
    information_schema.columns
where 
    table_name=@table_name
select @columns=''''''+replace(replace(@columns,' as ',''''' as '),',',',''''')

--Create a dummy file to have actual data
select @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.xls'

--Generate column names in the passed EXCEL file
set @sql='exec master..xp_cmdshell ''bcp " select * from (select '+@columns+') as t" queryout "'+@file_name+'" -c'''
exec(@sql)

--Generate data in the dummy file
set @sql='exec master..xp_cmdshell ''bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c'''
exec(@sql)

--Copy dummy file to passed EXCEL file
set @sql= 'exec master..xp_cmdshell ''type '+@data_file+' >> "'+@file_name+'"'''
exec(@sql)

--Delete dummy file 
set @sql= 'exec master..xp_cmdshell ''del '+@data_file+''''
exec(@sql)

After creating the procedure, execute it by supplying database name, table name and file path

EXEC proc_generate_excel_with_columns 'your dbname', 'your table name','your file path'

Thank you for your help! This is very useful, although because I am literally a complete beginner, I do not completely understand this solution. Do I have to replace all the entries starting with @ with my specific values? For example for @db_name, do I replace that with my actual data base name? Also, will this also export the meta-data with the Table to Excel? — Vincent, Apr 10 '14 at 02:45
Yes you have to replace all the entries starting with @ with specific values. This script will help you to export the meta-data with the Table to Excel — sharkbait, Apr 10 '14 at 15:02
I see. Great, thank you for the help, and I will give this a try! When replacing with the specific values do I literally just type the name or is there specific syntax that I should be aware of? — Vincent, Apr 11 '14 at 04:59
You can find more informations here in this similar answer http://stackoverflow.com/questions/9086880/t-sql-export-to-new-excel-file You have to read all the steps and set all the variables before to launch the script. — sharkbait, Apr 11 '14 at 17:46
One more quick question. If I use the Import/Export Wizard, will that automatically extract the meta-data? — Vincent, Apr 20 '14 at 04:58

score 0 · Answer 2 · answered Apr 09 '14 at 16:26

0

If your database admin and / or the developer have used what is called the extended properties to document the database tables, then you can retrieve those descriptions with this T-SQL statement:

SELECT 
    TableName = t.Name,
    ColumnName = c.Name,
    ep.class_desc,
    ep.value
FROM 
    sys.extended_properties ep
INNER JOIN 
    sys.tables t ON ep.major_id = t.object_id
INNER JOIN 
    sys.columns c ON ep.major_id = c.object_id AND ep.minor_id = c.column_id

It uses some of the SQL Server catalog views (sys.tables etc.) that contain all the valuable metadata information about SQL Server database objects.

answered Apr 09 '14 at 16:26

marc_s

732,580
175
1,330
1,459

Hi. Thanks for the help. I apologize because I am literally a complete beginner. So can I just open a new query and copy and paste this in? Or do I have to change t.Name to my table name? – Vincent Apr 10 '14 at 02:52
@Vincent: open a query window, make sure you're in the right database, and then execute this T-SQL statement – marc_s Apr 10 '14 at 04:47
One more quick question. If I use the Import/Export Wizard, will that automatically extract the meta-data? Thanks for the help! – Vincent Apr 20 '14 at 04:59

score 0 · Answer 3 · answered Apr 09 '14 at 16:36

Assuming you have correct foreign keys in place you can search for columns that are referenced using the following T-SQL:

SELECT constraint_object_name
     , constraint_object_name
     , referenced_object_name
     , referenced_column_name
FROM   (
        SELECT Object_Name(parent_object_id) As constraint_object_name
             , constraint_columns.name As constraint_column_name
             , Object_Name(referenced_object_id) As referenced_object_name
             , referenced_columns.name As referenced_column_name
        FROM   sys.foreign_key_columns
         INNER
          JOIN sys.columns As constraint_columns
            ON constraint_columns.object_id = foreign_key_columns.parent_object_id
           AND constraint_columns.column_id = foreign_key_columns.parent_column_id
         INNER
          JOIN sys.columns As referenced_columns
            ON referenced_columns.object_id = foreign_key_columns.referenced_object_id
           AND referenced_columns.column_id = foreign_key_columns.referenced_column_id
       ) As foreign_key_details
WHERE  'SearchingForThisColumnHere' IN (constraint_column_name, referenced_column_name)

To simply search for columns that share a name

SELECT Object_Name(object_id) As object_name
     , name As column_name
FROM   sys.columns
WHERE  name = 'SearchingForThisColumnHere'

How do I read meta-data from a SQL Server database table?

3 Answers3