question

Reno Lindberg avatar image
Reno Lindberg asked

Selecting data from tablenames and columns listed in a table

Hello all I have searced the net, but not found an answer to my problem .. so here it comes :-) I managed to list all tables in my SQL server containing timetamps. Now I want to list the row containing the oldest timestamp for each table on my server. As well it could be interesting to see all different dates in each table on the server Of course, tables are created and deleted from time to time, and the table containing tablenames and names of the timestamp columns is a dynamic view. How to do that? Table contaning tablenames: Tablenames.Tablename contains all tablenames containing timestamps in the server Tablenames.Timestamp contains the columnname of the timestamp for each table. Se forward to hear your suggestions Best wishes Reno Lindberg
selectdynamictablenames
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Pavel Pawlowski avatar image
Pavel Pawlowski answered
Here you have a simple solution using cursor. This will select the row with min value in the timestamp column of your tables. It also prints the constructed SQL. You can play more with the example and add other queries to list the different dates etc. Also it is possible to rewrite it to bypass using a cursor, but even the sp_MSForEachtable uses a cursor internally. To apply it directly to your table, change the @TableNames in the cursor declaration to TableNames and uncomment the EXEC statement DECLARE @TableNames TABLE(TableName varchar(20), [TimeStamp] varchar(20)) INSERT INTO @TableNames(TableName, TimeStamp) SELECT 'Table_A', 'Column_A' UNION ALL SELECT 'Table_B', 'Column_B' DECLARE @tableName sysname DECLARE @col sysname declare @sql nvarchar(4000) DECLARE cr CURSOR FAST_FORWARD FOR SELECT TableName, [TimeStamp] FROM @TableNames OPEN cr FETCH NEXT FROM cr INTO @tableName, @col WHILE @@FETCH_STATUS = 0 BEGIN SET @sql = 'SELECT TOP 1 * FROM ' + @tableName + ' ORDER BY ' + QUOTENAME(@col) PRINT @sql --EXEC sp_executesql @sql FETCH NEXT FROM cr INTO @tableName, @col END CLOSE cr DEALLOCATE cr
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hei Pavel YES it seems to work .. but I now get the full row of data for that perticular timestamp, but with no information on from which table it comes. All in all, the solution works, but with 1000 tables, its hard to find out which lines are from which tables.. Do you have a solution on how to add the tablename as an entry in a new column or so??
0 Likes 0 ·
yes, simply add the table name into the select. SET @sql = 'SELECT TOP 1 ''' + @tableName + ''' AS TableName, * FROM ' + @tableName + ' ORDER BY ' + QUOTENAME(@col)
0 Likes 0 ·
sp_lock avatar image
sp_lock answered
You could try using the sp_MSForEachtable proc if all your timestamps are of the same name example EXEC sp_MSForEachtable 'select ''?'', max (timestamp) from ?' This is assuming that the timestamp field is a datetime field and not an epoch type.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Reno Lindberg avatar image
Reno Lindberg answered
Unfortunately, not all columns have the same name. I find the columnnames by using a LIKE statement. Not all tables in the database holds timestamps. When using your code all tables are listed, but tables without timestamps have empty entries. Some timestamps are put in as text, but these can be delt with by using CAST I guess by rearranging a bit, your code can be used, the problem is to differ by the about 5 different columnnames holding timestamps in the database .. do you have an idea how that can be solved?
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Yes, I know, that it was to the Jonlee Lockwood solution.. Therefore I wrote about the comment.. When you write the comment everybody knows to which answers it belongs like this one.. :-)
1 Like 1 ·
I've posted the solution which uses directly your view with list of tables and timestamp columns. It returs whole row with min. timestamp. In the example is a table variable with demo tables and columns. In your real code you will use your view directly. and uncomment the EXEC command to run the query as in the example the tables doesn't exists. Also when responding to an answer, use Add Comment to the answer instead adding another answer.
0 Likes 0 ·
Sorry Pavel, my comment was regarding the solution from Jonlee Lockwood ..
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.