Select the top 5 records and the specific column from each table in the same database

Question

I have 50+ tables in a database.

I don't want to use the following SQL on each table in a database.

select top 10 * from {table_name}

So, is it possible to display the first 10 records PLUS only show the 2nd and 3rd column for each table inside the same database using sql?

(If the table does not have the 2 columns - it will display the 1st and 2nd column. If the table have 1 column, it will display the 1st column. If the table have more than 2 column, it will display the 2nd and 3rd column. If the table does not have any column, then display nothing.)

Answer 1

This will work on SQL Server 2005 and up:

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';

SELECT @sql = @sql + N'

SELECT TOP (10) ''' + REPLACE(name, '''','') + ''','
  + STUFF(
    (SELECT TOP (3) ', ' + name FROM sys.columns 
      WHERE [object_id] = t.[object_id]
      ORDER BY column_id
      FOR XML PATH, TYPE).value('.[1]', 'nvarchar(max)'
  ), 1, 2, '') + ' 
    FROM ' + QUOTENAME(SCHEMA_NAME([schema_id]))
  + '.' + QUOTENAME(name) + ';'
FROM sys.tables AS t;

PRINT @sql;
--EXEC sp_executesql @sql;

When you're satisfied with the PRINT output (note that it will truncate at 8K even though the command should be a lot larger than that for 50+ tables), uncomment the EXEC.