Script to get objects from all the databases

List of all tables in the all databases on the instance:

DECLARE @name VARCHAR(50) -- database name
declare @sql nvarchar(200)
Create Table #Temp(DatabaseName varchar(200),[Schema] varchar(20),TableName varchar(200),TableType varchar(15))
DECLARE db_cursor CURSOR FOR
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE databasePropertyex(name,'status')= 'online'
and name not in ('tempdb','master','msdb','model')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
set @SQL = 'use ['+@name+']'+
+'select * from information_schema.tables where table_type= ''BASE TABLE'' and Table_name not in (''dtproperties'')'
Insert into #Temp
Execute sp_executesql @SQL
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
select * from #Temp
Drop Table #Temp
List of all procedures in the all databases on the instance:
DECLARE @name VARCHAR(50) -- database name
declare @sql nvarchar(250)
Create Table #Temp(DatabaseName varchar(200),[Schema] varchar(20),ProcedureName varchar(200),ProcedureDefinition ntext)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE databasePropertyex(name,'status')= 'online'
and name not in ('tempdb','master','msdb','model')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
set @SQL = 'use ['+@name+']'+
+'select Routine_Catalog,Routine_schema,Routine_Name,'+
+'Routine_Definition from sysobjects A INNER JOIN '+
+'information_schema.Routines B on A.name= B.Routine_Name '+
+'where B.Routine_Type= ''Procedure'' and A.status >=0'
Insert into #Temp
Execute sp_executesql @SQL
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
select * from #Temp
Drop Table #Temp
List of all user defined functions in the all databases on the instance:
DECLARE @name VARCHAR(50) -- database name
declare @sql nvarchar(250)
Create Table #Temp(DatabaseName varchar(200),[Schema] varchar(20),ProcedureName varchar(200),ProcedureDefinition ntext)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE databasePropertyex(name,'status')= 'online'
and name not in ('tempdb','master','msdb','model')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
set @SQL = 'use ['+@name+']'+
+'select Routine_Catalog,Routine_schema,Routine_Name, '+
+'Routine_Definition from '+
+'information_schema.Routines B '+
+'where B.Routine_Type = ''Function'''
Insert into #Temp
Execute sp_executesql @SQL
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
select * from #Temp
Drop Table #Temp

Advertisement