Merge replication identity range crisis …

--Find the merge replication identity ranges/seed/crisis thresholds ...

SET NOCOUNT ON
declare @filterPct smallint
select @filterPct = 0
create
table #ranges
(objectId int, tableName sysname, columnName sysname, rangeMin int, rangeMax int, currentMax int NULL)
INSERT
INTO #ranges
-- find replication tables and columns which are identity columns
select
objectId , tableName , columnName
, convert(int, substring(minStr, 0, patindex('%and%', minStr))) minValue ,
convert(int, substring(maxStr, 0, len(maxstr))) maxValue ,
NULL from
(
select
o.id objectId ,
object_name(o.id) tableName
, o.name columnName
, o.colId
, com.text
, substring(com.text, patindex('%>%', com.text) + 1, 20) minStr
, substring(com.text, patindex('%<%', com.text) + 1, 20) maxStr
from
msrepl_identity_range r inner
join
syscolumns o on r.objid = o.Id inner
join
sysconstraints con on o.id = con.id and o.colid = con.colId inner
join
syscomments com on con.constId = com.id
where o.status = 0x80 ) t1
order by 2,3
DECLARE @objectId int, @sqlStr nvarchar(4000)
SELECT
TOP 1 @objectId = objectId from #ranges order
by objectId
WHILE (@@rowcount
> 0)
BEGIN
SELECT @sqlStr =
'update #ranges set currentMax = (select max('
+ columnName +
') from '
+ tableName +
'(nolock) where '
+ columnName +
' BETWEEN '
+
convert(varchar, rangeMin)
+
' AND '
+
convert(varchar, rangeMax)
+
') WHERE '
+
' objectId = '
+
convert(varchar, @objectId)
from #ranges where objectId = @objectId
EXEC
sp_executesql
@sqlStr
SELECT
TOP 1 @objectId = objectId from #ranges where objectId > @objectId order
by objectId
END
select
@@servername serverName
, tableName
, (rangeMax - rangeMin) rangeSize
, (rangeMax - currentMax) rangeAvailable
, (currentMax - rangeMin) rangeUsed
, (convert(decimal(10,2), (currentMax - rangeMin)) / (rangeMax - rangeMin)) * 100 rangeUtilisation
from
#ranges
where
(convert(decimal(10,2), (currentMax - rangeMin)) / (rangeMax - rangeMin)) * 100 > @filterPct
drop table #ranges

Script all user database tables …

--Script all user database tables.

set nocount on
use
master
go
declare @DBNAME varchar(128)
declare @TabName varchar(128)
declare @TabID int
declare @DDLDEF VARCHAR(8000)
declare @ColName varchar(1000)
declare @Count int
declare @FileGroup varchar(128)
declare DBCur cursor
for
select name from sysdatabases where name not in ('tempdb', 'master', 'pubs', 'Northwind', 'msdb', 'model')
order by name
OPEN DBCur
fetch next from DBCur into @DBNAME
while @@FETCH_STATUS =0

begin
exec('use ' + @DBNAME)
PRINT '/*Scripting for ' + @DBNAME + ' done on ' + CAST(GetDate() as varchar(50)) + '*/'
declare TabNameCur cursor
for
select '[' + su.name + '].[' + so.name + ']', so.id , sfg.groupname
from sysusers su inner join sysobjects so
on su.uid = so.uid
inner join sysindexes si on si.id = so.id
inner join sysfilegroups sfg on sfg.groupid = si.groupid
where so.type = 'U'
order by su.name, so.name
open TabNameCur
fetch next from TabNameCur into @TabName, @TabID, @FileGroup
while @@FETCH_STATUS = 0
begin
set @DDLDef = Char(13) + 'CREATE TABLE ' + @TabName + '(' + Char(13)
/* Must incorporate all datatypes */
declare ColNameCur cursor
for
select '[' + sc.name + ']' + ' ' + st.name +
CASE when st.name = 'char' then '(' + CAST(sc.LENGTH as varchar(50)) + ')'
when st.name = 'nchar' then '(' + CAST(sc.LENGTH as varchar(50)) + ')'
when st.name = 'nvarchar' then '(' + CAST(sc.LENGTH as varchar(50)) + ')'
when st.name = 'varbinary' then '(' + CAST(sc.LENGTH as varchar(50)) + ')'
when st.name = 'binary' then '(' + CAST(sc.LENGTH as varchar(50)) + ')'
when st.name = 'varchar' then '(' + CAST(sc.LENGTH as varchar(50)) + ')'
when st.name = 'decimal' then '(' + CAST(sc.LENGTH as varchar(50)) + ','
+ CAST(sc.PREC as varchar(50)) + ')'
when st.name = 'numeric' then '(' + CAST(sc.LENGTH as varchar(50)) + ',' +
CAST(sc.PREC as varchar(50)) + ')'
else ''
end
+ ' ' +
CASE when sc.ISNULLABLE = 1 then 'NULL' else 'NOT NULL' end
from syscolumns sc
INNER
JOIN systypes st on sc.xtype = st.xtype
where sc.id = @TabID
order by sc.colid
open ColNameCur
fetch next from ColNameCur into @ColName
set @Count = 1
while @@FETCH_STATUS = 0 -- ColNameCur
begin
if @Count = 1
begin
set @DDLDef = @DDLDef + ' ' + @ColName + Char(13)
set @Count = @Count + 1
end
else
begin
set @DDLDef = @DDLDef + ' , ' + @ColName + Char(13)
set @Count = @Count + 1
end
fetch next from ColNameCur into @ColName
end
/* ColNameCur @@FetchStatus */
set @DDLDef = @DDLDef + ') ON [' + @FileGroup + ']' + Char(13) + 'GO'
PRINT @DDLDEF
set @DDLDef = ''
close ColNameCur
deallocate ColNameCur
fetch next from TabNameCur into @TabName, @TabID, @FileGroup
end
close TabNameCur
deallocate TabNameCur
fetch
next
from DBCur into @DBNAME
end
close DBCur
deallocate DBCur