Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
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
add comment

1 Answer

up vote 3 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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