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

Question

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

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

thadeushuck Ten Centuries Points: 1367 More actions · Answer 1

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

Dave Vroman SSC Eights! Points: 821 More actions · Answer 2

Change the statement

DECLARE c CURSOR FOR

to

DECLARE c CURSOR LOCAL FAST_FORWARD READ_ONLY FOR

and test the speed difference.

Mahmood.BilalB Ten Centuries Points: 1303 More actions · Answer 3

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

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 4

SSC Guru

Points: 76202

May 10, 2016 at 9:16 am

Thanks for the script.