Creating a Data Dictionary with example data (SQL)

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?

Answer

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