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:

etc.

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

CodePudding user response：

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