/*
This post is a example of source control on sql databases, You can modify, extend or cut the functions of the next script, ask and discuss, The author is not responsible of content, only for educational purposes.
*/
/*Create a table where save every change on DB */
CREATE TABLE DEVSQLControl (
[ID] INT IDENTITY(1,1) ,
[Name] VARCHAR(256) ,
[Type] CHAR(2) ,
Create_date DATETIME ,
Modify_date DATETIME ,
DataCheckSum BIGINT ,
stamp TIMESTAMP ,
SnapDate DATETIME
)
/*
Insert in to DEVSQLControl table the new changes, including what I want to seek, you are free to change the list to your wish
*/
INSERT INTO
DEVSQLControl
(
[Name] ,
[type] ,
Create_date ,
Modify_date ,
DataCheckSum ,
SnapDate
)
SELECT
O.name ,
O.type ,
O.create_date ,
O.modify_date ,
BINARY_CHECKSUM(
O.name,
O.object_id,
O.principal_id,
O.schema_id,
O.parent_object_id,
O.type,
O.type_desc,
O.create_date,
O.modify_date,
O.is_ms_shipped,
O.is_published,
O.is_schema_published
) ,
Getdate()
FROM
SYS.OBJECTS AS O
INNER JOIN
DEVSQLControl AS DV
ON(-- 1 = 1
O.name COLLATE DATABASE_DEFAULT = DV.name COLLATE DATABASE_DEFAULT
AND O.type COLLATE DATABASE_DEFAULT = DV.type COLLATE DATABASE_DEFAULT
)
WHERE O.TYPE
IN ( 'U','P','fn', 'C ','PK','UQ','F ', 'D ' )
AND DataCheckSum <> BINARY_CHECKSUM(
O.name,
O.object_id,
O.principal_id,
O.schema_id,
O.parent_object_id,
O.type,
O.type_desc,
O.create_date,
O.modify_date,
O.is_ms_shipped,
O.is_published,
O.is_schema_published
)
GROUP BY
O.name ,
O.type ,
O.create_date ,
O.modify_date ,
BINARY_CHECKSUM(
O.name,
O.object_id,
O.principal_id,
O.schema_id,
O.parent_object_id,
O.type,
O.type_desc,
O.create_date,
O.modify_date,
O.is_ms_shipped,
O.is_published,
O.is_schema_published
)
