Get DBObjects with Specified Owner

,

A database can have objects with multiple owners. The script will give you the list of all the TABLES, VIEWS and PROCEDURES having the specified owner. Just input the owner name and it will give you the list of all the Objects with that owner. This scope of the script is limited to the TABLES, VIEW and PROCEDURES.

-- Proc_GetDBObjectsWithSpecifiedObjectOwner 'surveyuser'

Create Proc Proc_GetDBObjectsWithSpecifiedObjectOwner (@ObjectOwner nvarchar(517))
as
Begin
	if exists (select name from tempdb..sysobjects where name like '#tmpSP_TablesOutput%')
	Begin
		drop table #tmpSP_TablesOutput
	End
	
	create table #tmpSP_TablesOutput
	(
	TABLE_QUALIFIER sysname,
	TABLE_OWNER sysname,
	TABLE_NAME sysname, 
	TABLE_TYPE varchar(32), 
	REMARKS varchar(254) 
	)
	insert into #tmpSP_TablesOutput
		exec sp_tables
	
	if exists (select name from tempdb..sysobjects where name like '#tmpSP_stored_proceduresOutput%')
	Begin
		drop table #tmpSP_stored_proceduresOutput
	End
	
	create table #tmpSP_stored_proceduresOutput
	(
	PROCEDURE_QUALIFIER sysname,
	PROCEDURE_OWNER sysname,
	PROCEDURE_NAME nvarchar(134),
	NUM_INPUT_PARAMS int,
	NUM_OUTPUT_PARAMS int, 
	NUM_RESULT_SETS int,
	REMARKS varchar(254), 
	PROCEDURE_TYPE smallint 
	)
	insert into #tmpSP_stored_proceduresOutput
		Exec sp_stored_procedures
	
	if exists (select name from tempdb..sysobjects where name like '#tmpObjectOwnerDetails%')
	Begin
		drop table #tmpObjectOwnerDetails
	End
	
	create table #tmpObjectOwnerDetails
	(
	AutoID int identity,
	ObjectName nvarchar(255),
	ObjectOwner sysname,
	ObjectType varchar(32)
	)

	insert into #tmpObjectOwnerDetails (ObjectName, ObjectOwner, ObjectType)
		select TABLE_NAME, TABLE_OWNER, TABLE_TYPE  from #tmpSP_TablesOutput where TABLE_OWNER = @ObjectOwner
	
	insert into #tmpObjectOwnerDetails (ObjectName, ObjectOwner, ObjectType)
		select  PROCEDURE_NAME, PROCEDURE_OWNER, 'Procedure' from #tmpSP_stored_proceduresOutput where PROCEDURE_OWNER = @ObjectOwner
	
	select * from #tmpObjectOwnerDetails
End

Rate

Share

Share

Rate