Categoría: Base de datos

En esta ocasión les comparto un script para verificar la existencia de objetos en SQL Server, es muy útil para validar procesos de despliegue entre ambientes.

Solo necesitan agregar en la primera seccion los objetos a validar.

-----(Script to verify deploy DB objects...)-----

declare @TblResultset table 
(schema_name sysname null, 
ItemName sysname null, 
ParentName sysname null,	--apply for indexes and columns
ItemType int not null,		--1 for tables, 2 for udtt, 3 for stored procedures, 4 for functions, 5 for indexes & 6 for columns.
create_date datetime, 
modify_date datetime, 
StatusMsg nvarchar(256))

set nocount on 

--/* update here with the objects to search.... !!!! --*/
insert @TblResultset (schema_name, ItemName,ItemType,ParentName)
--tables (schema, TableName, 1, null)
select 'dbo', 'MyTable1' ,1,null union all
select 'dbo', 'MyTable2',1,null union all
--user defined table types (schema, UdtName, 2, null)
select 'dbo', 'udt_1',2,null union all
select 'dbo', 'udt_2',2,null union all
--stored procedures (schema, SpName, 3,null)
select 'dbo', 'MyProcedure1' ,3,null union all 
select 'dbo', 'MyProcedure2' ,3,null union all 
--functions (schema, FunctionName, 4,null)
select 'dbo', 'fn_MyFuntion1' ,4,null union all
--indexes (schema, idxName, 5, ParentTableName)
select 'MySchema', 'MyIndexName' ,5,'MyParentTable' union all
--columns  (schema, ColumnName, 6, ParentTableName)
select 'MySchema','MyColumnName',6,'MyParentTable'

update RS
set RS.create_date = ST.create_date,
	RS.modify_date = ST.modify_date, 
	RS.StatusMsg = 
		case
			when ST.name is not null then 'Exists in database.'
			else 'Do not exists in database.'
		end
from @TblResultset RS 
	left outer join sys.tables ST 
		on RS.ItemName = ST.name
where RS.ItemType = 1 --tables

update RS
set RS.create_date = AO.create_date,
	RS.modify_date = null,
	RS.StatusMsg = 
	case
		when ST.name is not null then 'Exists in database.'
		else 'Do not exists in database.'
	end
from @TblResultset RS 
	left outer join  sys.table_types ST 
		on RS.ItemName = ST.name
	left outer join sys.all_objects AO 
		on ST.type_table_object_id = AO.object_id
where RS.ItemType = 2 --user defined table type


update RS
set RS.create_date = ST.created,
	RS.modify_date = ST.LAST_ALTERED,
	RS.StatusMsg = 
	case
		when ST.SPECIFIC_NAME is not null then 'Exists in database.'
		else 'Do not exists in database.'
	end
from @TblResultset RS 
	left outer join INFORMATION_SCHEMA.ROUTINES ST 
		on RS.ItemName = ST.SPECIFIC_NAME
where RS.ItemType in(3,4) --stored procedures & functions

update RS
set RS.create_date = AO.create_date,
	RS.modify_date = AO.modify_date,
	RS.StatusMsg = 
	case
		when ST.name is not null then 'Exists in database.'
		else 'Do not exists in database.'
	end
from @TblResultset RS 
	left outer join  sysindexes ST 
		on RS.ItemName = ST.name
	left outer join sys.all_objects AO 
		on ST.id = AO.object_id
where RS.ItemType = 5 --index

update RS
set RS.create_date =null,
	RS.modify_date = null,
	RS.StatusMsg = 
	case
		when ST.name is not null then 'Exists in database.'
		else 'Do not exists in database.'
	end
from @TblResultset RS 
	left outer join  syscolumns ST 
		on RS.ItemName = ST.name and object_name(ST.id) = RS.ParentName
where RS.ItemType = 6 --columns

-----(((Output resultset)))-----
select schema_name, ItemName as ObjectName, 
case
	when ItemType = 1 then 'Table'
	when ItemType = 2 then 'UDTT'
	when ItemType = 3 then 'Stored Procedure'
	when ItemType = 4 then 'Function'
	when ItemType = 5 then 'Index'
	when ItemType = 6 then 'Column'
	else 'UFO'
end as ObjectType,
isnull (cast(create_date as varchar),'NA') as CreateDate, 
isnull(cast(modify_date as varchar),'NA') as ModifyDate,
StatusMsg
from @TblResultset

set nocount off
go

La salida del script seria asi:

Espero les sea de utilidad…