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
