September 19, 2018 at 6:40 am
Hi everyone,
I was trying to make a query that gives me all the row where Key's relationship isn't right.
My SSMS crashes before the end of the query, any tips?
DECLARE @Curseur CURSOR
DECLARE @requeteCheck nvarchar(MAX)
DECLARE @PKChamp nvarchar(100)
DECLARE @FKChamp nvarchar(100)
DECLARE @PKTable nvarchar(100)
DECLARE @FKTable nvarchar(100)
SET @Curseur =
CURSOR FOR SELECT
c2.name AS PK,
c1.name AS FK,
(s1.name + '.' + t1.name) AS Table_FK,
(s2.name + '.' + t2.name) AS Table_PK
FROM sysforeignkeys fk
INNER JOIN sysobjects o1
ON fk.fkeyid = o1.id
INNER JOIN sysobjects o2
ON fk.rkeyid = o2.id
INNER JOIN syscolumns c1
ON c1.id = o1.id AND c1.colid = fk.fkey
INNER JOIN syscolumns c2
ON c2.id = o2.id AND c2.colid = fk.rkey
INNER JOIN sysobjects s
ON fk.constid = s.id
INNER JOIN sys.foreign_keys f1
ON f1.name = s.name
INNER JOIN sys.tables t1
ON t1.object_id = f1.parent_object_id
INNER JOIN sys.tables t2
ON t2.object_id = f1.referenced_object_id
INNER JOIN sys.schemas s1
ON s1.schema_id = t1.schema_id
INNER JOIN sys.schemas s2 ON s2.schema_id = t2.schema_id
WHERE c2.name <> 'DomaineId'
ORDER BY o2.name
OPEN @Curseur
FETCH FROM @Curseur INTO @PKChamp, @FKChamp, @FKTable, @PKTable
WHILE @@FETCH_STATUS = 0
BEGIN
SET @requeteCheck = 'SELECT * FROM ' + @FKTable + ' WHERE ' + @FKChamp + ' NOT IN (SELECT ' + @PKChamp + ' FROM ' + @PKTable + ') ';
EXECUTE sp_executesql @requeteCheck
FETCH FROM @Curseur INTO @PKChamp, @FKChamp, @FKTable, @PKTable
END
Close @Curseur
DEALLOCATE @Curseur
September 19, 2018 at 8:45 am
mbichari - Wednesday, September 19, 2018 6:40 AMHi everyone,I was trying to make a query that gives me all the row where Key's relationship isn't right.
My SSMS crashes before the end of the query, any tips?
DECLARE @Curseur CURSOR
DECLARE @requeteCheck nvarchar(MAX)
DECLARE @PKChamp nvarchar(100)
DECLARE @FKChamp nvarchar(100)
DECLARE @PKTable nvarchar(100)
DECLARE @FKTable nvarchar(100)SET @Curseur =
CURSOR FOR SELECT
c2.name AS PK,
c1.name AS FK,
(s1.name + '.' + t1.name) AS Table_FK,
(s2.name + '.' + t2.name) AS Table_PK
FROM sysforeignkeys fk
INNER JOIN sysobjects o1
ON fk.fkeyid = o1.id
INNER JOIN sysobjects o2
ON fk.rkeyid = o2.id
INNER JOIN syscolumns c1
ON c1.id = o1.id AND c1.colid = fk.fkey
INNER JOIN syscolumns c2
ON c2.id = o2.id AND c2.colid = fk.rkey
INNER JOIN sysobjects s
ON fk.constid = s.id
INNER JOIN sys.foreign_keys f1
ON f1.name = s.name
INNER JOIN sys.tables t1
ON t1.object_id = f1.parent_object_id
INNER JOIN sys.tables t2
ON t2.object_id = f1.referenced_object_id
INNER JOIN sys.schemas s1
ON s1.schema_id = t1.schema_id
INNER JOIN sys.schemas s2 ON s2.schema_id = t2.schema_id
WHERE c2.name <> 'DomaineId'
ORDER BY o2.nameOPEN @Curseur
FETCH FROM @Curseur INTO @PKChamp, @FKChamp, @FKTable, @PKTable
WHILE @@FETCH_STATUS = 0
BEGIN
SET @requeteCheck = 'SELECT * FROM ' + @FKTable + ' WHERE ' + @FKChamp + ' NOT IN (SELECT ' + @PKChamp + ' FROM ' + @PKTable + ') ';
EXECUTE sp_executesql @requeteCheck
FETCH FROM @Curseur INTO @PKChamp, @FKChamp, @FKTable, @PKTable
ENDClose @Curseur
DEALLOCATE @Curseur
Start by adding QUOTENAME function to add quotes around the table and column names.
😎
Can you post the actual error if there is any? Or does SSMS just die? What versions of SQL Server and SSMS are you using?
September 19, 2018 at 8:51 am
Hi,
Thanks for the reply. Actually, SSMS just die. I use SQL Server 2008R2 and SSMS v17.7 (14.0.17254.0)
September 19, 2018 at 9:17 am
mbichari - Wednesday, September 19, 2018 8:51 AMHi,Thanks for the reply. Actually, SSMS just die. I use SQL Server 2008R2 and SSMS v17.7 (14.0.17254.0)
At the beginning of the script, add a variableDECLARE @MSG NVARCHAR(4000) = N'';
Then, in the cursor loop, add
-- CONCAT THE VARIABLES INTO THE @MSG
RAISERROR(@MSG,0,0) WITH NOWAIT;
This will tell you where SSMS scuttles (the talk like a pirate day is today)
😎
September 19, 2018 at 9:17 am
Probably a memory issue. How many tables are you querying with this code?
September 19, 2018 at 2:29 pm
mbichari - Wednesday, September 19, 2018 8:51 AMHi,Thanks for the reply. Actually, SSMS just die. I use SQL Server 2008R2 and SSMS v17.7 (14.0.17254.0)
SSMS is up to either 17.8 or 17.9 if I recall correctly. Just updating to the current version may help.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 19, 2018 at 2:49 pm
SSMS could still fail depending on how many tables are being queried and data returned. I have had that happen to me on a few occasions due to too many tables returning data back to data grids.
And the latest version is 17.9, as i just installed it myself.
September 20, 2018 at 1:43 am
I installed the latest version but the result still the same.
The select statement actually returns 1333 rows. I observed SSMS crash only when I'm on the tab "results", not when I'm on the tab "messages"
September 20, 2018 at 9:08 am
Again, how many tables are being queried by one run of the script?
September 20, 2018 at 9:57 am
There's 1333 tables queried by the script.
The database has 895 tables, tables are queried multiples time because I check every FK.
September 20, 2018 at 10:02 am
mbichari - Thursday, September 20, 2018 9:57 AMThere's 1333 tables queried by the script.The database has 895 tables, tables are queried multiples time because I check every FK.
That could be the problem. Instead of outputting the data for all the tables to SSMS in grid format, trying putting the data into tables and query those tables one at a time. Or, send the results to a file and view the data there with an editor like UltraEdit or Notepad++.
September 21, 2018 at 4:27 am
Have you tried using DBCC CHECKCONSTRAINTS?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply