CREATE PROCEDURE [dbo].[Itai_checkUnusedObjectsSP]
@IncludeTables int,
@IncludeViews int,
@IncludeFunctions int,
@IncludeSPs int,
@Schema nvarchar(100) = '',
@NotSchema nvarchar(100) = '',
@NamePrefix nvarchar(100) = ''
AS
declare @TempObjects table (schema_ nvarchar(50), table_ nvarchar(250))
declare @ObjectsNotToDelete table (schema_ nvarchar(50), table_ nvarchar(250))
declare @SQL as nvarchar(2000)
DECLARE @TempTypesStr nvarchar(100)
SET @TempTypesStr = ''
IF @IncludeTables+@IncludeViews+@IncludeFunctions+@IncludeSPs = 0
begin
select 'Select object type'
return
end
-- insert relevant objects into @TempObjects
SET @SQL = 'SELECT SCHEMA_NAME(schema_id), name
FROM sys.objects
WHERE '
IF @IncludeTables = 1 SET @TempTypesStr = @TempTypesStr + ',''U'''
IF @IncludeViews = 1 SET @TempTypesStr = @TempTypesStr + ',''V'''
IF @IncludeFunctions = 1 SET @TempTypesStr = @TempTypesStr + ',''AF'',''FN'',''FS'',''FT'',''IF'',''TF'''
IF @IncludeSPs = 1 SET @TempTypesStr = @TempTypesStr + ',''P'',''PC'',''RF'',''X'''
select @TempTypesStr = stuff(@TempTypesStr,1,1,'')
SET @SQL = @SQL +
'type in (' + @TempTypesStr + ') '
IF @Schema <> '' SET @SQL = @SQL + '
AND SCHEMA_NAME(schema_id) = ''' + @Schema + ''' '
IF @NotSchema <> '' SET @SQL = @SQL + '
AND SCHEMA_NAME(schema_id) <> ''' + @NotSchema + ''' '
IF @NamePrefix <> '' SET @SQL = @SQL + '
AND name like ''' + @NamePrefix + '%'' '
print @SQL
insert into @TempObjects(schema_, table_) EXEC(@SQL)
--check:
--select * from @TempObjects
IF @NotSchema = ''
BEGIN
insert into @ObjectsNotToDelete(schema_, table_)
-- return objects that have conections
SELECT distinct t.schema_, t.table_ --, s.SPECIFIC_NAME as object_name_ --s.*
FROM INFORMATION_SCHEMA.ROUTINES s
inner join @TempObjects t on s.ROUTINE_DEFINITION LIKE '%' + t.table_ + '%'
--and s.SPECIFIC_SCHEMA = t.schema_
where t.table_ <> s.SPECIFIC_NAME
union
select distinct t.schema_, t.table_ --, object_name(s.id) as object_name_ --, count(*) as count_
from syscomments s inner join sys.objects o on o.object_id = s.id
inner join @TempObjects t on s.text like '%' + t.table_ + '%' --and SCHEMA_NAME(o.schema_id) = t.schema_
where t.table_ <> object_name(s.id)
--order by table_
END
ELSE
BEGIN
insert into @ObjectsNotToDelete(schema_, table_)
-- return objects that have conections
SELECT distinct t.schema_, t.table_ --, s.SPECIFIC_NAME as object_name_ --s.*
FROM INFORMATION_SCHEMA.ROUTINES s
inner join @TempObjects t on s.ROUTINE_DEFINITION LIKE '%' + t.table_ + '%'
--and s.SPECIFIC_SCHEMA = t.schema_
and s.SPECIFIC_SCHEMA <> @NotSchema
where t.table_ <> s.SPECIFIC_NAME
union
select distinct t.schema_, t.table_ --, object_name(s.id) as object_name_ --, count(*) as count_
from syscomments s inner join sys.objects o on o.object_id = s.id
inner join @TempObjects t on s.text like '%' + t.table_ + '%' and SCHEMA_NAME(o.schema_id) <> @NotSchema
where t.table_ <> object_name(s.id)
--order by table_
END
if @IncludeTables = 1
begin
IF @NotSchema = ''
BEGIN
insert into @ObjectsNotToDelete(schema_, table_)
select SCHEMA_NAME(o.schema_id),
--f.constraint_object_id, object_name(f.constraint_object_id) as constraint_object, o.name,
--f.constraint_column_id, --object_name(f.constraint_column_id) as constraint_column,
--f.parent_object_id, object_name(f.parent_object_id) as parent_object,
--f.parent_column_id, object_name(f.parent_column_id) as parent_column,
--f.referenced_object_id,
object_name(f.referenced_object_id) as referenced_object --,
--f.referenced_column_id --, object_name(f.referenced_column_id) as referenced_column
from sys.foreign_key_columns f
inner join sys.objects o on o.object_id = f.referenced_object_id --and SCHEMA_NAME(o.schema_id) <> 'Lms'
where object_name(referenced_object_id) like @NamePrefix + '%'
END
ELSE
BEGIN
insert into @ObjectsNotToDelete(schema_, table_)
select SCHEMA_NAME(o.schema_id),
--f.constraint_object_id, object_name(f.constraint_object_id) as constraint_object, o.name,
--f.constraint_column_id, --object_name(f.constraint_column_id) as constraint_column,
--f.parent_object_id, object_name(f.parent_object_id) as parent_object,
--f.parent_column_id, object_name(f.parent_column_id) as parent_column,
--f.referenced_object_id,
object_name(f.referenced_object_id) as referenced_object --,
--f.referenced_column_id --, object_name(f.referenced_column_id) as referenced_column
from sys.foreign_key_columns f
inner join sys.objects o on o.object_id = f.referenced_object_id and SCHEMA_NAME(o.schema_id) <> @NotSchema
where object_name(referenced_object_id) like @NamePrefix + '%'
END
end
------------------------------------------------------------------------------------
-- For this section, you have to create temporary table in the database, that will contain the objects that you know that you can't delete (For example: object that is not called from the DB, but do called from the Application).
-- The SP do not display those objects in the results list.
-- See below the table script.
IF @IncludeTables = 1
insert into @ObjectsNotToDelete(schema_, table_)
select schema_, objName_ from CheckedObjectsNotToDelete where type_ = 'Table'
IF @IncludeViews = 1
insert into @ObjectsNotToDelete(schema_, table_)
select schema_, objName_ from CheckedObjectsNotToDelete where type_ = 'View'
IF @IncludeFunctions = 1
insert into @ObjectsNotToDelete(schema_, table_)
select schema_, objName_ from CheckedObjectsNotToDelete where type_ = 'Function'
IF @IncludeSPs = 1
insert into @ObjectsNotToDelete(schema_, table_)
select schema_, objName_ from CheckedObjectsNotToDelete where type_ = 'SP'
------------------------------------------------------------------------------------
--checks:
--select * from @TempObjects order by schema_, table_
--select distinct * from @ObjectsNotToDelete order by schema_, table_
select o.* from @TempObjects o
left join (select distinct * from @ObjectsNotToDelete) ontd on o.schema_ = ontd.schema_ and o.table_ = ontd.table_
where ontd.table_ is null
order by o.schema_, o.table_
GO
------------------------------------------------------------------------------------
--Script to create table CheckedObjectsNotToDelete:
CREATE TABLE [dbo].[CheckedObjectsNotToDelete](
[type_] [nvarchar](50) NULL,
[schema_] [nvarchar](50) NULL,
[objName_] [nvarchar](250) NULL
) ON [PRIMARY]
GO
-- type_:
-- Table
-- View
-- Function
-- SP (stored procedure)
No comments:
Post a Comment