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