Ads
15 March 2011
COMPARE 2 DB's
CREATE PROC sp_CompareDB
@db1 varchar(128),
@db2 varchar(128),
@OnlyStructure bit = 0,
@TabList varchar(8000) = '',
@NumbToShow int = 10,
@NoTimestamp bit = 0,
@VerboseLevel tinyint = 0
AS
if @OnlyStructure <> 0
set @OnlyStructure = 1
if @NoTimestamp <> 0
set @NoTimestamp = 1
if @VerboseLevel <> 0
set @VerboseLevel = 1
SET NOCOUNT ON
SET ANSI_WARNINGS ON
SET ANSI_NULLS ON
declare @sqlStr varchar(8000)
set nocount on
-- Checking if there are specified databases
declare @SrvName sysname
declare @DBName sysname
set @db1 = RTRIM(LTRIM(@db1))
set @db2 = RTRIM(LTRIM(@db2))
set @SrvName = @@SERVERNAME
if CHARINDEX('.',@db1) > 0
begin
set @SrvName = LEFT(@db1,CHARINDEX('.',@db1)-1)
if not exists (select * from master.dbo.sysservers where srvname = @SrvName)
begin
print 'There is no linked server named '+@SrvName+'. End of work.'
return
end
set @DBName = RIGHT(@db1,LEN(@db1)-CHARINDEX('.',@db1))
end
else
set @DBName = @db1
exec ('declare @Name sysname select @Name=name from ['+@SrvName+'].master.dbo.sysdatabases where name = '''+@DBName+'''')
if @@rowcount = 0
begin
print 'There is no database named '+@db1+'. End of work.'
return
end
set @SrvName = @@SERVERNAME
if CHARINDEX('.',@db2) > 0
begin
set @SrvName = LEFT(@db2,CHARINDEX('.',@db2)-1)
if not exists (select * from master.dbo.sysservers where srvname = @SrvName)
begin
print 'There is no linked server named '+@SrvName+'. End of work.'
return
end
set @DBName = RIGHT(@db2,LEN(@db2)-CHARINDEX('.',@db2))
end
else
set @DBName = @db2
exec ('declare @Name sysname select @Name=name from ['+@SrvName+'].master.dbo.sysdatabases where name = '''+@DBName+'''')
if @@rowcount = 0
begin
print 'There is no database named '+@db2+'. End of work.'
return
end
print Replicate('-',LEN(@db1)+LEN(@db2)+25)
print 'Comparing databases '+@db1+' and '+@db2
print Replicate('-',LEN(@db1)+LEN(@db2)+25)
print 'Options specified:'
print ' Compare only structures: '+CASE WHEN @OnlyStructure = 0 THEN 'No' ELSE 'Yes' END
print ' List of tables to compare: '+CASE WHEN LEN(@TabList) = 0 THEN ' All tables' ELSE @TabList END
print ' Max number of different rows in each table to show: '+LTRIM(STR(@NumbToShow))
print ' Compare timestamp columns: '+CASE WHEN @NoTimestamp = 0 THEN 'No' ELSE 'Yes' END
print ' Verbose level: '+CASE WHEN @VerboseLevel = 0 THEN 'Low' ELSE 'High' END
-----------------------------------------------------------------------------------------
-- Comparing structures
-----------------------------------------------------------------------------------------
print CHAR(10)+Replicate('-',36)
print 'Comparing structure of the databases'
print Replicate('-',36)
if exists (select * from tempdb.dbo.sysobjects where name like '#TabToCheck%')
drop table #TabToCheck
create table #TabToCheck (name sysname)
declare @NextCommaPos int
if len(@TabList) > 0
begin
while 1=1
begin
set @NextCommaPos = CHARINDEX(',',@TabList)
if @NextCommaPos = 0
begin
set @sqlstr = 'insert into #TabToCheck values('''+@TabList+''')'
exec (@sqlstr)
break
end
set @sqlstr = 'insert into #TabToCheck values('''+LEFT(@TabList,@NextCommaPos-1)+''')'
exec (@sqlstr)
set @TabList = RIGHT(@TabList,LEN(@TabList)-@NextCommaPos)
end
end
else -- then will check all tables
begin
exec ('insert into #TabToCheck select name from '+@db1+'.dbo.sysobjects where type = ''U''')
exec ('insert into #TabToCheck select name from '+@db2+'.dbo.sysobjects where type = ''U''')
end
-- First check if at least one table specified in @TabList exists in db1
exec ('declare @Name sysname select @Name=name from '+@db1+'.dbo.sysobjects where name in (select * from #TabToCheck)')
if @@rowcount = 0
begin
print 'No tables in '+@db1+' to check. End of work.'
return
end
-- Check if tables existing in db1 are in db2 (all tables or specified in @TabList)
if exists (select * from tempdb.dbo.sysobjects where name like '#TabNotInDB2%')
drop table #TabNotInDB2
create table #TabNotInDB2 (name sysname)
insert into #TabNotInDB2
exec ('select name from '+@db1+'.dbo.sysobjects d1o '+
'where name in (select * from #TabToCheck) and '+
' d1o.type = ''U'' and not exists '+
'(select * from '+@db2+'.dbo.sysobjects d2o'+
' where d2o.type = ''U'' and d2o.name = d1o.name)')
if @@rowcount > 0
begin
print CHAR(10)+'The table(s) exist in '+@db1+', but do not exist in '+@db2+':'
select * from #TabNotInDB2
end
delete from #TabToCheck where name in (select * from #TabNotInDB2)
drop table #TabNotInDB2
if exists (select * from tempdb.dbo.sysobjects where name like '#TabNotInDB1%')
drop table #TabNotInDB1
create table #TabNotInDB1 (name sysname)
insert into #TabNotInDB1
exec ('select name from '+@db2+'.dbo.sysobjects d1o '+
'where name in (select * from #TabToCheck) and '+
' d1o.type = ''U'' and not exists '+
'(select * from '+@db1+'.dbo.sysobjects d2o'+
' where d2o.type = ''U'' and d2o.name = d1o.name)')
if @@rowcount > 0
begin
print CHAR(10)+'The table(s) exist in '+@db2+', but do not exist in '+@db1+':'
select * from #TabNotInDB1
end
delete from #TabToCheck where name in (select * from #TabNotInDB1)
drop table #TabNotInDB1
-- Comparing structures of tables existing in both dbs
print CHAR(10)+'Checking if there are tables existing in both databases having structural differences ...'+CHAR(10)
if exists (select * from tempdb.dbo.sysobjects where name like '#DiffStructure%')
drop table #DiffStructure
create table #DiffStructure (name sysname)
set @sqlStr='
declare @TName1 sysname, @TName2 sysname, @CName1 sysname, @CName2 sysname,
@TypeName1 sysname, @TypeName2 sysname,
@CLen1 smallint, @CLen2 smallint, @Type1 sysname, @Type2 sysname, @PrevTName sysname
declare @DiffStructure bit
declare Diff cursor fast_forward for
select d1o.name, d2o.name, d1c.name, d2c.name, d1t.name, d2t.name,
d1c.length, d2c.length, d1c.type, d2c.type
from ('+@db1+'.dbo.sysobjects d1o
JOIN '+@db2+'.dbo.sysobjects d2o2 ON d1o.name = d2o2.name and d1o.type = ''U'' --only tables in both dbs
and d1o.name in (select * from #TabToCheck)
JOIN '+@db1+'.dbo.syscolumns d1c ON d1o.id = d1c.id
JOIN '+@db1+'.dbo.systypes d1t ON d1c.xusertype = d1t.xusertype)
FULL JOIN ('+@db2+'.dbo.sysobjects d2o
JOIN '+@db1+'.dbo.sysobjects d1o2 ON d1o2.name = d2o.name and d2o.type = ''U'' --only tables in both dbs
and d2o.name in (select * from #TabToCheck)
JOIN '+@db2+'.dbo.syscolumns d2c ON d2c.id = d2o.id
JOIN '+@db2+'.dbo.systypes d2t ON d2c.xusertype = d2t.xusertype)
ON d1o.name = d2o.name and d1c.name = d2c.name
WHERE (not exists
(select * from '+@db2+'.dbo.sysobjects d2o2
JOIN '+@db2+'.dbo.syscolumns d2c2 ON d2o2.id = d2c2.id
JOIN '+@db2+'.dbo.systypes d2t2 ON d2c2.xusertype = d2t2.xusertype
where d2o2.type = ''U''
and d2o2.name = d1o.name
and d2c2.name = d1c.name
and d2t2.name = d1t.name
and d2c2.length = d1c.length)
OR not exists
(select * from '+@db1+'.dbo.sysobjects d1o2
JOIN '+@db1+'.dbo.syscolumns d1c2 ON d1o2.id = d1c2.id
JOIN '+@db1+'.dbo.systypes d1t2 ON d1c2.xusertype = d1t2.xusertype
where d1o2.type = ''U''
and d1o2.name = d2o.name
and d1c2.name = d2c.name
and d1t2.name = d2t.name
and d1c2.length = d2c.length))
order by coalesce(d1o.name,d2o.name), d1c.name
open Diff
fetch next from Diff into @TName1, @TName2, @CName1, @CName2, @TypeName1, @TypeName2,
@CLen1, @CLen2, @Type1, @Type2
set @PrevTName = ''''
set @DiffStructure = 0
while @@fetch_status = 0
begin
if Coalesce(@TName1,@TName2) <> @PrevTName
begin
if @PrevTName <> '''' and @DiffStructure = 1
begin
insert into #DiffStructure values (@PrevTName)
set @DiffStructure = 0
end
set @PrevTName = Coalesce(@TName1,@TName2)
print @PrevTName
end
if @CName2 is null
print '' Colimn ''+RTRIM(@CName1)+'' not in '+@db2+'''
else
if @CName1 is null
print '' Colimn ''+RTRIM(@CName2)+'' not in '+@db1+'''
else
if @TypeName1 <> @TypeName2
print '' Colimn ''+RTRIM(@CName1)+'': in '+@db1+' - ''+RTRIM(@TypeName1)+'', in '+@db2+' - ''+RTRIM(@TypeName2)
else --the columns are not null(are in both dbs) and types are equal,then length are diff
print '' Colimn ''+RTRIM(@CName1)+'': in '+@db1+' - ''+RTRIM(@TypeName1)+''(''+
LTRIM(STR(CASE when @TypeName1=''nChar'' or @TypeName1 = ''nVarChar'' then @CLen1/2 else @CLen1 end))+
''), in '+@db2+' - ''+RTRIM(@TypeName2)+''(''+
LTRIM(STR(CASE when @TypeName1=''nChar'' or @TypeName1 = ''nVarChar'' then @CLen2/2 else @CLen2 end))+'')''
if @Type1 = @Type2
set @DiffStructure=@DiffStructure -- Do nothing. Cannot invert predicate
else
set @DiffStructure = 1
fetch next from Diff into @TName1, @TName2, @CName1, @CName2, @TypeName1, @TypeName2,
@CLen1, @CLen2, @Type1, @Type2
end
deallocate Diff
if @DiffStructure = 1
insert into #DiffStructure values (@PrevTName)
'
exec (@sqlStr)
if (select count(*) from #DiffStructure) > 0
begin
print CHAR(10)+'The table(s) have the same name and different structure in the databases:'
select distinct * from #DiffStructure
delete from #TabToCheck where name in (select * from #DiffStructure)
end
else
print CHAR(10)+'There are no tables with the same name and structural differences in the databases'+CHAR(10)+CHAR(10)
if @OnlyStructure = 1
begin
print 'The option ''Only compare structures'' was specified. End of work.'
return
end
exec ('declare @Name sysname select @Name=d1o.name
from '+@db1+'.dbo.sysobjects d1o, '+@db2+'.dbo.sysobjects d2o
where d1o.name = d2o.name and d1o.type = ''U'' and d2o.type = ''U''
and d1o.name not in (''dtproperties'')
and d1o.name in (select * from #TabToCheck)')
if @@rowcount = 0
begin
print 'There are no tables with the same name and structure in the databases to compare. End of work.'
return
end
-----------------------------------------------------------------------------------------
-- Comparing data
-----------------------------------------------------------------------------------------
-- ##CompareStr - will be used to pass comparing strings into dynamic script
-- to execute the string
if exists (select * from tempdb.dbo.sysobjects where name like '##CompareStr%')
drop table ##CompareStr
create table ##CompareStr (Ind int, CompareStr varchar(8000))
if exists (select * from tempdb.dbo.sysobjects where name like '#DiffTables%')
drop table #DiffTables
create table #DiffTables (Name sysname)
if exists (select * from tempdb.dbo.sysobjects where name like '#IdenticalTables%')
drop table #IdenticalTables
create table #IdenticalTables (Name sysname)
if exists (select * from tempdb.dbo.sysobjects where name like '#EmptyTables%')
drop table #EmptyTables
create table #EmptyTables (Name sysname)
if exists (select * from tempdb.dbo.sysobjects where name like '#NoPKTables%')
drop table #NoPKTables
create table #NoPKTables (Name sysname)
if exists (select * from tempdb.dbo.sysobjects where name like '#IndList1%')
truncate table #IndList1
else
create table #IndList1 (IndId int, IndStatus int,
KeyAndStr varchar(7000), KeyCommaStr varchar(1000))
if exists (select * from tempdb.dbo.sysobjects where name like '#IndList2%')
truncate table #IndList2
else
create table #IndList2 (IndId smallint, IndStatus int,
KeyAndStr varchar(7000), KeyCommaStr varchar(1000))
print Replicate('-',51)
print 'Comparing data in tables with indentical structure:'
print Replicate('-',51)
--------------------------------------------------------------------------------------------
-- Cursor for all tables in dbs (or for all specified tables if parameter @TabList is passed)
--------------------------------------------------------------------------------------------
declare @SqlStrGetListOfKeys1 varchar(8000)
declare @SqlStrGetListOfKeys2 varchar(8000)
declare @SqlStrGetListOfColumns varchar(8000)
declare @SqlStrCompareUKeyTables varchar(8000)
declare @SqlStrCompareNonUKeyTables varchar(8000)
set @SqlStrGetListOfKeys1 = '
declare @sqlStr varchar(8000)
declare @ExecSqlStr varchar(8000)
declare @PrintSqlStr varchar(8000)
declare @Tab varchar(128)
declare @d1User varchar(128)
declare @d2User varchar(128)
declare @KeyAndStr varchar(8000)
declare @KeyCommaStr varchar(8000)
declare @AndStr varchar(8000)
declare @Eq varchar(8000)
declare @IndId int
declare @IndStatus int
declare @CurrIndId smallint
declare @CurrStatus int
declare @UKey sysname
declare @Col varchar(128)
declare @LastUsedCol varchar(128)
declare @xType int
declare @Len int
declare @SelectStr varchar(8000)
declare @ExecSql nvarchar(1000)
declare @NotInDB1 bit
declare @NotInDB2 bit
declare @NotEq bit
declare @Numb int
declare @Cnt1 int
declare @Cnt2 int
set @Numb = 0
declare @StrInd int
declare @i int
declare @PrintStr varchar(8000)
declare @ExecStr varchar(8000)
declare TabCur cursor for
select d1o.name, d1u.name, d2u.name from '+@db1+'.dbo.sysobjects d1o, '+@db2+'.dbo.sysobjects d2o,
'+@db1+'.dbo.sysusers d1u, '+@db2+'.dbo.sysusers d2u
where d1o.name = d2o.name and d1o.type = ''U'' and d2o.type = ''U''
and d1o.uid = d1u.uid and d2o.uid = d2u.uid
and d1o.name not in (''dtproperties'')
and d1o.name in (select * from #TabToCheck)
order by 1
open TabCur
fetch next from TabCur into @Tab, @d1User, @d2User
while @@fetch_status = 0
begin
set @Numb = @Numb + 1
print Char(13)+Char(10)+LTRIM(STR(@Numb))+''. TABLE: [''+@Tab+''] ''
set @ExecSql = ''SELECT @Cnt = count(*) FROM '+@db1+'.[''+@d1User+''].[''+@Tab+'']''
exec sp_executesql @ExecSql, N''@Cnt int output'', @Cnt = @Cnt1 output
print CHAR(10)+STR(@Cnt1)+'' rows in '+@db1+'''
set @ExecSql = ''SELECT @Cnt = count(*) FROM '+@db2+'.[''+@d2User+''].[''+@Tab+'']''
exec sp_executesql @ExecSql, N''@Cnt int output'', @Cnt = @Cnt2 output
print STR(@Cnt2)+'' rows in '+@db2+'''
if @Cnt1 = 0 and @Cnt2 = 0
begin
exec ('' insert into #EmptyTables values(''''[''+@Tab+'']'''')'')
goto NextTab
end
set @KeyAndStr = ''''
set @KeyCommaStr = ''''
set @NotInDB1 = 0
set @NotInDB2 = 0
set @NotEq = 0
set @KeyAndStr = ''''
set @KeyCommaStr = ''''
truncate table #IndList1
declare UKeys cursor fast_forward for
select i.indid, i.status, c.name, c.xType from '+@db1+'.dbo.sysobjects o, '+@db1+'.dbo.sysindexes i, '+@db1+'.dbo.sysindexkeys k, '+@db1+'.dbo.syscolumns c
where i.id = o.id and o.name = @Tab
and (i.status & 2)<>0
and k.id = o.id and k.indid = i.indid
and c.id = o.id and c.colid = k.colid
order by i.indid, c.name
open UKeys
fetch next from UKeys into @IndId, @IndStatus, @UKey, @xType
set @CurrIndId = @IndId
set @CurrStatus = @IndStatus
while @@fetch_status = 0
begin
if @KeyAndStr <> ''''
begin
set @KeyAndStr = @KeyAndStr + '' and '' + CHAR(10)
set @KeyCommaStr = @KeyCommaStr + '', ''
end
if @xType = 175 or @xType = 167 or @xType = 239 or @xType = 231 -- char, varchar, nchar, nvarchar
begin
set @KeyAndStr = @KeyAndStr + '' ISNULL(d1.[''+@UKey+''],''''!#null$'''')=ISNULL(d2.[''+@UKey+''],''''!#null$'''') ''
end
if @xType = 173 or @xType = 165 -- binary, varbinary
begin
set @KeyAndStr = @KeyAndStr +
'' CASE WHEN d1.[''+@UKey+''] is null THEN 0x4D4FFB23A49411D5BDDB00A0C906B7B4 ELSE d1.[''+@UKey+''] END=''+
''CASE WHEN d2.[''+@UKey+''] is null THEN 0x4D4FFB23A49411D5BDDB00A0C906B7B4 ELSE d2.[''+@UKey+''] END ''
end
else if @xType = 56 or @xType = 127 or @xType = 60 or @xType = 122 -- int, 127 - bigint,60 - money, 122 - smallmoney
begin
set @KeyAndStr = @KeyAndStr +
'' CASE WHEN d1.[''+@UKey+''] is null THEN 971428763405345098745 ELSE d1.[''+@UKey+''] END=''+
''CASE WHEN d2.[''+@UKey+''] is null THEN 971428763405345098745 ELSE d2.[''+@UKey+''] END ''
end
else if @xType = 106 or @xType = 108 -- int, decimal, numeric
begin
set @KeyAndStr = @KeyAndStr +
'' CASE WHEN d1.[''+@UKey+''] is null THEN 71428763405345098745098.8723 ELSE d1.[''+@UKey+''] END=''+
''CASE WHEN d2.[''+@UKey+''] is null THEN 71428763405345098745098.8723 ELSE d2.[''+@UKey+''] END ''
end
else if @xType = 62 or @xType = 59 -- 62 - float, 59 - real
begin
set @KeyAndStr = @KeyAndStr +
'' CASE WHEN d1.[''+@UKey+''] is null THEN 8764589764.22708E237 ELSE d1.[''+@UKey+''] END=''+
''CASE WHEN d2.[''+@UKey+''] is null THEN 8764589764.22708E237 ELSE d2.[''+@UKey+''] END ''
end
else if @xType = 52 or @xType = 48 or @xType = 104 -- smallint, tinyint, bit
begin
set @KeyAndStr = @KeyAndStr + '' CASE WHEN d1.[''+@UKey+''] is null THEN 99999 ELSE d1.[''+@UKey+''] END=''+
''CASE WHEN d2.[''+@UKey+''] is null THEN 99999 ELSE d2.[''+@UKey+''] END ''
end
else if @xType = 36 -- 36 - id
begin
set @KeyAndStr = @KeyAndStr +
'' CASE WHEN d1.[''+@UKey+''] is null''+
'' THEN CONVERT(uniqueidentifier,''''1CD827A0-744A-4866-8401-B9902CF2D4FB'''')''+
'' ELSE d1.[''+@UKey+''] END=''+
''CASE WHEN d2.[''+@UKey+''] is null''+
'' THEN CONVERT(uniqueidentifier,''''1CD827A0-744A-4866-8401-B9902CF2D4FB'''')''+
'' ELSE d2.[''+@UKey+''] END''
end
else if @xType = 61 or @xType = 58 -- datetime, smalldatetime
begin
set @KeyAndStr = @KeyAndStr +
'' CASE WHEN d1.[''+@UKey+''] is null THEN ''''!#null$'''' ELSE CONVERT(varchar(40),d1.[''+@UKey+''],109) END=''+
''CASE WHEN d2.[''+@UKey+''] is null THEN ''''!#null$'''' ELSE CONVERT(varchar(40),d2.[''+@UKey+''],109) END ''
end
else if @xType = 189 -- timestamp (189)
begin
set @KeyAndStr = @KeyAndStr + '' d1.[''+@UKey+'']=d2.[''+@UKey+''] ''
end
else if @xType = 98 -- SQL_variant
begin
set @KeyAndStr = @KeyAndStr + '' ISNULL(d1.[''+@UKey+''],''''!#null$'''')=ISNULL(d2.[''+@UKey+''],''''!#null$'''') ''
end
set @KeyCommaStr = @KeyCommaStr + '' d1.''+@UKey
fetch next from UKeys into @IndId, @IndStatus, @UKey, @xType
if @IndId <> @CurrIndId
begin
insert into #IndList1 values (@CurrIndId, @CurrStatus, @KeyAndStr, @KeyCommaStr)
set @CurrIndId = @IndId
set @CurrStatus = @IndStatus
set @KeyAndStr = ''''
set @KeyCommaStr = ''''
end
end
deallocate UKeys
insert into #IndList1 values (@CurrIndId, @CurrStatus, @KeyAndStr, @KeyCommaStr)'
set @SqlStrGetListOfKeys2 = '
set @KeyAndStr = ''''
set @KeyCommaStr = ''''
truncate table #IndList2
declare UKeys cursor fast_forward for
select i.indid, i.status, c.name, c.xType from '+@db2+'.dbo.sysobjects o, '+@db2+'.dbo.sysindexes i, '+@db2+'.dbo.sysindexkeys k, '+@db2+'.dbo.syscolumns c
where i.id = o.id and o.name = @Tab
and (i.status & 2)<>0
and k.id = o.id and k.indid = i.indid
and c.id = o.id and c.colid = k.colid
order by i.indid, c.name
open UKeys
fetch next from UKeys into @IndId, @IndStatus, @UKey, @xType
set @CurrIndId = @IndId
set @CurrStatus = @IndStatus
while @@fetch_status = 0
begin
if @KeyAndStr <> ''''
begin
set @KeyAndStr = @KeyAndStr + '' and '' + CHAR(10)
set @KeyCommaStr = @KeyCommaStr + '', ''
end
if @xType = 175 or @xType = 167 or @xType = 239 or @xType = 231 -- char, varchar, nchar, nvarchar
begin
set @KeyAndStr = @KeyAndStr + '' ISNULL(d1.[''+@UKey+''],''''!#null$'''')=ISNULL(d2.[''+@UKey+''],''''!#null$'''') ''
end
if @xType = 173 or @xType = 165 -- binary, varbinary
begin
set @KeyAndStr = @KeyAndStr +
'' CASE WHEN d1.[''+@UKey+''] is null THEN 0x4D4FFB23A49411D5BDDB00A0C906B7B4 ELSE d1.[''+@UKey+''] END=''+
''CASE WHEN d2.[''+@UKey+''] is null THEN 0x4D4FFB23A49411D5BDDB00A0C906B7B4 ELSE d2.[''+@UKey+''] END ''
end
else if @xType = 56 or @xType = 127 or @xType = 60 or @xType = 122 -- int, 127 - bigint,60 - money, 122 - smallmoney
begin
set @KeyAndStr = @KeyAndStr +
'' CASE WHEN d1.[''+@UKey+''] is null THEN 971428763405345098745 ELSE d1.[''+@UKey+''] END=''+
''CASE WHEN d2.[''+@UKey+''] is null THEN 971428763405345098745 ELSE d2.[''+@UKey+''] END ''
end
else if @xType = 106 or @xType = 108 -- int, decimal, numeric
begin
set @KeyAndStr = @KeyAndStr +
'' CASE WHEN d1.[''+@UKey+''] is null THEN 71428763405345098745098.8723 ELSE d1.[''+@UKey+''] END=''+
''CASE WHEN d2.[''+@UKey+''] is null THEN 71428763405345098745098.8723 ELSE d2.[''+@UKey+''] END ''
end
else if @xType = 62 or @xType = 59 -- 62 - float, 59 - real
begin
set @KeyAndStr = @KeyAndStr +
'' CASE WHEN d1.[''+@UKey+''] is null THEN 8764589764.22708E237 ELSE d1.[''+@UKey+''] END=''+
''CASE WHEN d2.[''+@UKey+''] is null THEN 8764589764.22708E237 ELSE d2.[''+@UKey+''] END ''
end
else if @xType = 52 or @xType = 48 or @xType = 104 -- smallint, tinyint, bit
begin
set @KeyAndStr = @KeyAndStr + '' CASE WHEN d1.[''+@UKey+''] is null THEN 99999 ELSE d1.[''+@UKey+''] END=''+
''CASE WHEN d2.[''+@UKey+''] is null THEN 99999 ELSE d2.[''+@UKey+''] END ''
end
else if @xType = 36 -- 36 - id
begin
set @KeyAndStr = @KeyAndStr +
'' CASE WHEN d1.[''+@UKey+''] is null''+
'' THEN CONVERT(uniqueidentifier,''''1CD827A0-744A-4866-8401-B9902CF2D4FB'''')''+
'' ELSE d1.[''+@UKey+''] END=''+
''CASE WHEN d2.[''+@UKey+''] is null''+
'' THEN CONVERT(uniqueidentifier,''''1CD827A0-744A-4866-8401-B9902CF2D4FB'''')''+
'' ELSE d2.[''+@UKey+''] END''
end
else if @xType = 61 or @xType = 58 -- datetime, smalldatetime
begin
set @KeyAndStr = @KeyAndStr +
'' CASE WHEN d1.[''+@UKey+''] is null THEN ''''!#null$'''' ELSE CONVERT(varchar(40),d1.[''+@UKey+''],109) END=''+
''CASE WHEN d2.[''+@UKey+''] is null THEN ''''!#null$'''' ELSE CONVERT(varchar(40),d2.[''+@UKey+''],109) END ''
end
else if @xType = 189 -- timestamp (189)
begin
set @KeyAndStr = @KeyAndStr + '' d1.[''+@UKey+'']=d2.[''+@UKey+''] ''
end
else if @xType = 98 -- SQL_variant
begin
set @KeyAndStr = @KeyAndStr + '' ISNULL(d1.[''+@UKey+''],''''!#null$'''')=ISNULL(d2.[''+@UKey+''],''''!#null$'''') ''
end
set @KeyCommaStr = @KeyCommaStr + '' d1.''+@UKey
fetch next from UKeys into @IndId, @IndStatus, @UKey, @xType
if @IndId <> @CurrIndId
begin
insert into #IndList2 values (@CurrIndId, @CurrStatus, @KeyAndStr, @KeyCommaStr)
set @CurrIndId = @IndId
set @CurrStatus = @IndStatus
set @KeyAndStr = ''''
set @KeyCommaStr = ''''
end
end
deallocate UKeys
insert into #IndList2 values (@CurrIndId, @CurrStatus, @KeyAndStr, @KeyCommaStr)
set @KeyCommaStr = null
select @KeyCommaStr=i1.KeyCommaStr from #IndList1 i1
join #IndList2 i2 on i1.KeyCommaStr = i2.KeyCommaStr
where (i1.IndStatus & 2048)<> 0 and (i2.IndStatus & 2048)<>0
if @KeyCommaStr is null
set @KeyCommaStr = (select top 1 i1.KeyCommaStr from #IndList1 i1
join #IndList2 i2 on i1.KeyCommaStr = i2.KeyCommaStr)
set @KeyAndStr = (select TOP 1 KeyAndStr from #IndList1 where KeyCommaStr = @KeyCommaStr)
if @KeyCommaStr is null
set @KeyCommaStr = ''''
if @KeyAndStr is null
set @KeyAndStr = '''''
set @SqlStrGetListOfColumns = '
set @AndStr = ''''
set @StrInd = 1
declare Cols cursor local fast_forward for
select c.name, c.xtype, c.length from '+@db1+'.dbo.sysobjects o, '+@db1+'.dbo.syscolumns c
where o.id = c.id and o.name = @Tab
and CHARINDEX(c.name, @KeyCommaStr) = 0
open Cols
fetch next from Cols into @Col, @xType, @len
while @@fetch_status = 0
begin
if @xType = 175 or @xType = 167 or @xType = 239 or @xType = 231 -- char, varchar, nchar, nvarchar
begin
set @Eq = ''ISNULL(d1.[''+@Col+''],''''!#null$'''')=ISNULL(d2.[''+@Col+''],''''!#null$'''') ''
end
if @xType = 173 or @xType = 165 -- binary, varbinary
begin
set @Eq = ''CASE WHEN d1.[''+@Col+''] is null THEN 0x4D4FFB23A49411D5BDDB00A0C906B7B4 ELSE d1.[''+@Col+''] END=''+
''CASE WHEN d2.[''+@Col+''] is null THEN 0x4D4FFB23A49411D5BDDB00A0C906B7B4 ELSE d2.[''+@Col+''] END ''
end
else if @xType = 56 or @xType = 127 or @xType = 60 or @xType = 122 -- int, 127 - bigint,60 - money, 122 - smallmoney
begin
set @Eq = ''CASE WHEN d1.[''+@Col+''] is null THEN 971428763405345098745 ELSE d1.[''+@Col+''] END=''+
''CASE WHEN d2.[''+@Col+''] is null THEN 971428763405345098745 ELSE d2.[''+@Col+''] END ''
end
else if @xType = 106 or @xType = 108 -- int, decimal, numeric
begin
set @Eq = ''CASE WHEN d1.[''+@Col+''] is null THEN 71428763405345098745098.8723 ELSE d1.[''+@Col+''] END=''+
''CASE WHEN d2.[''+@Col+''] is null THEN 71428763405345098745098.8723 ELSE d2.[''+@Col+''] END ''
end
else if @xType = 62 or @xType = 59 -- 62 - float, 59 - real
begin
set @Eq = ''CASE WHEN d1.[''+@Col+''] is null THEN 8764589764.22708E237 ELSE d1.[''+@Col+''] END=''+
''CASE WHEN d2.[''+@Col+''] is null THEN 8764589764.22708E237 ELSE d2.[''+@Col+''] END ''
end
else if @xType = 52 or @xType = 48 or @xType = 104 -- smallint, tinyint, bit
begin
set @Eq = ''CASE WHEN d1.[''+@Col+''] is null THEN 99999 ELSE d1.[''+@Col+''] END=''+
''CASE WHEN d2.[''+@Col+''] is null THEN 99999 ELSE d2.[''+@Col+''] END ''
end
else if @xType = 36 -- 36 - id
begin
set @Eq = ''CASE WHEN d1.[''+@Col+''] is null''+
'' THEN CONVERT(uniqueidentifier,''''1CD827A0-744A-4866-8401-B9902CF2D4FB'''')''+
'' ELSE d1.[''+@Col+''] END=''+
''CASE WHEN d2.[''+@Col+''] is null''+
'' THEN CONVERT(uniqueidentifier,''''1CD827A0-744A-4866-8401-B9902CF2D4FB'''')''+
'' ELSE d2.[''+@Col+''] END''
end
else if @xType = 61 or @xType = 58 -- datetime, smalldatetime
begin
set @Eq =
''CASE WHEN d1.[''+@Col+''] is null THEN ''''!#null$'''' ELSE CONVERT(varchar(40),d1.[''+@Col+''],109) END=''+
''CASE WHEN d2.[''+@Col+''] is null THEN ''''!#null$'''' ELSE CONVERT(varchar(40),d2.[''+@Col+''],109) END ''
end
else if @xType = 34
begin
set @Eq = ''ISNULL(DATALENGTH(d1.[''+@Col+'']),0)=ISNULL(DATALENGTH(d2.[''+@Col+'']),0) ''
end
else if @xType = 35 or @xType = 99 -- text (35),ntext (99)
begin
set @Eq = ''ISNULL(SUBSTRING(d1.[''+@Col+''],1,DATALENGTH(d1.[''+@Col+
''])),''''!#null$'''')=ISNULL(SUBSTRING(d2.[''+@Col+''],1,DATALENGTH(d2.[''+@Col+''])),''''!#null$'''') ''
end
else if @xType = 189
begin
if '+STR(@NoTimestamp)+' = 0
set @Eq = ''d1.[''+@Col+'']=d2.[''+@Col+''] ''
else
set @Eq = ''1=1''
end
else if @xType = 98 -- SQL_variant
begin
set @Eq = ''ISNULL(d1.[''+@Col+''],''''!#null$'''')=ISNULL(d2.[''+@Col+''],''''!#null$'''') ''
end
if @AndStr = ''''
set @AndStr = @AndStr + CHAR(10) + '' '' + @Eq
else
if len(@AndStr) + len('' and '' + @Eq)<8000
set @AndStr = @AndStr + '' and '' + CHAR(10) + '' '' + @Eq
else
begin
set @StrInd = @StrInd + 1
Insert into ##CompareStr values(@StrInd,@AndStr)
set @AndStr = '' and '' + @Eq
end
fetch next from Cols into @Col, @xType, @len
end
deallocate Cols '
set @SqlStrCompareUKeyTables = '
if @KeyAndStr <> ''''
begin
set @SelectStr = ''SELECT ''+ @KeyCommaStr+'' INTO ##NotInDb2 FROM '+@db1+'.[''+@d1User+''].[''+@Tab+''] d1 ''+
'' WHERE not exists''+CHAR(10)+'' (SELECT * FROM '+@db2+'.[''+@d2User+''].[''+@Tab+''] d2 ''+
'' WHERE ''+CHAR(10)+@KeyAndStr+'')''
if '+STR(@VerboseLevel)+' = 1
print CHAR(10)+''To find rows that are in '+@db1+', but are not in db2:''+CHAR(10)+
REPLACE (@SelectStr, ''into ##NotInDB2'','''')
exec (@SelectStr)
if @@rowcount > 0
set @NotInDB2 = 1
set @SelectStr = ''SELECT ''+@KeyCommaStr+'' INTO ##NotInDB1 FROM '+@db2+'.[''+@d2User+''].[''+@Tab+''] d1 ''+
'' WHERE not exists''+CHAR(10)+'' (SELECT * FROM '+@db1+'.[''+@d1User+''].[''+@Tab+''] d2 ''+
'' WHERE ''+CHAR(10)+@KeyAndStr+'')''
if '+STR(@VerboseLevel)+' = 1
print CHAR(10)+''To find rows that are in '+@db2+', but are not in '+@db1+':''+CHAR(10)+
REPLACE (@SelectStr, ''into ##NotInDB1'','''')
exec (@SelectStr)
if @@rowcount > 0
set @NotInDB1 = 1
-- if there are non-key columns
if @AndStr <> ''''
begin
set @PrintStr = '' Print ''
set @ExecStr = '' exec (''
set @SqlStr = ''''
Insert into ##CompareStr values(1,
''SELECT ''+ @KeyCommaStr+'' INTO ##NotEq FROM '+@db2+'.[''+@d2User+''].[''+@Tab+''] d1 ''+
'' INNER JOIN '+@db1+'.[''+@d1User+''].[''+@Tab+''] d2 ON ''+CHAR(10)+@KeyAndStr+CHAR(10)+''WHERE not('')
-- Adding last string in temp table containing a comparing string to execute
set @StrInd = @StrInd + 1
Insert into ##CompareStr values(@StrInd,@AndStr+'')'')
set @i = 1
while @i <= @StrInd
begin
set @SqlStr = @SqlStr + '' declare @Str''+LTRIM(STR(@i))+'' varchar(8000) ''+
''select @Str''+LTRIM(STR(@i))+''=CompareStr FROM ##CompareStr WHERE ind = ''+STR(@i)
if @ExecStr <> '' exec (''
set @ExecStr = @ExecStr + ''+''
if @PrintStr <> '' Print ''
set @PrintStr = @PrintStr + ''+''
set @ExecStr = @ExecStr + ''@Str''+LTRIM(STR(@i))
set @PrintStr = @PrintStr + '' REPLACE(@Str''+LTRIM(STR(@i))+'','''' into ##NotEq'''','''''''') ''
set @i = @i + 1
end
set @ExecStr = @ExecStr + '') ''
set @ExecSqlStr = @SqlStr + @ExecStr
set @PrintSqlStr = @SqlStr +
'' Print CHAR(10)+''''To find rows that are different in non-key columns:'''' ''+
@PrintStr
if '+STR(@VerboseLevel)+' = 1
exec (@PrintSqlStr)
exec (@ExecSqlStr)
if @@rowcount > 0
set @NotEq = 1
end
else
if '+STR(@VerboseLevel)+' = 1
print CHAR(10)+''There are no non-key columns in the table''
truncate table ##CompareStr
if @NotInDB1 = 1 or @NotInDB2 = 1 or @NotEq = 1
begin
print CHAR(10)+''Data are different''
if @NotInDB2 = 1 and '+STR(@NumbToShow)+' > 0
begin
print ''These key values exist in '+@db1+', but do not exist in '+@db2+': ''
set @SelectStr = ''select top ''+STR('+STR(@NumbToShow)+')+'' * from ##NotInDB2''
exec (@SelectStr)
end
if @NotInDB1 = 1 and '+STR(@NumbToShow)+' > 0
begin
print ''These key values exist in '+@db2+', but do not exist in '+@db1+': ''
set @SelectStr = ''select top ''+STR('+STR(@NumbToShow)+')+'' * from ##NotInDB1''
exec (@SelectStr)
end
if @NotEq = 1 and '+STR(@NumbToShow)+' > 0
begin
print ''Row(s) with these key values contain differences in non-key columns: ''
set @SelectStr = ''select top ''+STR('+STR(@NumbToShow)+')+'' * from ##NotEq''
exec (@SelectStr)
end
exec (''insert into #DiffTables values(''''[''+@Tab+'']'''')'')
end
else
begin
print CHAR(10)+''Data are identical''
exec ('' insert into #IdenticalTables values(''''[''+@Tab+'']'''')'')
end
if exists (select * from tempdb.dbo.sysobjects where name like ''##NotEq%'')
drop table ##NotEq
end
else '
set @SqlStrCompareNonUKeyTables = '
begin
exec (''insert into #NoPKTables values(''''[''+@Tab+'']'''')'')
set @PrintStr = '' Print ''
set @ExecStr = '' exec (''
set @SqlStr = ''''
Insert into ##CompareStr values(1,
''SELECT ''+
'' * INTO ##NotInDB2 FROM '+@db1+'.[''+@d1User+''].[''+@Tab+''] d1 WHERE not exists ''+CHAR(10)+
'' (SELECT * FROM '+@db2+'.[''+@d2User+''].[''+@Tab+''] d2 WHERE '')
set @StrInd = @StrInd + 1
Insert into ##CompareStr values(@StrInd,@AndStr+'')'')
set @i = 1
while @i <= @StrInd
begin
set @SqlStr = @SqlStr + '' declare @Str''+LTRIM(STR(@i))+'' varchar(8000) ''+
''select @Str''+LTRIM(STR(@i))+''=CompareStr FROM ##CompareStr WHERE ind = ''+STR(@i)
if @ExecStr <> '' exec (''
set @ExecStr = @ExecStr + ''+''
if @PrintStr <> '' Print ''
set @PrintStr = @PrintStr + ''+''
set @ExecStr = @ExecStr + ''@Str''+LTRIM(STR(@i))
set @PrintStr = @PrintStr + '' REPLACE(@Str''+LTRIM(STR(@i))+'','''' into ##NotInDB2'''','''''''') ''
set @i = @i + 1
end
set @ExecStr = @ExecStr + '') ''
set @ExecSqlStr = @SqlStr + @ExecStr
set @PrintSqlStr = @SqlStr +
'' Print CHAR(10)+''''To find rows that are in '+@db1+', but are not in '+@db2+':'''' ''+
@PrintStr
if '+STR(@VerboseLevel)+' = 1
exec (@PrintSqlStr)
exec (@ExecSqlStr)
if @@rowcount > 0
set @NotInDB2 = 1
delete from ##CompareStr where ind = 1
set @PrintStr = '' Print ''
set @ExecStr = '' exec (''
set @SqlStr = ''''
Insert into ##CompareStr values(1,
''SELECT ''+
'' * INTO ##NotInDB1 FROM '+@db2+'.[''+@d2User+''].[''+@Tab+''] d1 WHERE not exists ''+CHAR(10)+
'' (SELECT * FROM '+@db1+'.[''+@d1User+''].[''+@Tab+''] d2 WHERE '')
set @i = 1
while @i <= @StrInd
begin
set @SqlStr = @SqlStr + '' declare @Str''+LTRIM(STR(@i))+'' varchar(8000) ''+
''select @Str''+LTRIM(STR(@i))+''=CompareStr FROM ##CompareStr WHERE ind = ''+STR(@i)
if @ExecStr <> '' exec (''
set @ExecStr = @ExecStr + ''+''
if @PrintStr <> '' Print ''
set @PrintStr = @PrintStr + ''+''
set @ExecStr = @ExecStr + ''@Str''+LTRIM(STR(@i))
set @PrintStr = @PrintStr + '' REPLACE(@Str''+LTRIM(STR(@i))+'','''' into ##NotInDB1'''','''''''') ''
set @i = @i + 1
end
set @ExecStr = @ExecStr + '') ''
set @ExecSqlStr = @SqlStr + @ExecStr
set @PrintSqlStr = @SqlStr +
'' Print CHAR(10)+''''To find rows that are in '+@db2+', but are not in '+@db1+':'''' ''+
@PrintStr
if '+STR(@VerboseLevel)+' = 1
exec (@PrintSqlStr)
exec (@ExecSqlStr)
if @@rowcount > 0
set @NotInDB1 = 1
truncate table ##CompareStr
if @NotInDB1 = 1 or @NotInDB2 = 1
begin
print CHAR(10)+''Data are different''
if @NotInDB2 = 1 and '+STR(@NumbToShow)+' > 0
begin
print ''The row(s) exist in '+@db1+', but do not exist in '+@db2+': ''
set @SelectStr = ''select top ''+STR('+STR(@NumbToShow)+')+'' * from ##NotInDB2''
exec (@SelectStr)
end
if @NotInDB1 = 1 and '+STR(@NumbToShow)+' > 0
begin
print ''The row(s) exist in '+@db2+', but do not exist in '+@db1+': ''
set @SelectStr = ''select top ''+STR('+STR(@NumbToShow)+')+'' * from ##NotInDB1''
exec (@SelectStr)
end
exec (''insert into #DiffTables values(''''[''+@Tab+'']'''')'')
end
else
begin
print CHAR(10)+''Data are identical''
exec ('' insert into #IdenticalTables values(''''[''+@Tab+'']'''')'')
end
end
if exists (select * from tempdb.dbo.sysobjects where name like ''##NotInDB1%'')
drop table ##NotInDB1
if exists (select * from tempdb.dbo.sysobjects where name like ''##NotInDB2%'')
drop table ##NotInDB2
NextTab:
fetch next from TabCur into @Tab, @d1User, @d2User
end
deallocate TabCur
'
exec (@SqlStrGetListOfKeys1+@SqlStrGetListOfKeys2+@SqlStrGetListOfColumns+
@SqlStrCompareUKeyTables+@SqlStrCompareNonUKeyTables)
print ' '
SET NOCOUNT OFF
if (select count(*) from #NoPKTables) > 0
begin
select name as 'Table(s) without Unique key:' from #NoPKTables
end
if (select count(*) from #DiffTables) > 0
begin
select name as 'Table(s) with the same name & structure, but different data:' from #DiffTables
end
else
print CHAR(10)+'No tables with the same name & structure, but different data'+CHAR(10)
if (select count(*) from #IdenticalTables) > 0
begin
select name as 'Table(s) with the same name & structure and identical data:' from #IdenticalTables
end
if (select count(*) from #EmptyTables) > 0
begin
select name as 'Table(s) with the same name & structure and empty in the both databases:' from #EmptyTables
end
drop table #TabToCheck
drop table ##CompareStr
drop table #DiffTables
drop table #IdenticalTables
drop table #EmptyTables
drop table #NoPKTables
drop table #IndList1
drop table #IndList2
return
GO
14 March 2011
Assigning Log On As A Service rights - SQL SERVER SERVICE ACCOUNT PROBLEMS
|
8.7.1 |
10 March 2011
Login Script
* Run the Proc in master
USE [master]
GO
/****** Object: StoredProcedure [dbo].[PRADI] Script Date: 03/10/2011 12:10:43 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PRADI]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[PRADI]
GO
USE [master]
GO
/****** Object: StoredProcedure [dbo].[PRADI] Script Date: 03/10/2011 12:10:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[PRADI]
@LoginNamePattern sysname = NULL
AS
set nocount on
declare
@exec_stmt nvarchar(3550)
declare
@RetCode int
,@CountSkipPossUsers int
,@Int1 int
declare
@c10DBName sysname
,@c10DBStatus int
,@c10DBSID varbinary(85)
declare
@charMaxLenLoginName varchar(11)
,@charMaxLenDBName varchar(11)
,@charMaxLenUserName varchar(11)
,@charMaxLenLangName varchar(11)
declare
@DBOptLoading int --0x0020 32 "DoNotRecover"
,@DBOptPreRecovery int --0x0040 64
,@DBOptRecovering int --0x0080 128
,@DBOptSuspect int --0x0100 256 ("not recovered")
,@DBOptOffline int --0x0200 512
,@DBOptDBOUseOnly int --0x0800 2048
,@DBOptSingleUser int --0x1000 4096
CREATE TABLE #tb1_UA
(
LoginName sysname collate database_default NOT Null
,DBName sysname collate database_default NOT Null
,UserName sysname collate database_default NOT Null
,UserOrAlias char(8) collate database_default NOT Null
)
---------------- Initial data values -------------------
select
@RetCode = 0 -- 0=good ,1=bad
,@CountSkipPossUsers = 0
---------------- Only SA can run this -------------------
if (not (is_srvrolemember('securityadmin') = 1))
begin
raiserror(15247,-1,-1)
select @RetCode = 1
goto label_86return
end
---------------------- spt_values ----------------
-------- 'D'
select @DBOptLoading = number
from master.dbo.spt_values
where type = 'D'
and name = 'loading'
select @DBOptPreRecovery = number
from master.dbo.spt_values
where type = 'D'
and name = 'pre recovery'
select @DBOptRecovering = number
from master.dbo.spt_values
where type = 'D'
and name = 'recovering'
select @DBOptSuspect = number
from master.dbo.spt_values
where type = 'D'
and name = 'not recovered'
select @DBOptOffline = number
from master.dbo.spt_values
where type = 'D'
and name = 'offline'
select @DBOptDBOUseOnly = number
from master.dbo.spt_values
where type = 'D'
and name = 'dbo use only'
select @DBOptSingleUser = number
from master.dbo.spt_values
where type = 'D'
and name = 'single user'
--------------- Cursor, for DBNames -------------------
declare ms_crs_10_DB
Cursor local static For
select
name ,status ,sid
from
master.dbo.sysdatabases
OPEN ms_crs_10_DB
----------------- LOOP 10: thru Databases ------------------
--------------
WHILE (10 = 10)
begin --LOOP 10: thru Databases
FETCH
next
from
ms_crs_10_DB
into
@c10DBName
,@c10DBStatus
,@c10DBSID
IF (@@fetch_status <> 0)
begin
deallocate ms_crs_10_DB
BREAK
end
-------------------- Okay if we peek inside this DB now?
IF ( @c10DBStatus & @DBOptDBOUseOnly > 0
AND @c10DBSID <> suser_sid()
)
begin
select @CountSkipPossUsers = @CountSkipPossUsers + 1
CONTINUE
end
IF (@c10DBStatus & @DBOptSingleUser > 0)
begin
select @Int1 = count(*)
from sys.dm_exec_requests
where session_id <> @@spid
and database_id = db_id(@c10DBName)
IF (@Int1 > 0)
begin
select @CountSkipPossUsers = @CountSkipPossUsers + 1
CONTINUE
end
end
IF (@c10DBStatus &
(
@DBOptLoading
| @DBOptRecovering
| @DBOptSuspect
| @DBOptPreRecovery
)
> 0
)
begin
select @CountSkipPossUsers = @CountSkipPossUsers + 1
CONTINUE
end
IF (@c10DBStatus &
(
@DBOptOffline
)
> 0
)
begin
--select @CountSkipPossUsers = @CountSkipPossUsers + 1
CONTINUE
end
IF (has_dbaccess(@c10DBName) <> 1)
begin
raiserror(15622,-1,-1, @c10DBName)
CONTINUE
end
--------------------- Add the User info to holding table.
select @exec_stmt = '
INSERT #tb1_UA
(
DBName
,LoginName
,UserName
,UserOrAlias
)
select
N' + quotename(@c10DBName, '''') + '
,l.name
,u.name
,''User''
from
' + quotename(@c10DBName, '[') + '.sys.sysusers u
,sys.server_principals l
where
u.sid = l.sid' +
case
when @LoginNamePattern is null
then ''
else ' and ( l.name = N' + quotename(@LoginNamePattern , '''') + '
or l.name = N' + quotename(@LoginNamePattern , '''') + ')'
end
+
' UNION
select
N' + quotename(@c10DBName, '''') + '
,l.name
,u2.name
,''MemberOf''
from
' + quotename(@c10DBName, '[')+ '.sys.database_role_members m
,' + quotename(@c10DBName, '[')+ '.sys.database_principals u1
,' + quotename(@c10DBName, '[')+ '.sys.database_principals u2
,sys.server_principals l
where
u1.sid = l.sid
and m.member_principal_id = u1.principal_id
and m.role_principal_id = u2.principal_id' +
case
when @LoginNamePattern is null
then ''
else ' and ( l.name = N' + quotename(@LoginNamePattern , '''') + '
or l.name = N' + quotename(@LoginNamePattern , '''') + ')'
end
EXECUTE(@exec_stmt)
end --loop 10
------------ Optimize UA report column display widths -----------
select
@charMaxLenLoginName =
convert ( varchar
,isnull ( max(datalength(LoginName)) ,9)
)
,@charMaxLenDBName =
convert ( varchar
,isnull ( max(datalength(DBName)) ,6)
)
,@charMaxLenUserName =
convert ( varchar
,isnull ( max(datalength(UserName)) ,8)
)
from
#tb1_UA
------------ Print out the UserOrAlias report ------------
EXEC(
'
set nocount off
select
''LoginName'' = substring (LoginName ,1 ,'
+ @charMaxLenLoginName + ')
,''DBName'' = substring (DBName ,1 ,'
+ @charMaxLenDBName + ')
,''UserName'' = substring (UserName ,1 ,'
+ @charMaxLenUserName + ')
,UserOrAlias
from
#tb1_UA
order by
1 ,2 ,3
Set nocount on
'
)
----------------------- Finalization --------------------
label_86return:
IF (object_id('#tb1_UA') IS NOT Null)
DROP Table #tb1_UA
Return @RetCode
GO
08 March 2011
sql-server-performance-tuning
http://sqlserverpedia.com/blog/sql-server-performance-tuning/kevin-kline-tutorial-on-sql-server-query-tuning/
Thanks to Kevin
02 March 2011
Dynamic Management Views
Two types of dynamic management views:
- Server-scoped DMV: Stored in Master Database
- Database-scoped DMV: Specific to each database
Permission to Execute DMV [Security]
To query a server scoped DMV, the database user must have SELECT
privilege on VIEW SERVER STATE
and for database scoped DMV, the user must have SELECT
privilege on VIEW DATABASE STATE
.
- GRANT VIEW SERVER STATE to
- GRANT VIEW DATABASE STATE to
If you want to deny a user permission to query certain DMVs, you can use the DENY
command to restrict access to a specific DMV.
Getting Started
All the DMVs exits in SYS schema and their names start with DM_
. So when you need to query a DMV, you should prefix the view name with SYS
. As an example, if you need to see the total physical memory of the SQL Server machine; then execute the below TSQL command:
SELECT
(Physical_memory_in_bytes/1024.0)/1024.0 AS Physical_memory_in_Mb
FROM
sys.dm_os_sys_info
In this article, I will be explaining some of the DMVs which can be used frequently to understand the current behavior of SQL Server:
- SQL Server related [Hardware Resources] DMV
- Database related DMV
- Index related DMV
- Execution related DMV
1. SQL Server Related DMV
This section details the DMVs associated with SQL Server system. SQL DMV is responsible to manage server level resources specific to a SQL Server instance.
This section covers DMVs related to OS, Disk and Memory.
a. sys.dm_os_sys_info
This view returns the information about the SQL Server machine, available resources and the resource consumption.
This view returns information like the following:
- CPU Count: Number of logical CPUs in the server
- Hyperthread-ratio: Ratio of logical and physical CPUs
Physical_memory_in_bytes
: Amount of physical memory availableVirtual_memory_in_bytes
: Amount of virtual memory availableBpool_commited
: Committed physical memory in buffer poolOS_Priority_class
: Priority class for SQL Server processMax_workers_thread
: Maximum number of workers which can be created
b. sys.dm_os_hosts
This view returns all the hosts registered with SQL Server 2005. This view also provides the resources used by each host.
Name
: Name of the host registeredType
: Type of hosted component [SQL Native Interface/OLE DB/MSDART]Active_tasks_count
: Number active tasks host placedActive_ios_count
: I/O requests from host waiting
c. sys.dm_os_schedulers
Sys.dm_os_schedulers
view will help you identify if there is any CPU bottleneck in the SQL Server machine. The number of runnable tasks is generally a nonzero value; a nonzero value indicates that tasks have to wait for their time slice to run. If the runnable task counts show high values, then there is a symptom of CPU bottleneck.
SELECT
scheduler_id,current_tasks_count,runnable_tasks_count
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255
The above query will list all the available schedulers in the SQL Server machine and the number of runnable tasks for each scheduler.
d. sys.dm_io_pending_io_requests
This dynamic view will return the I/O requests pending in SQL Server side. It gives you information like:
Io_type
: Type of pending I/O requestIo_pending
: Indicates whether the I/O request is pending or has been completed by WindowsScheduler_address
: Scheduler on which this I/O request was issued
e. sys.dm_io_virtual_file_stats
This view returns I/O statistics for data and log files [MDF and LDF file]. This view is one of the commonly used views and will help you to identify I/O file level. This will return information like:
Sample_ms
: Number of milliseconds since the instance of SQL Server has startedNum_of_reads
: Number of reads issued on the fileNum_of_bytes_read
: Total number of bytes read on this fileIo_stall_read_ms
: Total time, in milliseconds, that the users waited for reads issued on the fileNum_of_writes
: Number of writes made on this fileNum_of_bytes_written
: Total number of bytes written to the fileIo_stall_write_ms
: Total time, in milliseconds, that users waited for writes to be completed on the fileIo_stall
: Total time, in milliseconds, that users waited for I/O to be completedSize_on_disk_bytes
: Number of bytes used on the disk for this file
f. sys.dm_os_memory_clerks
This DMV will help how much memory SQL Server has allocated through AWE.
SELECT
SUM(awe_allocated_kb) / 1024 as [AWE allocated, Mb]
FROM sys.dm_os_memory_clerks
The same DMV can be used to get the memory consumption by internal components of SQL Server 2005.
SELECT TOP 10 type,
SUM(single_pages_kb) as [SPA Mem, Kb]
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY SUM(single_pages_kb) DESC
g. sys.dm_os_ring_buffers
This DMV uses RING_BUFFER_RESOURCE_MONITOR
and gives information from resource monitor notifications to identify memory state changes. Internally, SQL Server has a framework that monitors different memory pressures. When the memory state changes, the resource monitor task generates a notification. This notification is used internally by the components to adjust their memory usage according to the memory state.
SELECT
Record FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'
The output of the above query will be in XML format. The output will help you in detecting any low memory notification.
RING_BUFFER_OOM
: Ring buffer oom contains records indicating server out-of-memory conditions.
SELECT
record FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_OOM'
2. Database Related DMV
This section details the DMVs associated with SQL Server Databases. These DMVs will help to identify database space usages, partition usages, session information usages, etc...
a. sys.dm_db_file_space_usage
This DMV provides the space usage information of TEMPDB
database.
b. sys.dm_db_session_space_usage
This DMV provides the number of pages allocated and de-allocated by each session for the database
c. sys.dm_db_partition_stats
This DMV provides page and row-count information for every partition in the current database.
The below query shows all counts for all partitions of all indexes and heaps in the MSDB database:
USE MSDB;
GO
SELECT * FROM sys.dm_db_partition_stats;
The following query shows all counts for all partitions of Backup set table and its indexes
USE MSDB
GO
SELECT * FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('backupset');
d. sys.dm_os_performance_counters
Returns the SQL Server / Database related counters maintained by the server.
The below sample query uses the dm_os_performance_counters
DMV to get the Log file usage for all databases in KB.
SELECT instance_name
,cntr_value 'Log File(s) Used Size (KB)'
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Log File(s) Used Size (KB)'
3. INDEX Related DMV
This section details the DMVs associated with SQL Server Databases. These DMVs will help to identify database space usages, Partition usages, Session information usages, etc.
a. sys.dm_db_index_usage_stats
This DMV is used to get useful information about the index usage for all objects in all databases. This also shows the amount of seeks and scan for each index.
SELECT object_id, index_id, user_seeks, user_scans, user_lookups
FROM sys.dm_db_index_usage_stats
ORDER BY object_id, index_id
All indexes which have not been used so far in as database can be identified using the below Query:
SELECT object_name(i.object_id),
i.name,
s.user_updates,
s.user_seeks,
s.user_scans,
s.user_lookups
from sys.indexes i
left join sys.dm_db_index_usage_stats s
on s.object_id = i.object_id and
i.index_id = s.index_id and s.database_id = 5
where objectproperty(i.object_id, 'IsIndexable') = 1 and
s.index_id is null or
(s.user_updates > 0 and s.user_seeks = 0
and s.user_scans = 0 and s.user_lookups = 0)
order by object_name(i.object_id)
Replace the Database_id
with the database you are looking at.
4. Execution Related DMV
Execution related DMVs will provide information regarding sessions, connections, and various requests which are coming into the SQL Server.
a. sys.dm_exec_sessions
This DMV will give information on each session connected to SQL Server. This DMV is similar to running sp_who2
or querying Master..sysprocesses
table.
SELECT
session_id,login_name,
last_request_end_time,cpu_time
FROM sys.dm_exec_sessions
WHERE session_id >= 51 – All user Sessions
b. sys.dm_exec_connections
This DMV shows all the connection to SQL Server. The below query uses sys.dm_exec_connections
DMV to get connection information. This view returns one row for each user connection (Sessionid > =51
).
SELECT
connection_id,
session_id,client_net_address,
auth_scheme
FROM sys.dm_exec_connections
c. sys.dm_exec_requests
This DMV will give details on what each connection is actually performing in SQL Server.
SELECT
session_id,status,
command,sql_handle,database_id
FROM sys.dm_exec_requests
WHERE session_id >= 51
d. sys.dm_exec_sql_text
This dynamic management function returns the text of a SQL statement given a SQL handle.
SELECT
st.text
FROM
sys.dm_exec_requests r
CROSS APPLY
sys.dm_exec_sql_text(sql_handle) AS st
WHERE r.session_id = 51
Conclusion
Dynamic Management views (DMV) and Dynamic Management Functions (DMF) in SQL Server 2005 give a transparent view of what is going on inside various areas of SQL Server. By using them, we will be able to query the system for information about its current state in a much more effective manner and provide solutions much faster. DMVs can be used to performance tune and for troubleshooting server and queries. This article has shown an overview of what they are and how we can use them.
SQL Server 2008 Indexing Best Practices
The best index
- There's a best index for every query
- You don't want to do that for every query, though
- You would end up with too many indexes
- You need to select a good set of base indexes
Find a balance
- You need to find a balance between no indexes and one ideal index on every query
- Do not put a non-clustered index on every columns :-)
- You can find (mathematically) if a non-clustered index will help or not
- You can sometimes replace a narrow non-clustered index with a wider one that is more useful
Strategies
- Create constraints for primary keys and alternate/candidate keys
- Add indexes to foreign key constraints. It can help some joins
- Capture workloads and use the database tuning advisor (DTA)
- Add indexes to help with specific queries using joins, aggregations, etc.
- Missing index DMVs query http://blogs.msdn.com/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx
- These are only strategies you should consider, not absolutes.
- Also, you need to maintain the indexes over time. Things change over time.
OLTP x OLAP
- OLTP tables typically uses fewer indexes than OLAP tables
- OLTP tables can make good use of indexes
- However, there are certain indexes on OLTP that will be bad
Clustered index keys
- Clustered Index keys: Unique, narrow, static, ever increasing
- Examples: Identity / Date,Identity / GUID (with NEWSEQUENTIALID function)
- Add something like identity to avoid ending up with uniquefiers
- This means that you will have a lot less management over time
- If you can have a really good clustering key, avoid heaps.
Identity
- Identity is naturally unique, static, narrow, hot spot for inserts
- Being a hot spot in this case is actually a good thing
- It minimizes cache requirements, naturally needs less maintenance
- Could have contention in GAM/SGAM pages if you have lots of tables with identity
- Can overcome with multiple files, see http://support.microsoft.com/kb/328551
Lookups vs. Table Scans
- Lookups. Physical vs. Logical I/Os
- Potential for physical I/Os is in the leaf level (non-leaf are usually cached)
- SQL knows how many pages are on the leaf level, how many rows per page
- SQL needs to make a decision on Lookup x Table Scan
- If it's in the WHERE clause, it doesn't mean an index on that will be used
- Estimating query cost: tipping point for a query to go TS over a lookup
- Won't spoil the results, but it is smaller than most would think...
- Details at http://sqlskills.com/BLOGS/KIMBERLY/post/Tipping-Point-Queries-More-Questions-To-Really-Test-You!.aspx
Hints
- You can force SQL to do Lookup x TS, but SQL usually does a good estimate
- There are cases when the statistics for the table are not up to date
- People end up using more hints (like INDEX, FORCESEEK) or plan guides than they should
- If you use a hint and you're wrong, SQL will follow your hint anyway
Coverage
- The power of covering. Leaf level of the non-clustered index has all the rows
- People use indexes with "LN,FN,MI including SSN" and "LN, FN, MI including PHONE" and "LN with SSN", etc..
- You can cover the same with a single index on "LN, FN, MI including SSN, PHONE"
- Order of included columns do not matter. Just the order of the keys
- Could this be happening because of the missing index message in query results?
- Multiple developers not in sync can be a common source if this kind of thing.
Included columns
- INCLUDE option lets you separate what goes in the leaf and non-leaf of index
- There is no limit to what you can INCLUDE, but be careful with the duplication
- You can create a non-clustered, covering, seekable index just for a query
- For instance, you could create a non-clustered index on the SSN key and a few INCLUDED columns just to help a specific set of queries without hitting data pages at all
- Full scan on a small non-clustered index could even beat seeks in certain cases.
Filtered Indexes
- SQL Server 2008 includes the ability to do Filtered Indexes
- For instance, filter for "status=1" to index only active items
- For instance, filter for orderdate in last year for certain reports
- Now that the index is much smaller, you can now maybe consider including more columns
- This will give you more coverage without using a lot of space
- Filtered indexes statistics are usually more accurate, if they cover fewer rows
- This is not a substitute for partitions. Don't create filtered indexes if all sets are useful
- Kimberly's SP_HELPINDEX2 shows included columns and filtered indexes
http://www.sqlskills.com/blogs/Kimberly/post/Updates-(fixes)-to-sp_helpindex2.aspx
Sparse Columns
- Combine with sparse columns for indexes on subsets with only certain columns INCLUDED
- Beware: Create indexes on tables with sparse columns might materialize sparse columns.
Conclusion
- The best case: index covers all you need (key+included), nothing you don't and is seekable
- Indexes do use more space and come at a cost for inserts, updates, maintenance
- You don't have to do this for every query or every table
- Put effort in the top few queries and you'll fix most of your issues
- There are usually a few situations that cause you most of the grief
- Look at that stored procedure that is executed thousand times per hour
- You can overdo it. Be careful
22 February 2011
Max Worker Threads Configuration
In 2005 the default value for this setting has changed from 255 (SQL2k) to 0 (dynamic in SQL2k5). According to various sources, the following table shows the automatically configured number of Max Worker Threads for SQL Servers (2005) with different numbers of CPUs when the SQL Server database engine is left to dynamically set this value:
CPUs | Auto Configured Max Worker Threads (32 bit Server) | Auto Configured Max Worker Threads (64 bit Server) |
<= 4 CPUs | 256 | 512 |
8 CPUs | 288 | 576 |
16 CPUs | 352 | 704 |
32 CPUs | 480 | 960 |
The formula to determine the max worker processes is as follows:
For x86 systems where total number of logical processors <=4
# max worker threads = 256
Otherwise:
# max worker threads = 256 + ((# Procs – 4) * 8)
For x64 systems where total number of logical processors <= 4
# max worker threads = 512
Otherwise
# max worker threads = 512 + ((# Procs – 4) * 16)
On an 8 processor x86 machine (could be 4-proc HT or 4-proc dual core machine, therefore shows 8 logical procs), max worker threads will be configured to 288 (256 + (4 * 8))
You can find out how many threads have been configured by looking at the max_workers_count column in the sys.dm_os_sys_info DMV.
Note:
SQL and indeed Windows has NO method of identifying the difference between physical and logical processors – i.e. multi-core and hyper-threaded processors appear to windows (and therefore) SQL as processors. As such 4 x Dual core would appear as 8 logical processors – and max worker threads would be sized on this basis regardless of whether these are 8 x physical, 4 x dual core or 4 x HT.
-
ERROR: report server remote procedure call (RPC) end point. Verify that the Report Server Windows service is running, and then retry the o...
-
Error messages: · The process could not execute 'sp_repldone/sp_replcounters' on ' SCKNLSSI '. (Source:...
-
Error Message: Replication-Replication Distribution Subsystem: agent DBA\DBA-EPDW-EPDW-DB6C\DB6C-5 failed. The subscription(S) have been...