Make a list of all tables in all data bases with creation date

  • Comments posted to this topic are about the item Make a list of all tables in all data bases with creation date

  • DECLARE @db_name sysname, @SQL nvarchar(max)

    --

    IF OBJECT_ID('tempdb..#Results') IS NOT NULL

    DROP TABLE #Results;

    IF OBJECT_ID('tempdb..#Databases') IS NOT NULL

    DROP TABLE #Databases;

    --

    CREATE TABLE #Results (DatabaseName sysname, TableName NVARCHAR(128), crdate datetime);

    CREATE TABLE #Databases (databaseName sysname);

    --

    INSERT INTO #Databases

    SELECT name FROM sys.databases WHERE name NOT IN ('master', 'tempdb', 'msdb', 'model');

    WHILE (SELECT COUNT(*) FROM #Databases) > 0

    BEGIN

    SET @db_name = (SELECT TOP 1 databaseName FROM #Databases);

    SET @SQL = 'USE ['+@db_name+'];

    INSERT INTO #Results Select '''+@db_name+''' as [dbname], name as [tblname], crdate from sysobjects'

    --

    EXEC (@SQL)

    --

    DELETE #Databases

    WHERE databaseName = @db_name

    END;

    --

    SELECT * from #Results;

    just another cursor rewrite for fun..

  • Change the statement

    DECLARE c CURSOR FOR

    to

    DECLARE c CURSOR LOCAL FAST_FORWARD READ_ONLY FOR

    and test the speed difference.

  • I have tested the code and it is giving an error. I think end statement is not required at the last line, just comment that out and it works

  • Thanks for the script.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply