Capa de trabalho
Por: Emanuel Barbosa Soares • 25/8/2015 • Trabalho acadêmico • 389 Palavras (2 Páginas) • 310 Visualizações
SET ANSI_WARNINGS OFF
DECLARE @outb nvarchar(50),
@utbt int,
@cnt int,
@cols int,
@query nvarchar(2000)
DECLARE outb CURSOR FOR SELECT tablename,objecttype FROM outb
OPEN outb
FETCH NEXT FROM outb INTO @outb,@utbt
WHILE @@fetch_status=0
BEGIN
IF @utbt=0 -- No Object
BEGIN
SELECT @cols=count(t0.name) FROM syscolumns T0 INNER JOIN sysobjects T1 on T0.id=T1.id
WHERE T1.name='@'+@outb AND( T0.name='Code' or T0.name='Name')
If @cols=2
BEGIN
SET @query='SELECT @outparm=count(convert(nvarchar(20),[code])+''_''+convert(nvarchar(20),[name])) FROM [@'+ @outb +'] GROUP BY convert(nvarchar(20),[code])+''_''+convert(nvarchar(20),[name]) HAVING count(convert(nvarchar(20),code)+''_''+convert(nvarchar(20),[name]))>1'
exec SP_executesql @query,N'@outparm int output', @cnt output
IF @cnt>0
BEGIN
SET @query='SELECT convert(nvarchar(20),[code])+''_''+convert(nvarchar(20),[name]) as [T_@'+ @outb +'] FROM [@'+ @outb +'] GROUP BY convert(nvarchar(20),[code])+''_''+convert(nvarchar(20),[name]) HAVING count(convert(nvarchar(20),code)+''_''+convert(nvarchar(20),[name]))>1'
exec SP_executesql @query
PRINT '@' + @outb + ': identity violation'
END
END
ELSE
BEGIN
PRINT 'Missing columns ("Code" or "Name") in table @'+@outb
END
END
IF @utbt=1 -- Master Data
BEGIN
SELECT @cols=count(t0.name) FROM syscolumns T0 INNER JOIN sysobjects T1 on T0.id=T1.id
WHERE T1.name='@'+@outb AND( T0.name='Code' or T0.name='Docentry')
If @cols=2
BEGIN
SET @query='SELECT @outparm=count(convert(nvarchar(20),[code])+''_''+convert(nvarchar(20),[Docentry])) FROM [@'+ @outb +'] GROUP BY convert(nvarchar(20),[code])+''_''+convert(nvarchar(20),[Docentry]) HAVING count(convert(nvarchar(20),[code])+''_''+convert(nvarchar(20),[Docentry])) >1'
exec SP_executesql @query,N'@outparm int output', @cnt output
IF @cnt>0
BEGIN
SET @query='SELECT convert(nvarchar(20),[code])+''_''+convert(nvarchar(20),[Docentry]) as [T_@'+ @outb +'] FROM [@'+ @outb +'] GROUP BY convert(nvarchar(20),[code])+''_''+convert(nvarchar(20),[Docentry]) HAVING count(convert(nvarchar(20),[code])+''_''+convert(nvarchar(20),[Docentry])) >1'
exec SP_executesql @query
PRINT '@' + @outb + ': identity violation'
END
END
ELSE
BEGIN
PRINT 'Missing columns ("Code" or "DocEntry") in table @'+@outb
END
END
IF @utbt=2 -- Master Data Rows
BEGIN
SELECT @cols=count(t0.name) FROM syscolumns T0 INNER JOIN sysobjects T1 on T0.id=T1.id
WHERE T1.name='@'+@outb AND( T0.name='Code' or T0.name='LineID')
If @cols=2
BEGIN
SET @query='SELECT @outparm=count(convert(nvarchar(20),[code])+''_''+convert(nvarchar(20),[LineID])) FROM [@'+ @outb +'] GROUP BY convert(nvarchar(20),[code])+''_''+convert(nvarchar(20),[LineID]) HAVING count(convert(nvarchar(20),[code])+''_''+convert(nvarchar(20),[LineID])) >1'
exec SP_executesql @query,N'@outparm int output', @cnt output
IF @cnt>0
BEGIN
SET @query='SELECT convert(nvarchar(20),[code])+''_''+convert(nvarchar(20),[LineID]) as [T_@'+ @outb +'] FROM [@'+ @outb +'] GROUP BY convert(nvarchar(20),[code])+''_''+convert(nvarchar(20),[LineID]) HAVING count(convert(nvarchar(20),[code])+''_''+convert(nvarchar(20),[LineID])) >1'
exec SP_executesql @query
PRINT '@' + @outb + ': identity violation'
END
END
ELSE
BEGIN
...