Creating a Data Dictionary with example data (SQL)

user_19784368 0
Viewed 46

I am looking to run a script on a SQL database to create a Data Dictionary with an example of the data for each field.

To keep it simple I would just like to include the data from the first row of each table along with each table name and column name

So something like this:

Table Name Field Name Example Data
Customer ID CU1
Customer Title Mrs
Customer Name Anne
Customer Order No ORD1

etc.

Is there an easy way to do this with a SQL script?

tsql sql
1 Answers

Somebody smarter than me could probably optimize this and remove the cursor but the Dynamic SQL was giving me a headache. I think in this scenario, a cursor is acceptable

DROP TABLE IF EXISTS ##DataDictionary
CREATE TABLE ##DataDictionary (TableName SYSNAME, ColumnName SYSNAME, SampleData NVARCHAR(MAX))
DECLARE @TableName SYSNAME
DECLARE @ColumnName SYSNAME
DECLARE @SQL NVARCHAR(MAX)

DECLARE cur CURSOR FOR
  SELECT t.name AS TableName,c.Name AS ColumnName 
  FROM sys.tables t
    JOIN sys.columns c ON t.object_id = c.object_id

OPEN cur

FETCH cur  INTO @TableName,@ColumnName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = ''
    SELECT @SQL = '
    INSERT INTO ##DataDictionary(TableName,ColumnName,SampleData)
    SELECT '''+@TableName+''','''+@ColumnName+'''
    ,(SELECT TOP 1 '+QUOTENAME(@ColumnName)+' FROM '+QUOTENAME(@TableName)+' ORDER BY NEWID()) -- NewID randomly selects a sample row
    '
    print @SQL
    EXEC (@SQL)

    FETCH cur  INTO @TableName,@ColumnName
END

CLOSE cur
DEALLOCATE cur

SELECT * from ##DataDictionary
user_127752910
Related