Q:

Select column names and top 1 records along, dynamically

I was trying to select top 1 column value from the table to have a glimpse of the data, based on the output 
(i.e. equivalently, 
  SELECT c.name FROM st.Name

This query retrieves column names and their data type along with the tables they're in. I am looking for an additional column that shows top 1 record from the columns. 
SELECT 
st.name 'Table Name', 
c.name 'Column Name', 
t.name 'Data Type'

FROM sys.columns c
INNER JOIN sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id 
LEFT OUTER JOIN sys.tables st ON st.object_id = i.object_id 

I have been trying to use a dynamic sql, but as it should put the table name in a single quotation as a string, it couldn't work; when I try to avoid that, it just displays the declared variable. 
Any idea is much appreciated. Thanks

A:

So the way this works is basically creates a bunch of selects like:
SELECT 'dbo' AS [Schema Name]
     , 'Table1' AS [Table Name]
     , 'Id' AS [Column Name]
     , 'bigint' AS [Data Type]
     , (SELECT TOP 1 CONVERT(NVARCHAR(MAX), Id) FROM [dbo].[Table1]) AS [Top 1 Value] 

UNION ALL

-- Another table

Values are converted into NVARCHAR(MAX) because column type in an union has to match and I guess that's the best bet. 
Here goes:
DECLARE @query NVARCHAR(MAX) = ''
SELECT @Query += 
'SELECT ' + '''' + sch.name + '''' + ' AS [Schema Name],' + CHAR(13)+CHAR(10)
          + '''' + st.name  + '''' + ' AS [Table Name],' + CHAR(13)+CHAR(10)
          + '''' + c.name   + '''' + ' AS [Column Name],' + CHAR(13)+CHAR(10)
          + '''' + t.name   + '''' + ' AS [Data Type],' + CHAR(13)+CHAR(10)
          + '(SELECT TOP 1 CONVERT(NVARCHAR(MAX), ' + c.name + ') FROM ' + QUOTENAME(sch.name) + '.' + QUOTENAME(st.name) + ') AS [Top 1 Value] ' + CHAR(13)+CHAR(10)
          + 'UNION ALL'+CHAR(13)+CHAR(10)
FROM sys.columns c
JOIN sys.types t ON c.user_type_id = t.user_type_id
JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id 
JOIN sys.tables st ON st.object_id = i.object_id 
JOIN sys.schemas sch ON sch.schema_id = st.schema_id

-- Get rid of trailing UNION ALL
SET @Query = LEFT(@Query, LEN(@Query) - LEN('UNION ALLxx'))

PRINT @query
EXEC sp_executesql @query

Consider running with TOP 10 or some such first to make sure it's producing the right results.

