Validate Deployed Databases by Version

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].ValidatorV2P3') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].ValidatorV2P3
GO

Create Procedure dbo.ValidatorV2P3
AS

/* 
ValidatorV2P3 - written by Bill Talada

This procedure will compare actual object definitions with ones you will embed in here.
Do not use this if you allow sqlserver to generate random names for your defaults, primary constraints, or unique constraints.

There are 11 "selects" commented out below - one for each of the object types compared.
Run each commented "select" and store the results right below it.

Create this right before releasing to your customers.
Customers can then execute this at any time to see if their actual definitions match your expected definitions.
I run a current version of the validator before upgrading client databases to make sure they have not changed things.

defaults,
foreign keys,
functions,
indexes,
stored procedures,
parameters,
synonyms,
tables,
columns,
triggers,
views
*/
set nocount on

declare @d table(
SchemaName varchar(200),
TableName varchar(200),
ColumnName varchar(200),
DefaultName varchar(100),
Definition varchar(100)
)

declare @d2 table(
SchemaName varchar(200),
TableName varchar(200),
ColumnName varchar(200),
DefaultName varchar(100),
Definition varchar(100)
)

/* version specific

set nocount on

select
'insert into @d values('''+
schema_name(dc.schema_id)+''','''+
t.name+''','''+
col_name(dc.parent_object_id, dc.parent_column_id)+''','''+
dc.name +''','''+
dc.definition+''')'
from
sys.default_constraints dc
join
sys.tables t on dc.parent_object_id=t.object_id and t.name <> 'dtproperties'
order by 
schema_name(dc.schema_id),
t.name,
col_name(dc.parent_object_id, dc.parent_column_id),
dc.name

*/--defaults-------------






--defaults-------------

insert into @d2
select
schema_name(dc.schema_id),
t.name,
col_name(dc.parent_object_id, dc.parent_column_id) as ColumnName,
dc.name,
dc.definition
from
sys.default_constraints dc
join
sys.tables t on dc.parent_object_id=t.object_id and t.name <> 'dtproperties'


select
'Expected' as 'Expected',
d.*,
'Actual' as 'Actual',
d2.*
from
@d d
full outer join 
@d2 d2 on d.SchemaName = d2.SchemaName and d.TableName = d2.TableName and d.ColumnName = d2.ColumnName
where
d.DefaultName is null
or 
d2.DefaultName is null
or
d.DefaultName <> d2.DefaultName
or
d.definition <> d2.definition
order by
coalesce(d.DefaultName, d2.DefaultName)


-- ForeignKeyValidatorV2P3 --------------------------------

set nocount on

declare @fk table(
SchemaName varchar(100),
FkTable varchar(100),
RefTable varchar(100),
FkName varchar(200),
DeleteAction varchar(20),
UpdateAction varchar(20),
constraint_column_id int,
FkCol varchar(100),
RefCol varchar(100),
primary key (SchemaName, FkName, constraint_column_id)
)

declare @fk2 table(
SchemaName varchar(100),
FkTable varchar(100),
RefTable varchar(100),
FkName varchar(200),
DeleteAction varchar(20),
UpdateAction varchar(20),
constraint_column_id int,
FkCol varchar(100),
RefCol varchar(100),
primary key (SchemaName, FkName, constraint_column_id)
)

/* version specific

select
'insert into @fk values('''+
schema_name(fk.schema_id)+''','''+
tp.name+''','''+
tr.name+''','''+
fk.name+''','''+
case delete_referential_action_desc when 'NO_ACTION' then 'NO_ACTION' when 'CASCADE' then 'CASCADE' when 'SET_NULL' then 'SET_NULL' end   +''','''+
case update_referential_action_desc when 'NO_ACTION' then 'NO_ACTION' when 'CASCADE' then 'CASCADE' when 'SET_NULL' then 'SET_NULL' end +''','+
cast(fkc.constraint_column_id as varchar(10))+','''+
col_name(fkc.parent_object_id,fkc.parent_column_id)+''','''+
col_name(fkc.referenced_object_id,fkc.referenced_column_id)+''')'
from
sys.foreign_keys fk
join
sys.foreign_key_columns fkc on fk.object_id = fkc.constraint_object_id
join
sys.tables tp on fk.parent_object_id=tp.object_id
join
sys.tables tr on fk.referenced_object_id=tr.object_id
order by
schema_name(fk.schema_id),
tp.name,
tr.name,
fk.name

*/--foreignkeys-------------




--foreignkeys-------------

insert into @fk2
select
schema_name(fk.schema_id),
tp.name as 'FkTable',
tr.name as 'RefTable',
fk.name as 'FkName',
delete_referential_action_desc as 'DeleteAction',
update_referential_action_desc as 'UpdateAction',
fkc.constraint_column_id,
col_name(fkc.parent_object_id,fkc.parent_column_id) as FkCol,
col_name(fkc.referenced_object_id,fkc.referenced_column_id)as RefCol
from
sys.foreign_keys fk
join
sys.foreign_key_columns fkc on fk.object_id = fkc.constraint_object_id
join
sys.tables tp on fk.parent_object_id=tp.object_id
join
sys.tables tr on fk.referenced_object_id=tr.object_id



select
'Expected' as 'Expected',
f.*,
'Actual' as 'Actual',
f2.*
from
@fk f
full outer join
@fk2 f2 on f.SchemaName = f2.SchemaName and f.FkName = f2.FkName and f.constraint_column_id = f2.constraint_column_id
where
f.FkName is null
or
f2.FkName is null
or
f.FkTable <> f2.FkTable
or
f.RefTable <> f2.RefTable
or
f.DeleteAction <> f2.DeleteAction
or
f.UpdateAction <> f2.UpdateAction
or
f.FkCol <> f2.FkCol
or
f.RefCol <> f2.RefCol
order by
coalesce(f.SchemaName,f2.SchemaName),
coalesce(f.FkTable,f2.FkTable),
coalesce(f.RefTable, f2.RefTable),
coalesce(f.FkCol, f2.FkCol)





-- FunctionValidatorV2P3 ---------------------------------------------

set nocount on

declare @f table(
SchemaName varchar(100),
FuncName varchar(100),
parameter_id int,
ParmName varchar(100),
type_name varchar(20),
max_length int,
precision int,
scale int,
is_output bit,
primary key (SchemaName, FuncName, parameter_id)
)

declare @f2 table(
SchemaName varchar(100),
FuncName varchar(100),
parameter_id int,
ParmName varchar(100),
type_name varchar(20),
max_length int,
precision int,
scale int,
is_output bit,
primary key (SchemaName, FuncName, parameter_id)
)

/* version specific 

select
'insert into @f values('''+
schema_name(s.schema_id)+''','''+
s.name+''','+
cast(p.parameter_id as varchar(10))+','''+
p.name+''','''+
type_name(p.system_type_id)+''','+
cast(p.max_length as varchar(10))+','+
cast(p.precision as varchar(10))+','+
cast(p.scale as varchar(10))+','+
cast(p.is_output as varchar(1))+')'
from
sys.objects s
join
sys.parameters p on s.object_id = p.object_id
where
s.type = 'FN'
and
s.name not in ('fn_diagramobjects')
order by
s.name,
p.parameter_id

*/--functions-------------







--functions-------------


insert into @f2
select
schema_name(s.schema_id),
s.name,
p.parameter_id,
p.name,
type_name(p.system_type_id),
p.max_length,
p.precision,
p.scale,
p.is_output
from
sys.objects s
join
sys.parameters p on s.object_id = p.object_id
where
s.type = 'FN'
and
s.name not in ('fn_diagramobjects')
order by
s.name,
p.parameter_id


select
'Expected' as 'Expected',
f.*,
'Actual' as 'Actual',
f2.*
from
@f f
full outer join
@f2 f2 on f.FuncName = f2.FuncName and f.parameter_id = f2.parameter_id
where
f.FuncName is null
or 
f2.FuncName is null
or
f.ParmName <> f2.ParmName
or
f.type_name <> f2.type_name
or
f.max_length <> f2.max_length
or
f.precision <> f2.precision
or
f.scale <> f2.scale
or
f.is_output <> f2.is_output



-- index validator ---------------------------

set nocount on

declare @i table
( 
SchemaName varchar(200),
TableName varchar(200),
IndexName varchar(100),
type_desc varchar(20),
is_unique bit,
is_primary_key bit,
is_unique_constraint bit,
index_column_id int,
ColumnName varchar(200),
is_descending_key bit,
primary key (SchemaName, TableName, IndexName, index_column_id)
)

declare @j table
( 
SchemaName varchar(200),
TableName varchar(200),
IndexName varchar(100),
type_desc varchar(20),
is_unique bit,
is_primary_key bit,
is_unique_constraint bit,
index_column_id int,
ColumnName varchar(200),
is_descending_key bit,
primary key (SchemaName, TableName, IndexName, index_column_id)
)

/* replace inserts for each version

select
'insert into @i values('''+
schema_name(t.schema_id)+''','''+
t.name+''','''+
i.name+''','''+
case i.type_desc when 'HEAP' then 'HEAP' when 'CLUSTERED' then 'CLUSTERED' when 'NONCLUSTERED' then 'NONCLUSTERED' when 'XML' then 'XML' end+''','+
cast(i.is_unique as varchar(1))+','+
cast(i.is_primary_key as varchar(1))+','+
cast(i.is_unique_constraint as varchar(1))+','+
cast(ic.index_column_id as varchar(10))+','''+
c.name+''','+
cast(ic.is_descending_key as varchar(1))+')'
from 
sys.tables t
join
sys.indexes i on t.object_id=i.object_id
join
sys.index_columns ic on i.object_id=ic.object_id and i.index_id=ic.index_id
join
sys.columns c on ic.object_id=c.object_id and ic.column_id=c.column_id
where
t.name not in ('dtproperties','sysdiagrams')
order by
schema_name(t.schema_id),
t.name,
i.name,
ic.index_column_id


*/--indexes-------------








--indexes-------------

insert into @j
select
schema_name(t.schema_id),
t.name,
i.name,
i.type_desc,
i.is_unique,
i.is_primary_key,
i.is_unique_constraint,
ic.index_column_id,
c.name,
ic.is_descending_key
from 
sys.tables t
join
sys.indexes i on t.object_id=i.object_id
join
sys.index_columns ic on i.object_id=ic.object_id and i.index_id=ic.index_id
join
sys.columns c on ic.object_id=c.object_id and ic.column_id=c.column_id
where
t.name not in ('dtproperties','sysdiagrams')


select
'Expected' as 'Expected',
i.*,
'Actual' as 'Actual',
j.*
from
@i i
full outer join
@j j on i.SchemaName = j.SchemaName and i.IndexName = j.IndexName and i.index_column_id = j.index_column_id
where
i.type_desc is null 
or
j.type_desc is null
or
i.type_desc <> j.type_desc
or
i.ColumnName <> j.ColumnName
or
i.is_unique <> j.is_unique
or
i.is_primary_key <> j.is_primary_key
or
i.is_unique_constraint <> j.is_unique_constraint
or
i.is_descending_key <> j.is_descending_key



-- StoredProcedureValidatorV2P3 -------------------------------------------

set nocount on

declare @sp table(
SchemaName varchar(200),
ProcName varchar(200),
primary key (SchemaName, ProcName)
)

declare @sp2 table(
SchemaName varchar(200),
ProcName varchar(200),
primary key (SchemaName, ProcName)
)



/* version specific

select
'insert into @sp values('''+
schema_name(s.schema_id)+''','''+
s.name+''')'
from
sys.procedures s
where
s.name not in ('dt_addtosourcecontrol', 'dt_addtosourcecontrol_u', 'dt_adduserobject', 'dt_adduserobject_vcs', 'dt_checkinobject', 'dt_checkinobject_u', 'dt_checkoutobject', 'dt_checkoutobject_u', 'dt_displayoaerror', 'dt_displayoaerror_u', 'dt_droppropertiesbyid', 'dt_dropuserobjectbyid', 'dt_generateansiname', 'dt_getobjwithprop', 'dt_getobjwithprop_u', 'dt_getpropertiesbyid', 'dt_getpropertiesbyid_u', 'dt_getpropertiesbyid_vcs', 'dt_getpropertiesbyid_vcs_u', 'dt_isundersourcecontrol', 'dt_isundersourcecontrol_u', 'dt_removefromsourcecontrol', 'dt_setpropertybyid', 'dt_setpropertybyid_u', 'dt_validateloginparams', 'dt_validateloginparams_u', 'dt_vcsenabled', 'dt_verstamp006', 'dt_verstamp007', 'dt_whocheckedout', 'dt_whocheckedout_u', 'sp_alterdiagram', 'sp_creatediagram', 'sp_dropdiagram', 'sp_helpdiagramdefinition', 'sp_helpdiagrams', 'sp_renamediagram', 'sp_upgraddiagrams')
order by
s.name

*/--storedprocedures-------------








--storedprocedures-------------


insert into @sp2
select
schema_name(s.schema_id),
s.name as 'ProcName'
from
sys.procedures s
where
s.name not in ('dt_addtosourcecontrol', 'dt_addtosourcecontrol_u', 'dt_adduserobject', 'dt_adduserobject_vcs', 'dt_checkinobject', 'dt_checkinobject_u', 'dt_checkoutobject', 'dt_checkoutobject_u', 'dt_displayoaerror', 'dt_displayoaerror_u', 'dt_droppropertiesbyid', 'dt_dropuserobjectbyid', 'dt_generateansiname', 'dt_getobjwithprop', 'dt_getobjwithprop_u', 'dt_getpropertiesbyid', 'dt_getpropertiesbyid_u', 'dt_getpropertiesbyid_vcs', 'dt_getpropertiesbyid_vcs_u', 'dt_isundersourcecontrol', 'dt_isundersourcecontrol_u', 'dt_removefromsourcecontrol', 'dt_setpropertybyid', 'dt_setpropertybyid_u', 'dt_validateloginparams', 'dt_validateloginparams_u', 'dt_vcsenabled', 'dt_verstamp006', 'dt_verstamp007', 'dt_whocheckedout', 'dt_whocheckedout_u', 'sp_alterdiagram', 'sp_creatediagram', 'sp_dropdiagram', 'sp_helpdiagramdefinition', 'sp_helpdiagrams', 'sp_renamediagram', 'sp_upgraddiagrams')




select
'Expected' as 'Expected',
p.*,
'Actual' as 'Actual',
p2.*
from
@sp p
full outer join
@sp2 p2 on p.SchemaName = p2.SchemaName and p.ProcName = p2.ProcName
where
p.ProcName is null
or
p2.ProcName is null




-- ProcedureValidatorV2P3 -------------------------------------------

set nocount on

declare @p table(
SchemaName varchar(200),
ProcName varchar(200),
parameter_id int,
ParmName varchar(200),
DataType varchar(50),
max_length int,
precision int,
scale int,
is_output bit,
primary key (SchemaName, ProcName, parameter_id)
)

declare @p2 table(
SchemaName varchar(200),
ProcName varchar(200),
parameter_id int,
ParmName varchar(200),
DataType varchar(50),
max_length int,
precision int,
scale int,
is_output bit,
primary key (SchemaName, ProcName, parameter_id)
)


/* version specific

select
'insert into @p values('''+
schema_name(s.schema_id)+''','''+
s.name+''','+
cast(p.parameter_id as varchar(10))+','''+
p.name+''','''+
type_name(p.system_type_id)+''','+
cast(p.max_length as varchar(10))+','+
cast(p.precision as varchar(10))+','+
cast(p.scale as varchar(10))+','+
cast(p.is_output as varchar(1))+')'
from
sys.procedures s
join
sys.parameters p on s.object_id=p.object_id
where
s.name not in ('dt_addtosourcecontrol', 'dt_addtosourcecontrol_u', 'dt_adduserobject', 'dt_adduserobject_vcs', 'dt_checkinobject', 'dt_checkinobject_u', 'dt_checkoutobject', 'dt_checkoutobject_u', 'dt_displayoaerror', 'dt_displayoaerror_u', 'dt_droppropertiesbyid', 'dt_dropuserobjectbyid', 'dt_generateansiname', 'dt_getobjwithprop', 'dt_getobjwithprop_u', 'dt_getpropertiesbyid', 'dt_getpropertiesbyid_u', 'dt_getpropertiesbyid_vcs', 'dt_getpropertiesbyid_vcs_u', 'dt_isundersourcecontrol', 'dt_isundersourcecontrol_u', 'dt_removefromsourcecontrol', 'dt_setpropertybyid', 'dt_setpropertybyid_u', 'dt_validateloginparams', 'dt_validateloginparams_u', 'dt_vcsenabled', 'dt_verstamp006', 'dt_verstamp007', 'dt_whocheckedout', 'dt_whocheckedout_u', 'sp_alterdiagram', 'sp_creatediagram', 'sp_dropdiagram', 'sp_helpdiagramdefinition', 'sp_helpdiagrams', 'sp_renamediagram', 'sp_upgraddiagrams')
order by
schema_name(s.schema_id),
s.name,
p.parameter_id

*/--parameters-------------








--parameters-------------

insert into @p2
select
schema_name(s.schema_id),
s.name as 'ProcName',
p.parameter_id,
p.name as 'ParmName',
type_name(p.system_type_id) as DataType,
p.max_length,
p.precision,
p.scale,
p.is_output
from
sys.procedures s
join
sys.parameters p on s.object_id=p.object_id
where
s.name not in ('dt_addtosourcecontrol', 'dt_addtosourcecontrol_u', 'dt_adduserobject', 'dt_adduserobject_vcs', 'dt_checkinobject', 'dt_checkinobject_u', 'dt_checkoutobject', 'dt_checkoutobject_u', 'dt_displayoaerror', 'dt_displayoaerror_u', 'dt_droppropertiesbyid', 'dt_dropuserobjectbyid', 'dt_generateansiname', 'dt_getobjwithprop', 'dt_getobjwithprop_u', 'dt_getpropertiesbyid', 'dt_getpropertiesbyid_u', 'dt_getpropertiesbyid_vcs', 'dt_getpropertiesbyid_vcs_u', 'dt_isundersourcecontrol', 'dt_isundersourcecontrol_u', 'dt_removefromsourcecontrol', 'dt_setpropertybyid', 'dt_setpropertybyid_u', 'dt_validateloginparams', 'dt_validateloginparams_u', 'dt_vcsenabled', 'dt_verstamp006', 'dt_verstamp007', 'dt_whocheckedout', 'dt_whocheckedout_u', 'sp_alterdiagram', 'sp_creatediagram', 'sp_dropdiagram', 'sp_helpdiagramdefinition', 'sp_helpdiagrams', 'sp_renamediagram', 'sp_upgraddiagrams')
order by
s.name,
p.parameter_id


select
'Expected' as 'Expected',
p.SchemaName,
p.ProcName,
p.parameter_id,
p.ParmName,
p.DataType,
p.max_length,
p.precision,
p.scale,
p.is_output,
'Actual' as 'Actual',
p2.SchemaName,
p2.ProcName,
p2.parameter_id,
p2.ParmName,
p2.DataType,
p2.max_length,
p2.precision,
p2.scale,
p2.is_output

from
@p p
full outer join
@p2 p2 on p.SchemaName = p2.SchemaName and p.ProcName = p2.ProcName and p.parameter_id = p2.parameter_id
where
p.ParmName is null
or
p2.ParmName is null
or
p.ParmName <> p2.ParmName
or
p.DataType <> p2.DataType
or
p.max_length <> p2.max_length
or
p.precision <> p2.precision
or
p.scale <> p2.scale
or
p.is_output <> p2.is_output




-- SynonymValidatorV2P3 ----------------------------------

set nocount on

declare @s table (
SchemaName varchar(100),
name varchar(100),
primary key (SchemaName, Name)
)

declare @s2 table (
SchemaName varchar(100),
name varchar(100),
primary key (SchemaName, Name)
)

/* version specific

select
'insert into @s values('''+
schema_name(s.schema_id)+''','''+
name+''')'
from
sys.synonyms s
order by
name

*/--synonyms-------------







--synonyms-------------

insert into @s2
select
schema_name(s.schema_id),
name
from
sys.synonyms s


select
'Expected' as 'Expected',
s.SchemaName,
s.name,
'Actual' as 'Actual',
s2.SchemaName,
s2.name
from
@s s
full outer join
@s2 s2 on s.name = s2.name
where
s.name is null
or
s2.name is null
order by
coalesce(s.SchemaName, s2.SchemaName),
coalesce(s.name,s2.name)


-- TableValidatorForV2P2 --------------------------------------------

set nocount on

declare
@t table
(
SchemaName varchar(100),
TableName varchar(100),
lob_data_space_id bit,
uses_ansi_nulls bit,
large_value_types_out_of_row bit,
primary key (SchemaName, TableName)
)

declare
@t2 table
(
SchemaName varchar(100),
TableName varchar(100),
lob_data_space_id bit,
uses_ansi_nulls bit,
large_value_types_out_of_row bit,
primary key (SchemaName, TableName)
)

/* version specific

select
'insert into @t values('''+
schema_name(t.schema_id)+''','''+
name+''','+
cast(lob_data_space_id as varchar(1))+','+
cast(uses_ansi_nulls as varchar(1))+','+
cast(large_value_types_out_of_row as varchar(1))+')'
from
sys.tables t
where
name not in ('sysdiagrams','dtproperties')
order by
name

*/--tables-------------








--tables-------------

insert into @t2
select
schema_name(t.schema_id),
name,
lob_data_space_id,
uses_ansi_nulls,
large_value_types_out_of_row
from
sys.tables t
where
name not in ('sysdiagrams','dtproperties')


select
'Expected' as 'Expected',
t.*,
'Actual' as 'Actual',
t2.*
from
@t t
full outer join
@t2 t2 on t.SchemaName = t2.SchemaName and t.TableName = t2.TableName
where
t.TableName is null
or
t2.TableName is null
or
t.lob_data_space_id <> t2.lob_data_space_id
or
t.uses_ansi_nulls <> t2.uses_ansi_nulls
or
t.large_value_types_out_of_row <> t2.large_value_types_out_of_row
order by
coalesce(t.SchemaName,t2.SchemaName),
coalesce(t.TableName,t2.TableName)

-- TableColumnValidatorForV2P2 -----------------------------------------------

set nocount on

declare @tc table
(
SchemaName varchar(200),
TableName varchar(200),
ColumnName varchar(200),
DataType varchar(50),
max_length int,
precision int,
scale int,
is_nullable bit,
is_identity bit,
is_ansi_padded bit,
primary key (SchemaName, TableName, ColumnName)
)

declare @tc2 table
(
SchemaName varchar(200),
TableName varchar(200),
ColumnName varchar(200),
DataType varchar(50),
max_length int,
precision int,
scale int,
is_nullable bit,
is_identity bit,
is_ansi_padded bit,
primary key (SchemaName, TableName, ColumnName)
)

/* version specific

select
'insert into @tc values ('''+
schema_name(t.schema_id)+''','''+
t.name+''','''+
c.name+''','''+
y.name+''','+
cast(c.max_length as varchar(10))+','+
cast(c.precision as varchar(10))+','+
cast(c.scale as varchar(10))+','+
cast(c.is_nullable as varchar(1))+','+
cast(c.is_identity as varchar(1))+','+
cast(c.is_ansi_padded as varchar(1))+')'
from
sys.tables t
join
sys.columns c on t.object_id=c.object_id
join
sys.types y on c.user_type_id = y.user_type_id
where
t.name not in ('sysdiagrams','dtproperties')
order by 
t.name,
c.name

*/--tablecolumns-------------








--tablecolumns-------------

insert into @tc2
select
schema_name(t.schema_id) as 'SchemaName',
t.name as 'TableName',
c.name as 'ColumnName',
y.name as 'DataType',
c.max_length,
c.precision,
c.scale,
c.is_nullable,
c.is_identity,
c.is_ansi_padded
from
sys.tables t
join
sys.columns c on t.object_id=c.object_id
join
sys.types y on c.user_type_id = y.user_type_id
where
t.name not in ('sysdiagrams','dtproperties')
order by 
t.name,
c.column_id



select
'Expected' as 'Expected',
c.*,
'Actual' as 'Actual',
c2.*
from
@tc c
full outer join
@tc2 c2 on c.SchemaName = c2.SchemaName and c.TableName = c2.TableName and c.ColumnName = c2.ColumnName

where
c.ColumnName is null
or
c2.ColumnName is null
or
c.DataType <> c2.DataType
or
c.max_length <> c2.max_length
or
c.precision <> c2.precision
or
c.scale <> c2.scale
or
c.is_nullable <> c2.is_nullable
or
c.is_identity <> c2.is_identity
or
c.is_ansi_padded <> c2.is_ansi_padded








-- TriggerValidatorV2P3 -------------------------------------------

set nocount on

declare @tr table(
SchemaName varchar(200),
TableName varchar(200),
TriggerName varchar(100),
IsInsteadOfTrigger bit,
IsInsert bit,
IsUpdate bit,
IsDelete bit,
primary key (SchemaName, TableName, TriggerName)
)

declare @tr2 table(
SchemaName varchar(200),
TableName varchar(200),
TriggerName varchar(100),
IsInsteadOfTrigger bit,
IsInsert bit,
IsUpdate bit,
IsDelete bit,
primary key (SchemaName, TableName, TriggerName)
)

/* version specific

select
'insert into @tr values('''+
schema_name(t.schema_id)+''','''+
t.name+''','''+
tr.name+''','+
cast(tr.is_instead_of_trigger as varchar(1))+','+
case tei.type_desc when 'Insert' then '1,' else '0,' end +
case teu.type_desc when 'Update' then '1,' else '0,' end +
case ted.type_desc when 'Delete' then '1)' else '0)' end
from
sys.tables t
join
sys.triggers tr on t.object_id=tr.parent_id
left join
sys.trigger_events tei on tr.object_id=tei.object_id and tei.type = 1
left join
sys.trigger_events teu on tr.object_id=teu.object_id and teu.type = 2
left join
sys.trigger_events ted on tr.object_id=ted.object_id and ted.type = 3
order by 
t.name,
tr.name

*/--triggers-------------








--triggers-------------

insert into @tr2
select
schema_name(t.schema_id),
t.name,
tr.name,
tr.is_instead_of_trigger,
case tei.type_desc when 'Insert' then 1 else 0 end,
case teu.type_desc when 'Update' then 1 else 0 end,
case ted.type_desc when 'Delete' then 1 else 0 end
from
sys.tables t
join
sys.triggers tr on t.object_id=tr.parent_id
left join
sys.trigger_events tei on tr.object_id=tei.object_id and tei.type = 1
left join
sys.trigger_events teu on tr.object_id=teu.object_id and teu.type = 2
left join
sys.trigger_events ted on tr.object_id=ted.object_id and ted.type = 3
order by 
t.name,
tr.name


select
'Expected' as 'Expected',
t.SchemaName,
t.TableName,
t.TriggerName,
t.IsInsteadOfTrigger,
t.IsInsert,
t.IsUpdate,
t.IsDelete,
'Actual' as 'Actual',
t2.SchemaName,
t2.TableName,
t2.TriggerName,
t2.IsInsteadOfTrigger,
t2.IsInsert,
t2.IsUpdate,
t2.IsDelete
from
@tr t
full outer join
@tr2 t2 on t.SchemaName = t2.SchemaName and t.TableName = t2.TableName and t.TriggerName = t2.TriggerName
where
t.TriggerName is null
or
t2.TriggerName is null
or
t.IsInsteadOfTrigger <> t2.IsInsteadOfTrigger
or
t.IsInsert <> t2.IsInsert
or
t.IsUpdate <> t2.IsUpdate
or
t.IsDelete <> t2.IsDelete




-- ViewColumnValidatorForV2P2 --------------------------------------------------

set nocount on

declare @vc table
(
SchemaName varchar(100),
ViewName varchar(100),
ColumnName varchar(200),
DataType varchar(50),
max_length int,
precision int,
scale int,
is_nullable bit,
is_identity bit,
is_ansi_padded bit,
primary key (SchemaName, ViewName, ColumnName)
)

declare @vc2 table
(
SchemaName varchar(100),
ViewName varchar(100),
ColumnName varchar(200),
DataType varchar(50),
max_length int,
precision int,
scale int,
is_nullable bit,
is_identity bit,
is_ansi_padded bit,
primary key (SchemaName, ViewName, ColumnName)
)

/* version specific

select
'insert into @vc values ('''+
schema_name(t.schema_id)+''','''+
t.name+''','''+
c.name+''','''+
y.name+''','+
cast(c.max_length as varchar(10))+','+
cast(c.precision as varchar(10))+','+
cast(c.scale as varchar(10))+','+
cast(c.is_nullable as varchar(1))+','+
cast(c.is_identity as varchar(1))+','+
cast(c.is_ansi_padded as varchar(1))+')'
from
sys.views t
join
sys.columns c on t.object_id=c.object_id
join
sys.types y on c.user_type_id = y.user_type_id
where
t.name not in ('sysdiagrams','dtproperties')
order by 
t.name,
c.name

*/--viewcolumns-------------








--viewcolumns-------------

insert into @vc2
select
schema_name(t.schema_id),
t.name as 'ViewName',
c.name as 'ColumnName',
y.name as 'DataType',
c.max_length,
c.precision,
c.scale,
c.is_nullable,
c.is_identity,
c.is_ansi_padded
from
sys.views t
join
sys.columns c on t.object_id=c.object_id
join
sys.types y on c.user_type_id = y.user_type_id
where
t.name not in ('sysdiagrams','dtproperties')
order by 
t.name,
c.column_id


select
'Expected' as 'Expected',
c.*,
'Actual' as 'Actual',
c2.*
from
@vc c
full outer join
@vc2 c2 on c.ViewName = c2.ViewName and c.ColumnName = c2.ColumnName
where
c.ViewName is null
or
c2.ViewName is null
or
c.DataType <> c2.DataType
or
c.max_length <> c2.max_length
or
c.precision <> c2.precision
or
c.scale <> c2.scale
or
c.is_nullable <> c2.is_nullable
or
c.is_identity <> c2.is_identity
or
c.is_ansi_padded <> c2.is_ansi_padded


RETURN 0
GO