none
forigen key instance list wrong RRS feed

  • שאלה

  • הכל התחיל כשניסיתי למחוק שורה מטבלה שהיתה מקושרת לכמה טבלאות בforigen key  
     
    רציתי לראות איזה עמודות בטבלאות שונות מקושרות לעמודה של טבלת האב
    וידנית למחוק את הנתונים   

    רציתי ליצור view של טבלת forigen key על כל ה instance
    כדי שאדע איזה עמודה מסוימת בטבלה מסויימת מקושרת לעמודה שבטבלה מסויימת

    אם אני נכנס לdb  יחיד ואני מריץ בו את הקוד הבא 
    הוא מחזיר לי נתונים הגיוניים 
    SELECT 
      f.name [ForeignKey] 
    ,  OBJECT_NAME (f.referenced_object_id) [ReferenceTableName] 
    ,  COL_NAME(fc.referenced_object_id, fc.referenced_column_id) ReferenceColumnName
    , OBJECT_NAME(f.parent_object_id) [TableName] 
    , COL_NAME(fc.parent_object_id,fc.parent_column_id) [ColumnName] 
    FROM 
    sys.foreign_keys AS f
    INNER JOIN 
    sys.foreign_key_columns AS fc 
    ON f.OBJECT_ID = fc.constraint_object_id



    אבל כשאני מריץ את הסקריפט הבא עם פקודת sp_msforeachdb כדי לקבל את כל ה forigen key שנמצאים בinstance
    אני מקבל את כל הנתונים בבלאגן גדול מאוד וללא כל הקשרים שאני מצפה למצוא 

    IF (not EXISTS (SELECT * 
                     FROM INFORMATION_SCHEMA.tables 
                     WHERE TABLE_NAME = 'FKeyInstance'))
     BEGIN
     
    CREATE TABLE FKeyInstance
            (
                            a nvarchar(100),
                            b nvarchar(100),
    						c nvarchar(100),
    						d nvarchar(100),
    						e nvarchar(100),
    						f nvarchar(100),
                    )
    
    
                    INSERT        FKeyInstance
    EXEC        ('sp_msforeachdb ''SELECT "?",
      f.name [ForeignKey] 
    ,  OBJECT_NAME (f.referenced_object_id) [ReferenceTableName] 
    ,  COL_NAME(fc.referenced_object_id, fc.referenced_column_id) ReferenceColumnName
    , OBJECT_NAME(f.parent_object_id) [TableName] 
    , COL_NAME(fc.parent_object_id,fc.parent_column_id) [ColumnName] 
    FROM 
    sys.foreign_keys AS f
    INNER JOIN 
    sys.foreign_key_columns AS fc 
    ON f.OBJECT_ID = fc.constraint_object_id  ''')
    
    
     END
     select * from FKeyInstance 




    האם מישהו מזהה איזשהי טעות שאני עושה???

    תודה מראש

     
    • נערך על-ידי Netanel F יום רביעי 10 דצמבר 2014 15:19
    יום רביעי 10 דצמבר 2014 15:17

תשובות

  • ערב טוב נטע

    אני רואה יותר מדי נקודות בעייתית בקוד שלך. תבדקי אם זה מה שאת מחפשת:

    use master
    GO
    
    create database D
    GO
    
    use D
    GO
    
    create table T (id int primary key)
    GO
    
    create table T1 (id int primary key, FK int FOREIGN KEY (id) REFERENCES  T (id) ON DELETE CASCADE ON UPDATE CASCADE)
    GO
    
    SELECT 
    	f.name [ForeignKey] 
    	, OBJECT_NAME (f.referenced_object_id)								[ReferenceTableName] 
    	, COL_NAME    (fc.referenced_object_id, fc.referenced_column_id)	[ReferenceColumnName]
    	, OBJECT_NAME (f.parent_object_id)									[TableName] 
    	, COL_NAME    (fc.parent_object_id,fc.parent_column_id)				[ColumnName] 
    FROM  sys.foreign_keys AS f
    INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
    
    -- Your query return only 4 columns and do not fit your table FKeyInstance structure!
    IF OBJECT_ID('FKeyInstance', 'U') IS NOT NULL DROP table FKeyInstance
    CREATE TABLE FKeyInstance (
    	[ForeignKey]			nvarchar(4000),
    	[ReferenceTableName]	nvarchar(4000),
    	[ReferenceColumnName]	nvarchar(4000),
    	[TableName]				nvarchar(4000),
    	[ColumnName]			nvarchar(4000)
    )
    
    EXEC sp_MSforeachdb @command1='
    	use ?; 
    	INSERT D.dbo.FKeyInstance -- Using tree parts name to insert the result to specific tabe in specific database
    	SELECT 
    		f.name [ForeignKey] 
    		,  OBJECT_NAME (f.referenced_object_id) [ReferenceTableName] 
    		,  COL_NAME(fc.referenced_object_id, fc.referenced_column_id) ReferenceColumnName
    		, OBJECT_NAME(f.parent_object_id) [TableName] 
    		, COL_NAME(fc.parent_object_id,fc.parent_column_id) [ColumnName] 
    	FROM  sys.foreign_keys AS f
    	INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
    '
    GO
    
    select * from D.dbo.FKeyInstance
    GO



    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]

    • סומן כתשובה על-ידי Netanel F יום חמישי 11 דצמבר 2014 11:56
    יום רביעי 10 דצמבר 2014 16:52
    מנחה דיון

כל התגובות

  • ערב טוב נטע

    אני רואה יותר מדי נקודות בעייתית בקוד שלך. תבדקי אם זה מה שאת מחפשת:

    use master
    GO
    
    create database D
    GO
    
    use D
    GO
    
    create table T (id int primary key)
    GO
    
    create table T1 (id int primary key, FK int FOREIGN KEY (id) REFERENCES  T (id) ON DELETE CASCADE ON UPDATE CASCADE)
    GO
    
    SELECT 
    	f.name [ForeignKey] 
    	, OBJECT_NAME (f.referenced_object_id)								[ReferenceTableName] 
    	, COL_NAME    (fc.referenced_object_id, fc.referenced_column_id)	[ReferenceColumnName]
    	, OBJECT_NAME (f.parent_object_id)									[TableName] 
    	, COL_NAME    (fc.parent_object_id,fc.parent_column_id)				[ColumnName] 
    FROM  sys.foreign_keys AS f
    INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
    
    -- Your query return only 4 columns and do not fit your table FKeyInstance structure!
    IF OBJECT_ID('FKeyInstance', 'U') IS NOT NULL DROP table FKeyInstance
    CREATE TABLE FKeyInstance (
    	[ForeignKey]			nvarchar(4000),
    	[ReferenceTableName]	nvarchar(4000),
    	[ReferenceColumnName]	nvarchar(4000),
    	[TableName]				nvarchar(4000),
    	[ColumnName]			nvarchar(4000)
    )
    
    EXEC sp_MSforeachdb @command1='
    	use ?; 
    	INSERT D.dbo.FKeyInstance -- Using tree parts name to insert the result to specific tabe in specific database
    	SELECT 
    		f.name [ForeignKey] 
    		,  OBJECT_NAME (f.referenced_object_id) [ReferenceTableName] 
    		,  COL_NAME(fc.referenced_object_id, fc.referenced_column_id) ReferenceColumnName
    		, OBJECT_NAME(f.parent_object_id) [TableName] 
    		, COL_NAME(fc.parent_object_id,fc.parent_column_id) [ColumnName] 
    	FROM  sys.foreign_keys AS f
    	INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
    '
    GO
    
    select * from D.dbo.FKeyInstance
    GO



    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]

    • סומן כתשובה על-ידי Netanel F יום חמישי 11 דצמבר 2014 11:56
    יום רביעי 10 דצמבר 2014 16:52
    מנחה דיון
  • זה נתנאל ולא נטע

    תודה רבה על התגובה 

    כנראה שהייתי צריך לכתוב את הטבלה בהשאילתא עם שלושה חלקים של הטבלה 

    תודה רבה זה עזר לי המון

    יום חמישי 11 דצמבר 2014 11:11
  • בכיף :-)

    סליחה על הטעות בשם

    * אנא זכור לסגור את השרשור / להצביע להודעות וכו' :-)


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]


    יום חמישי 11 דצמבר 2014 11:46
    מנחה דיון
  • הי נתנאל,

    הפרוצדורה sp_msforeachdb בסך הכל מריצה פקודה בתוך לולאה אשר עוברת על כל בסיסי הנתונים ב-Instance. חשוב להבין שהפרוצדורה הזאת לא משנה את ה-Context של בסיס הנתונים בכל איטרציה, ולכן מה שאתה עשית למעשה היה להריץ את אותה שאילתה הרבה פעמים באותו בסיס נתונים (מספר הפעמים הוא כמספר בסיסי הנתונים ב-Instance). כל מה שאתה צריך לעשות הוא להוסיף "? USE" כדי לשנות Context בכל איטרציה.
    בהזדמנות זו כדאי לציין שהפרוצדורה הזאת היא Undocumented, וגם יש בה כל מיני באגים. אני ממליץ על גירסא יותר טובה של הפרוצדורה הזאת, שכתב Aaron Bertrand. היא נמצאת כאן: http://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/.

    -----------------------------
    גיא גלנצר
    יועץ ומדריך SQL Server
    Madeira - SQL Server Services
    http://www.madeirasql.com


    יום ראשון 21 דצמבר 2014 06:16
    מנחה דיון