Check for changes to a SQL Server database?
Solution 1
You might find the information in the transaction log...
Use
DBCC LOG(<database name>[,{0|1|2|3|4}])
0 - Basic Log Information (default)
1 - Lengthy Info
2 - Very Length Info
3 - Detailed
4 - Full
Example:
DBCC log (MY_DB, 4)
The result is somewhat cryptic and is better used with a third-party tool thought...
Credits: http://www.mssqlcity.com/Articles/KnowHow/ViewLog.htm
EDIT:
You might try
Select * from ::fn_dblog(null, null)
This page contains some usefull information about the results returned by the functions... http://www.novicksoftware.com/udfofweek/Vol1/T-SQL-UDF-Volume-1-Number-27-fn_dblog.htm
Keep in mind, that those procedures are not for the public and therefore not easy to understand. The other possibility is to add triggers on all tables. But that is on the otherhand a lot of work.
Solution 2
By "changes" I had a similar problem but not changes to the data, rather changes to the schema. I created the following function to generate a checksum of the whole database schema based on INFORMATION_SCHEMA. Expand to additional information schema tables if your system uses them, this is enough for my usage.
I've used this to create a daily alert to notify me of any changes to our production database, so it's become easy to police unplanned/unauthorised changes by other team members. Simply store the checksum, and then run the function and compare to the stored value. If different, then someone has changed something.
CREATE FUNCTION [dbo].GetDbSchemaChecksum
(
)
RETURNS int
AS
BEGIN
declare @retVal int
set @retVal =
(
SELECT
CHECKSUM_AGG(T.Chk) DbSchemaChecksum
FROM
(
select checksum(TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE) Chk from INFORMATION_SCHEMA.TABLES
union all
select checksum(TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, DATETIME_PRECISION) Chk from INFORMATION_SCHEMA.COLUMNS
union all
select checksum(CONSTRAINT_SCHEMA, CONSTRAINT_NAME, UNIQUE_CONSTRAINT_CATALOG, UNIQUE_CONSTRAINT_SCHEMA, UNIQUE_CONSTRAINT_NAME, MATCH_OPTION, UPDATE_RULE, DELETE_RULE) Chk from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
union all
select checksum(CONSTRAINT_NAME, TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_TYPE, IS_DEFERRABLE, INITIALLY_DEFERRED) Chk from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
union all
select checksum(TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CONSTRAINT_SCHEMA, CONSTRAINT_NAME) Chk from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
union all
select checksum(CONSTRAINT_SCHEMA, CONSTRAINT_NAME, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION) Chk from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
union all
select checksum(CONSTRAINT_SCHEMA, CONSTRAINT_NAME, CHECK_CLAUSE) Chk from INFORMATION_SCHEMA.CHECK_CONSTRAINTS
union all
select checksum(TABLE_SCHEMA, TABLE_NAME, VIEW_DEFINITION, CHECK_OPTION, IS_UPDATABLE) Chk from INFORMATION_SCHEMA.VIEWS
union all
select checksum(VIEW_SCHEMA, VIEW_NAME, TABLE_SCHEMA, TABLE_NAME) Chk from INFORMATION_SCHEMA.VIEW_TABLE_USAGE
union all
select checksum(SPECIFIC_SCHEMA, SPECIFIC_NAME, ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_DEFINITION) Chk from INFORMATION_SCHEMA.ROUTINES
union all
select checksum(SPECIFIC_SCHEMA, SPECIFIC_NAME, ORDINAL_POSITION, PARAMETER_MODE, IS_RESULT, AS_LOCATOR, PARAMETER_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH) Chk from INFORMATION_SCHEMA.PARAMETERS
) AS T
)
return @retVal
END
GO
Solution 3
One approach to record/log schema changes is to use DDL triggers as of SQL Server 2005.
You can set them up on a database to fire on specific types of schema changes (e.g. CREATE_PROCEDURE, ALTER_PROCEDURE) and then maintain whatever log you want to.
Pavlo Neiman
Interested in BigData, ML, AI and all other interesting stuff
Updated on June 04, 2022Comments
-
Pavlo Neiman almost 2 years
is there a way to get database checksum using system SQL Server functions?
Or other way to quickly figure out if there are any changes in database?
I do not want to use any specific database analysis software for this.
I look for any changes in database (schema/object changes, data changes).
I use SQL Server 2008.