Validate Deployed Databases by Version

, 2010-01-14 (first published: )

How can you guarantee your customer database is exactly as you installed it last quarter? How can you be sure your upgrade completed perfectly? How can you immediately identify the object changes a customer has made to your database since the last release?

The attached Validator script may be created and executed as-is in AdventureWorks or any database for an immediate display of the objects and attributes it tracks. Right before deploying a database to a customer, spend ten minutes creating a custom version of Validator by embedding the results of eleven "selects" within it as per the instructions. Deploy the version specific ValidatorVnPn stored procedure with the database for the customer to use any time he has doubts.

 

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

Rate

Share

Share

Rate

Related content

The Basics of Good T-SQL Coding Style – Part 2: Defining Database Objects

Technical debt is a real problem in database development, where corners have been cut in the rush to keep to dates. The result may work but the problems are in the details: such things as inconsistent naming of objects, or of defining columns; sloppy use of data types, archaic syntax or obsolete system functions. With databases, technical debt is even harder to pay back. Robert Sheldon explains how and why you can get it right first time instead.

2017-07-25

5,860 reads