CODEWITHSUNDEEP
sql-servert-sql
Magnus Karlsson
Magnus Karlsson

Reputation: 3579

Get first row in table while printing out Table name and Column name

I need to get a quick overlook of the data in a MS SQL datase and found the following code which gives me all but the last column I need. This third column should show data from the first row. 

SELECT TABLE_NAME, COLUMN_NAME  FROM INFORMATION_SCHEMA.COLUMNS

enter image description here

So my question is how I should formulate the SQL query to get a third column with data from the first row)?

//Update This code gives me all I want except what table the data comes from. (In a similar question answered by Yaroslav at Select the first 3 rows of each table in a database)

DECLARE @sql VARCHAR(MAX)=''; 
SELECT @sql=@sql+'SELECT TOP 3 * FROM '+'['+SCHEMA_NAME(schema_id)+'].['+name+']'+';'
FROM sys.tables
EXEC(@sql)

Upvotes: 2

Views: 1555

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

One approach, if I get this correctly, was an undocumented stored procedure sp_MsForeachTable. The questionmark is a placeholder for the table's name there:

Hint: edit "YourDataBase"...

EXEC sp_msforeachtable 'USE YourDataBase;SELECT TOP 1 ''?'' AS TableName, * FROM ?';

With this query you can explore all first rows easily

CREATE TABLE #test(TableName NVARCHAR(MAX),Content XML)

EXEC sp_msforeachtable 
'USE YourDataBase;INSERT INTO #test SELECT ''?'' AS TableName, (SELECT TOP 1 * FROM ? FOR XML PATH(''row''))';

SELECT * FROM #test;

UPDATE

Your own code would return the table's name also. Try this

DECLARE @sql VARCHAR(MAX)=''; 
SELECT @sql=@sql+'SELECT TOP 3 ''' + t.[name] + ''' AS TableName, * FROM '+'['+SCHEMA_NAME(schema_id)+'].['+name+']'+';'
FROM sys.tables t
EXEC(@sql)

Upvotes: 1

user8513344
user8513344

Reputation:

I know this isnt much but it build you a select statement, you can then loop through each statement exec each statement or write a union, But I guess it could be a good starting point

Edit: You could also write a loop and exec each statement in a loop insert the value into a final table, Then just select from that table and you should be good.

SELECT
    t.String
   ,t.q
   ,t.TABLE_NAME
   ,t.q2
   ,t.tbname
   ,t.com2
   ,t.q4
   ,t.COLUMN_NAME
   ,t.q5
   ,t.Columnname
   ,t.com3
   ,t.ColName
   ,t.[From]
   ,t.FromSelect
FROM (SELECT
        'Select top 1 ' AS String
       ,'''' q
       ,TABLE_NAME
       ,'''' q2
       ,'as TableName'
        as tbname
       ,',' com2

       ,'''' q4
       ,COLUMN_NAME
       ,'''' q5
       ,'as COLUMN_NAME'
       as Columnname
       ,',' com3
       ,COLUMN_NAME as ColName
       , 'Value From ' as [From]
       ,TABLE_NAME as FromSelect
       ,ROW_NUMBER() OVER (PARTITION BY TABLE_NAME ORDER BY TABLE_NAME DESC, COLUMN_NAME) rn
    FROM INFORMATION_SCHEMA.COLUMNS c
    ) t
WHERE rn = 1;

The result will be something like this for each table.

Select top 1    '   zipcodes    '   as TableName    ,   '   CITY    '   as COLUMN_NAME  ,   CITY    Value From  zipcodes
Select top 1    '   _BHCAMERAPRICE  '   as TableName    ,   '   _BHID   '   as COLUMN_NAME  ,   _BHID   Value From  _BHCAMERAPRICE

Upvotes: 0

Related Questions