Saturday, 16 February 2013

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
--Script to make a list of all tables
--in all datatabses
--and store them in tab_alldatabases

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
exec (@script)

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

No comments:

Post a Comment