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.)

  • 1
    Using the knowledge you gained in your other similar question, what have you tried so far? – Jon Seigel Feb 27 '13 at 18:18
  • 1
    Do you mean "any 10" since without an ORDER BY there is no such concept as "first 10"... – Aaron Bertrand Feb 27 '13 at 19:59
  • @AaronBertrand Thanks Aaron Bertrand for the solution, otherwise, I really do not know how can I use sp_MSforeachtable form my other similar question (dba.stackexchange.com/questions/35529/…) – Jack Feb 28 '13 at 1:00
  • @Jack yes, it would be a little more complex to pass this type of script into sp_MSforeachtable. – Aaron Bertrand Feb 28 '13 at 3:31

This will work on SQL Server 2005 and up:

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.