Excel - Converting Datasource into Data Dictionary

This may be the wrong place to ask this, but I'm trying to take data from a SQL Datasource, place it into Excel, and automatically generate a Data Dictionary from the aforementioned SQL Datasource. Has anyone done this or know how to do thi开发者_运维百科s?

[EDIT]

What I need is all of the table names, and all of the columns in each table (each table will be it's own excel tab), and for each column, I need the data type and whether or not it is nullable

Assuming you mean a listing of columns in a database with the column meta data and this is a one time need and doesn't need much in the way of automation.

I would execute the following in SSMS

SELECT Table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'


DECLARE @tableName sysname
DECLARE table_cursor CURSOR FAST_FORWARD  FOR 
SELECT Table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

OPEN table_cursor

FETCH NEXT FROM table_cursor 
INTO @tableName

WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT TABLE_NAME,
            COLUMN_NAME,
            Data_type + COALESCE('(' + Cast(Character_Maximum_length as varchar) + ')' ,'') DataType, 
            IS_NULLABLE

    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE
    TABLE_NAME = @tableName



    FETCH NEXT FROM table_cursor 
    INTO @tableName
END
CLOSE table_cursor
DEALLOCATE table_cursor

This will output multiple results set the first one is a table name then everyone after would be the column data you mentioned.

You could get rid of the cursor and just have two outputs if you prefer but I find it easier to press Ctrl+A Ctrl+Con each grid

Conrad is definitely on the right track, but if you need the column information, you may want to use information_schema.columns instead of information_schema.tables. The information_schema.columns will give you data type, whether it is nullable, the table the column is in, and more for all tables in the database.

If you need this broken up by table so you can put it in each table, I would use a loop to get a separate result set from information_schema.columns for each table.

I tried this way, and I got what i need.

SELECT T.NAME 'TABLE',C.NAME 'COLUMN' ,S.NAME 'DATATYPE',C.is_nullable 'ISNULL'
FROM SYS.TABLES T INNER  JOIN SYS.COLUMNS C ON T.OBJECT_ID=C.OBJECT_ID 
INNER JOIN SYS.TYPES S ON C.SYSTEM_TYPE_ID=S.SYSTEM_TYPE_ID
WHERE T.TYPE='U' 
ORDER BY T.NAME,C.NAME