Technical Article

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

,

First I create a table called tab_alladatabases which contains 3 columns

1) db: the dta base name

2) tab : the table name

3) cdate the creation date of teh table

Then I construct a script which is getting the table names of all data bases by using master..sysdatabases and sysobjects tables

This script uses a cursor which gets as result a sql script

The sql script inserts into the table tab_alldatatbases the results

Create proc usp_alldatabases
as
begin
--Script to make a list of all tables
--in all datatabses
--and store them in tab_alldatabases
--By KAMEL AL GAZZAH, Tunisia
--kamelgazzah@gmail.com

declare @script as nvarchar(2000)
if  exists(select 1 from sysobjects where name='tab_alltables') drop table tab_alltables
create table tab_alltables (db nvarchar(1000), tab nvarchar(1000),cdate datetime)
declare c cursor  for
select 'insert into tab_alltables (tab,db,cdate) select name,'''+name+ ''',crdate from ' +name+'..sysobjects where xtype=''u''' from master..sysdatabases  where dbid>4
open c
fetch c into @script
while @@fetch_status=0
begin
exec (@script)

print @script
fetch c into @script
end
close c deallocate c
select * from tab_alltables --You can add your criteria here to serach for a particular table name
end

Rate

3.75 (4)

You rated this post out of 5. Change rating

Share

Share

Rate

3.75 (4)

You rated this post out of 5. Change rating