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

• kamel Gazzah

  SSC Rookie

  Points: 43

  More actions

  June 27, 2012 at 1:20 pm

  #263326

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

• thadeushuck

  Ten Centuries

  Points: 1367

  More actions

  July 2, 2012 at 7:27 am

  #1507727

  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

  July 2, 2012 at 10:57 am

  #1507838

  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

  July 3, 2012 at 12:12 am

  #1508025

  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

  May 10, 2016 at 9:16 am

  #1877834

  Thanks for the script.