H\u00e1t igen, f\u00fcgg\u0151s\u00e9gek. Ez az a t\u00e9ma, amivel ki lehet kergetni a vil\u00e1gb\u00f3l egy SQL Server DBA-t. Mindenki tudja, hogy az SQL Server 2005-ben f\u00fcgg\u0151s\u00e9gek kezel\u00e9se sz\u00e1nalmas, egy\u00e1ltal\u00e1n nem megb\u00edzhat\u00f3.<\/p>\n
Egy egyszer\u0171 p\u00e9ld\u00e1val vil\u00e1g\u00edtom meg.<\/p>\n
\r\ncreate view dbo.VA\r\nas\r\n select * from Production.Product\r\ngo\r\n\r\ncreate view dbo.VB\r\nas\r\n select * from VA\r\ngo\r\n<\/pre>\nVB f\u00fcgg VA-t\u00f3l, tiszta \u00fcgy. A gond az, hogy megalterezve VA-t elt\u0171nik az az inf\u00f3, hogy VB f\u00fcgg VA-t\u00f3l.<\/p>\n
\r\nalter view dbo.VA\r\nas\r\n select * from Production.Product\r\n<\/pre>\nEz eszement dolog, de \u00edgy van (nem tudom ellen\u0151rizni, mert nincs a k\u00f6zelemben SQL 2005, ha valaki tudja valid\u00e1lni, k\u00e9rem tegye meg). <\/p>\n
2008-ban m\u00e1r norm\u00e1lisan mennek a f\u00fcgg\u0151s\u00e9gek. Ennek az az oka, hogy nem csak id szerint t\u00e1rolj\u00e1k \u0151ket, hanem n\u00e9v szerint is. A r\u00e9gebbi verzi\u00f3k csak id-t haszn\u00e1ltak, \u00edgy nem tudt\u00e1k let\u00e1rolni a k\u00e9sleltetett n\u00e9vfelold\u00e1s miatt id\u0151legesen hi\u00e1nyz\u00f3 f\u00fcgg\u0151s\u00e9geket (mondjuk ez nem menti fel \u0151ket az el\u0151bbi p\u00e9lda eset\u00e9ben).<\/p>\n
2008-ban a f\u00fcgg\u0151s\u00e9geket a sys.sql_expression_dependencies rendszert\u00e1bla t\u00e1rolja, \u00e9s k\u00e9t f\u00fcggv\u00e9ny seg\u00edt lek\u00e9rdezni bel\u0151l\u00fck.<\/p>\n
\r\ndrop view dbo.VA\r\ndrop view dbo.VB\r\ndrop proc dbo.PA\r\ndrop proc dbo.PB\r\ngo\r\n\r\ncreate view dbo.VA\r\nas\r\n select * from Production.Product\r\ngo\r\n\r\nselect OBJECT_NAME(referencing_id), referenced_entity_name, referenced_id\r\nfrom sys.sql_expression_dependencies\r\nwhere referencing_id = OBJECT_ID('dbo.VA')\r\n<\/pre>\n\r\nreferencing_object referenced_entity_name referenced_id\r\n------------------------------ ------------------------------ -------------\r\nVA Product 1461580245\r\n<\/pre>\nSz\u00e9pen van n\u00e9v \u00e9s id is, hisz l\u00e9tezett a Product t\u00e1bla. Eddig ok.<\/p>\n
\r\ncreate view dbo.VB\r\nas\r\n select * from VA\r\ngo\r\n\r\nselect OBJECT_NAME(referencing_id) referencing_object, referenced_entity_name, referenced_id\r\nfrom sys.sql_expression_dependencies\r\nwhere referencing_id in (OBJECT_ID('dbo.VA'), OBJECT_ID('dbo.VB'))\r\n<\/pre>\n\r\nreferencing_object referenced_entity_name referenced_id\r\n------------------------------ ------------------------------ -------------\r\nVA Product 1461580245\r\nVB VA 992722589\r\n<\/pre>\nEz is rendben van. Most j\u00f6n a r\u00e9gi probl\u00e9m\u00e1s pont:<\/p>\n
\r\nalter view dbo.VA\r\nas\r\n select * from Production.Product\r\ngo\r\n\r\nselect OBJECT_NAME(referencing_id) referencing_object, referenced_entity_name, referenced_id\r\nfrom sys.sql_expression_dependencies\r\nwhere referencing_id in (OBJECT_ID('dbo.VA'), OBJECT_ID('dbo.VB'))\r\n<\/pre>\n\u00c9s a kimenet j\u00f3!<\/p>\n
\r\n------------------------------ ------------------------------ -------------\r\nVA Product 1461580245\r\nVB VA 992722589\r\n<\/pre>\nJ\u00f6het a k\u00e9seltetett n\u00e9vfelold\u00e1s tesztje.<\/p>\n
\r\ncreate proc dbo.PA\r\nas\r\n exec dbo.PB\r\ngo\r\n\r\nselect OBJECT_NAME(referencing_id) referencing_object, referenced_entity_name, referenced_id\r\nfrom sys.sql_expression_dependencies\r\nwhere referencing_id in (OBJECT_ID('PA'))\r\n<\/pre>\n\r\nThe module 'PA' depends on the missing object 'dbo.PB'. The module will still be created; however, it cannot run successfully until the object exists.\r\nreferencing_object referenced_entity_name referenced_id\r\n------------------------------ ------------------------------ -------------\r\nPA PB NULL\r\n<\/pre>\nL\u00e1that\u00f3, hogy a referenced_id NULL, mert m\u00e9g nem l\u00e9tezik PB, de n\u00e9v alapj\u00e1n felvett\u00e9k a f\u00fcgg\u0151s\u00e9get. Kiv\u00e1l\u00f3! Mikor lesz kit\u00f6ltve az id?<\/p>\n
\r\ncreate proc dbo.PB\r\nas\r\ngo\r\n\r\nselect OBJECT_NAME(referencing_id) referencing_object, referenced_entity_name, referenced_id\r\nfrom sys.sql_expression_dependencies\r\nwhere referencing_id in (OBJECT_ID('PA'))\r\n<\/pre>\n\r\nreferencing_object referenced_entity_name referenced_id\r\n------------------------------ ------------------------------ -------------\r\nPA PB 1040722760\r\n<\/pre>\nA reference_id most m\u00e1r j\u00f3, mert l\u00e9tezik a hivatkozott objektum.<\/p>\n
N\u00e9zz\u00fck a n\u00e9zeteket. Kire hivatkozik egy ojjektum? sys.dm_sql_referenced_entities n\u00e9zet:<\/p>\n
\r\nselect OBJECT_NAME(referenced_id) referenced_object, * from sys.dm_sql_referenced_entities('dbo.PA', 'OBJECT')\r\n<\/pre>\n\r\nreferenced_object referencing_minor_id referenced_server_name referenced_database_name referenced_schema_name referenced_entity_name referenced_minor_name referenced_id referenced_minor_id referenced_class referenced_class_desc is_caller_dependent is_ambiguous\r\n------------------------------ -------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------- ------------------- ---------------- ------------------------------ ------------------- ------------\r\nPB 0 NULL NULL dbo PB NULL 1040722760 0 1 OBJECT_OR_COLUMN 0 0\r\n<\/pre>\nL\u00e1that\u00f3, hogy PA PB-re hivatkozik.<\/p>\n
Ki hivatkozik PB-re? sys.dm_sql_referencing_entities f\u00fcggv\u00e9ny:<\/p>\n
\r\nselect * from sys.dm_sql_referencing_entities('dbo.PB', 'OBJECT')\r\n<\/pre>\n\r\nreferencing_schema_name referencing_entity_name referencing_id referencing_class referencing_class_desc is_caller_dependent\r\n------------------------------ ------------------------------ -------------- ----------------- ------------------------------ -------------------\r\ndbo PA 1024722703 1 OBJECT_OR_COLUMN 0\r\n<\/pre>\n