select 'alter table ' + s.name + '.' + o.name + ' add rowguid uniqueidentifier not null default newsequentialid() rowguidcol;'
from sys.objects o, sys.schemas s
where type = 'u'
and o.schema_id = s.schema_id
and o.name not in ('dtproperties', 'sysdiagrams')
and object_id not in (select c.object_id from sys.columns c
where is_rowguidcol = 1)
Creates a unique index on the rowguid column where there is not already an index:
select 'create unique nonclustered index IX_' + o.name + '_Merge_rowguid_ix on ' + s.name + '.' +
o.name + '(' + c.name + ');'
from sys.objects o, sys.schemas s, sys.columns c
where type = 'u'
and o.schema_id = s.schema_id
and o.name not in ('dtproperties', 'sysdiagrams')
and c.object_id = o.object_id
and is_rowguidcol = 1
and not exists (select * from sys.indexes i,
sys.index_columns ic,
sys.columns c2
where i.object_id = o.object_id
and i.index_id = ic.index_id
and i.object_id = ic.object_id
and c2.object_id = i.object_id
and c2.column_id = ic.column_id
and c2.column_id = c.column_id)
This script creates some more script which can be cut and paste to execute on in Management Studio/Query Analyser/ISQLw.
And here are some scripts to drop the created constraints and columns:
select 'alter table ' + s.name + '.' + o.name + ' drop constraint ' + cn.name + ';'
from sys.objects o, sys.schemas s, sys.columns c, sys.default_constraints cn
where o.type = 'u'
and o.schema_id = s.schema_id
and o.name not in ('dtproperties', 'sysdiagrams')
and o.object_id = c.object_id
and c.name = 'rowguid'
and cn.parent_object_id = o.object_id
and c.is_rowguidcol = 1
and cn.parent_column_id = c.column_id
select 'alter table ' + s.name + '.' + o.name + ' drop column rowguid;'
from sys.objects o, sys.schemas s
where type = 'u'
and o.schema_id = s.schema_id
and o.name not in ('dtproperties', 'sysdiagrams')
and object_id in (select c.object_id from sys.columns c
where c.name = 'rowguid'
and is_rowguidcol = 1)
A script to find overlapping indexes
Sometimes getting a snapshot to apply after invalidating the current snapshot and making a lot of schema changes can be difficult. Here are a few things that can help:
To find the order that the scripts are going to be applied
sp_MSenumschemachange 'B496EDF3-E9AD-4CD8-BD09-63FA84B75836',2311,9000000,0,0,1
The guid is the schemaguid(check) and 2311 is the schemaversion which are available in sysmergesubscriptions
Its possible to modify the order by changing the schemaversion, but this is not supported and may completely hose your database and cause you to be fired. If you do modify the order in this way, you may end up with this error message:
"The Subscriber's schema version information is inconsistent with the Publisher's schema version information"
This happens due to the last the schemaversion being modified and the last applied schema at the subscriber being different to the publisher. This can be manuaaly fudged by changing sysmergesubscriptions at the subscriber. Also not supported and liable to hose. For example:
update sysmergesubscriptions
set schemaversion = 2437, schemaguid ='9893932B-079A-4EF2-91AC-3BE3278F2DE9'
where subscriber_server = 'machine1'
Code to examine the state of a managed identity column:
declare
@table varchar(1000)
set @table = 'OSS_GD_CALCULATION_TIMESHEET'
dbcc checkident (@table, noreseed)
select definition from sys.check_constraints cc,
sys.objects o
where o.name = @table
and o.object_id = cc.parent_object_id
select s.subscriber_server, ir.range_begin, range_end, next_range_begin, next_range_end from dbo.MSmerge_identity_range ir,
sysmergearticles a, sysmergesubscriptions s
where a.artid = ir.artid
and a.name = @table
and ir.subid = s.subid
A script to show foreign keys to or from a particular table.
And a modification to drop them:
SELECT 'alter table ' + FK.TABLE_NAME + ' drop constraint ' + C.CONSTRAINT_NAME + ';'
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
WHERE PK.TABLE_NAME='primarytablename'
SELECT 'ALTER TABLE [dbo].[' + RTRIM(TABLE_NAME) +'] DROP CONSTRAINT ' + RTRIM(CONSTRAINT_NAME) + ';'
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME
Script for top 5 slowest queries
SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY total_worker_time/execution_count DESC;
SELECT
object_id AS objectid,
sysobjects.NAME,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag_percentage,
*
FROM sys.dm_db_index_physical_stats (10, NULL, NULL , NULL, 'DETAILED') -- 10 is the db id
JOIN sysobjects ON sysobjects.Id = object_id
WHERE index_id > 0 AND avg_fragmentation_in_percent > 20
ORDER BY sysobjects.name
--ORDER BY frag_percentage DESC
--- #1 first run the selects and review the extent of the index fragmentation:
SELECT object_name(object_id), * FROM sys.dm_db_index_physical_stats (db_id(),object_id('MSmerge_contents'),NULL,NULL,'LIMITED')
SELECT object_name(object_id), * FROM sys.dm_db_index_physical_stats (db_id(),object_id('MSmerge_genhistory'),NULL,NULL,'LIMITED')
SELECT object_name(object_id), * FROM sys.dm_db_index_physical_stats (db_id(),object_id('MSmerge_tombstone'),NULL,NULL,'LIMITED')
SELECT object_name(object_id), * FROM sys.dm_db_index_physical_stats (db_id(),object_id('sysmergeschemaarticles'),NULL,NULL,'LIMITED')
SELECT object_name(object_id), * FROM sys.dm_db_index_physical_stats (db_id(),object_id('MSmerge_identity_range'),NULL,NULL,'LIMITED')
--- #2 if the fragmentation is > 10% then run the following rebuild index commands.
Alter index xyz on MSmerge_contents rebuild with (FILLFACTOR=90, PAD_INDEX = ON, SORT_IN_TEMPDB =ON,online=on,MAXDOP=1)