April 12, 2016 at 8:02 am
Hi,
I am setting up a housekeeping exercise in a Data Warehouse environment and I have many reference tables set up as views from many separate systems.
We have one external system which has used the same internal ref for different descriptions with the same ref table ( yes I know, dont ask !! )
I would like to set up a query that hits ALL ref tables in a database and runs the the following for each to return any duplicate values that may exist within a single table.
How would I go about this without having to union all tables (158 of them but will grow) or creating a cursor (not too familiar with cursors but I know there are more efficient ways???)
I need to run the following check against the views in the select below.
Any help greatly received.
---Single Table Check
SELECT ConsultationSourceCodeId AS Code,
ConsultationSourceOriginalTerm AS LocalDescription
FROM CareRecord.Consultation
GROUP BY ConsultationSourceCodeId, ConsultationSourceOriginalTerm
HAVING (COUNT(*) > 1)
ORDER BY code
-- List of tables to checked
SELECT name
FROM sys.tables
WHERE (name LIKE '%vw_LocalDomain%')
GROUP BY name
ORDER BY name
April 12, 2016 at 8:44 am
This is a great place to use a cursor. You need to go table by table to get the desired results.
Here's an example, just change the query text and you should be all set.
DECLARE @SQL nvarchar(max);
-- List of tables to checked
DECLARE cTables CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY
FOR
SELECT N'SELECT TOP 1 * FROM ' + QUOTENAME( name) + N';'
FROM sys.tables
--WHERE (name LIKE '%vw_LocalDomain%')
ORDER BY name;
OPEN cTables;
FETCH NEXT FROM cTables INTO @SQL;
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC( @SQL);
FETCH NEXT FROM cTables INTO @SQL;
END;
CLOSE cTables;
DEALLOCATE cTables;
April 13, 2016 at 2:52 am
Many Thanks for your reply.
I have got it working but now i need to export the results into a table so i can monitor the results daily via an SQL job.
It seems to run with no errors but is not giving me the any results in the table PS_Test.
Can you see where I'm going wrong.
Thanks
CREATE TABLE PS_Test
(
cnt varchar(255),
code varchar(255),
tn varchar(255),
);
DECLARE @SQL nvarchar(max);
DECLARE @tbl TABLE (ID VARCHAR (255))
-- List of tables to checked
DECLARE cTables CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY
FOR
SELECT N'
select count (*) as cnt, code,''' + QUOTENAME( name) + ''' as tn
FROM' + QUOTENAME( name) +
'group by code
having count (*) > 1 ' + N';'
FROM sys.views
WHERE (name LIKE '%vw_LocalDomain%') AND name NOT LIKE '%RIO_SPONT%'
ORDER BY name;
OPEN cTables;
FETCH NEXT FROM cTables INTO @SQL;
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC( @SQL);
INSERT @tbl SELECT @SQL
FETCH NEXT FROM cTables INTO @SQL;
END;
CLOSE cTables;
DEALLOCATE cTables;
GO
SELECT * FROM dbo.PS_Test
DROP TABLE dbo.PS_Test
April 13, 2016 at 3:00 am
Figured it out 🙂
Thanks again. P
CREATE TABLE PS_Test
(
cnt varchar(255),
code varchar(255),
tn varchar(255),
);
--SELECT * FROM dbo.PS_Test
--GO
DECLARE @SQL nvarchar(max);
DECLARE @tbl TABLE (ID VARCHAR (255))
-- List of tables to checked
DECLARE cTables CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY
FOR
--SELECT N'SELECT TOP 1 * FROM ' + QUOTENAME( name) + N';' -- insert select in here
SELECT N'
insert into PS_Test (cnt, code, tn)
select count (*) as cnt, code,''' + QUOTENAME( name) + ''' as tn
FROM' + QUOTENAME( name) +
'group by code
having count (*) > 1 ' + N';'
FROM sys.views
WHERE (name LIKE '%vw_LocalDomain%') AND name NOT LIKE '%RIO_SPONT%'
ORDER BY name;
OPEN cTables;
FETCH NEXT FROM cTables INTO @SQL;
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC( @SQL);
--INSERT @tbl SELECT @SQL
FETCH NEXT FROM cTables INTO @SQL;
END;
CLOSE cTables;
DEALLOCATE cTables;
GO
SELECT * FROM dbo.PS_Test
DROP TABLE dbo.PS_Test
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply