sys.object
-
I have a table that is empty and it should have data in it. I need to find a stored procedure or table(s) that populates this table.
How do i modify this code to get what i need?
DECLARE @ObjectName NVARCHAR(261) = N'sys.object'
;
--===== Find the objects referencing our object by name.
SELECT RefObjectName = CONCAT(sre.referencing_schema_name
,'.'
,sre.referencing_entity_name)
,RefObjectType = obj.type_desc
,RefObjectID = sre.referencing_id
,IsCallerDependent = is_caller_dependent
,ParentObjectID = obj.parent_object_id
,ParentObjectName = CONCAT(OBJECT_SCHEMA_NAME(obj.parent_object_id)
,'.'
,OBJECT_NAME(obj.parent_object_id))
,CreatedOn = obj.create_date
,LastModifiedOn = obj.modify_date
FROM sys.dm_sql_referencing_entities(@ObjectName, 'OBJECT') sre
JOIN sys.objects obj
ON obj.object_id = sre.referencing_id
ORDER BY RefObjectType,RefObjectName
;
GO -
please don't create new threads to continue your questions - keep this on the same original thread.
I will advise you to read the sql server manuals for the system functions and tables as the ones referenced above as they contain all the information you need.
one other table that is also useful on this is sys.sql_modules.
-
Where do i modify the above code to get what i need?
-
The variable @ObjectName provided as an example was not plural such that it corresponds to an actual system object. Try it with N'sys.objects' instead of N'sys.object'. YOU NEED TO PROVIDE THE 2 PART NAME WHICH INCLUDES THE SCHEMA
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
Please send me ascreenshot of how you would do it, "YOU NEED TO PROVIDE THE 2 PART NAME WHICH INCLUDES THE SCHEMA"
-
Have a look at the Docs on "Multipart Names"
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
- yrstruly wrote:
I have a table that is empty and it should have data in it. I need to find a stored procedure or table(s) that populates this table.
How do i modify this code to get what i need?
DECLARE @ObjectName NVARCHAR(261) = N'sys.object'
;
--===== Find the objects referencing our object by name.
SELECT RefObjectName = CONCAT(sre.referencing_schema_name
,'.'
,sre.referencing_entity_name)
,RefObjectType = obj.type_desc
,RefObjectID = sre.referencing_id
,IsCallerDependent = is_caller_dependent
,ParentObjectID = obj.parent_object_id
,ParentObjectName = CONCAT(OBJECT_SCHEMA_NAME(obj.parent_object_id)
,'.'
,OBJECT_NAME(obj.parent_object_id))
,CreatedOn = obj.create_date
,LastModifiedOn = obj.modify_date
FROM sys.dm_sql_referencing_entities(@ObjectName, 'OBJECT') sre
JOIN sys.objects obj
ON obj.object_id = sre.referencing_id
ORDER BY RefObjectType,RefObjectName
;
GOInstead of "sys.object", use the actual schema name and object name of the table you're looking for,
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Not getting any data back. -
Is the current database that you're running the code in the same database as where the table is?
If so, then I don't know what the issue is other than there possible being zero working code in that same database that refers to it, which would certainly be a reason as to why it's not populated.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Yes, it is the database thats hold the table in question. I got this as an instruction:
"find out how the table is populated, fn_GetSummaryReport only reads from the table. If you can't find the proc that populates it then you'll have to compare it to the QA environment, it might be static data"
Something is wrong. I just tried it with a table with data in it and i get nothing back.
-
I'm thinking that you need to find out if it's "static" or not. It's starting to sound like it and you may have to make a copy of the data from the QA environment.
Why it's not returning your function as an object that uses it is unknown to me. In my prod environment, it finds all the goodies that point to this table. For me, it returns procs, functions, table constraints, etc. It doesn't return FKs pointing at it or indexes.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - yrstruly wrote:
Something is wrong. I just tried it with a table with data in it and i get nothing back.
That doesn't mean that there's a stored procedure that created the data. The data could have been inserted from a GUI on a webserver or any kind of ad hoc statement.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Please explain what is meant by static? Also, how would i do a QA?
I found it retun this data for:
- yrstruly wrote:
Yes, it is the database thats hold the table in question. I got this as an instruction:
"find out how the table is populated, fn_GetSummaryReport only reads from the table. If you can't find the proc that populates it then you'll have to compare it to the QA environment, it might be static data"
Something is wrong. I just tried it with a table with data in it and i get nothing back.
As a bit of a sidebar, I certainly don't envy you if they can't even tell you how the table is populated. They sure did through you into the deep end on all of this. Hang in there. You'll get through this.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Just had another thought... do you have privs to view everything? It may be that the script will only return what YOU can see on the database. Like I said, I've just started working on that script and so I haven't yet explored all the "gazintas" yet.
Of course, it could also be telling you the truth. You can find out for sure by right clicking on the table and "follow your nose" to list the dependencies for the object.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Not getting any data back.
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply