January 23, 2017 at 2:33 am
Welsh Corgi - Sunday, January 22, 2017 7:00 AMDesNorton - Thursday, January 19, 2017 10:56 AMI would start by finding all of the objects that reference the lookup table.
SELECT
SchemaName = OBJECT_SCHEMA_NAME(sm.object_id)
, ObjectName = OBJECT_NAME(sm.object_id)
FROM sys.sql_modules sm
WHERE sm.[definition] LIKE '%YourLookupTablename%'I believe I need a cursor to loop thru all of the dependent object of a table that have the LastIDNumber that is specified in the lookup table.
Any ideas would be greatly appreciated?
ok, I just want to i
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 23, 2017 at 2:37 am
Welsh Corgi - Monday, January 23, 2017 2:33 AMWelsh Corgi - Sunday, January 22, 2017 7:00 AMDesNorton - Thursday, January 19, 2017 10:56 AMI would start by finding all of the objects that reference the lookup table.
SELECT
SchemaName = OBJECT_SCHEMA_NAME(sm.object_id)
, ObjectName = OBJECT_NAME(sm.object_id)
FROM sys.sql_modules sm
WHERE sm.[definition] LIKE '%YourLookupTablename%'I believe I need a cursor to loop thru all of the dependent object of a table that have the LastIDNumber that is specified in the lookup table.
Any ideas would be greatly appreciated?
ok, I just want to i
ok I just want to identify dependent objects on a table one by one last has a LastIDNumber
Is this a fools errand and/or a manual errand?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 23, 2017 at 7:29 am
Welsh Corgi - Monday, January 23, 2017 2:37 AMWelsh Corgi - Monday, January 23, 2017 2:33 AMWelsh Corgi - Sunday, January 22, 2017 7:00 AMDesNorton - Thursday, January 19, 2017 10:56 AMI would start by finding all of the objects that reference the lookup table.
SELECT
SchemaName = OBJECT_SCHEMA_NAME(sm.object_id)
, ObjectName = OBJECT_NAME(sm.object_id)
FROM sys.sql_modules sm
WHERE sm.[definition] LIKE '%YourLookupTablename%'I believe I need a cursor to loop thru all of the dependent object of a table that have the LastIDNumber that is specified in the lookup table.
Any ideas would be greatly appreciated?
ok, I just want to i
ok I just want to identify dependent objects on a table one by one last has a LastIDNumber
Is this a fools errand and/or a manual errand?
If if you find a row that has a value that is in the lookup table that is no guarantee that it received that value from the lookup table. The best you would be able to do is identity those that are likely coming from the lookup table. There may be enough information in the system to accomplish what you are trying to do but we don't have enough of that information to offer much more than extremely vague suggestions. You could spend weeks digging through this and still get lots of false positives and miss a lot of things.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 23, 2017 at 8:33 am
DesNorton - Thursday, January 19, 2017 10:56 AMI would start by finding all of the objects that reference the lookup table.
SELECT
SchemaName = OBJECT_SCHEMA_NAME(sm.object_id)
, ObjectName = OBJECT_NAME(sm.object_id)
FROM sys.sql_modules sm
WHERE sm.[definition] LIKE '%YourLookupTablename%'
How should I modify this query to identify the table name?
SELECT name
FROM sys.procedures
WHERE Object_definition(object_id) LIKE '%LastIDNumber%'
ORDER BY Name
Thank you.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 23, 2017 at 8:55 am
Welsh Corgi - Monday, January 23, 2017 8:33 AMHow should I modify this query to identify the table name?
SELECT nameFROM sys.procedures
WHERE Object_definition(object_id) LIKE '%LastIDNumber%'
ORDER BY Name
Thank you.
That's not a reliable way to identify dependencies. What happens if the object name appears commented out, or enclosed in quotes, in the stored procedure definition? You should listen to Lowell's advice earlier in this thread.
John
January 23, 2017 at 9:04 am
DesNorton - Thursday, January 19, 2017 10:56 AMI would start by finding all of the objects that reference the lookup table.
SELECT
SchemaName = OBJECT_SCHEMA_NAME(sm.object_id)
, ObjectName = OBJECT_NAME(sm.object_id)
FROM sys.sql_modules sm
WHERE sm.[definition] LIKE '%YourLookupTablename%'
I tried all of the recommendations listed below.
I just need to relate sys.procedures to sys.tables or whatever to incluse the SP and table Name.
Thank you.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 23, 2017 at 9:16 am
I hate to ask but how do I modify the following code to include the Table Name.
I would greatly appreciate any help.
Thank you.
SELECT name
FROM sys.procedures
WHERE Object_definition(object_id) LIKE '%LastIDNumber%'
ORDER BY Name
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 23, 2017 at 9:31 am
John Mitchell-245523 - Monday, January 23, 2017 8:55 AMWelsh Corgi - Monday, January 23, 2017 8:33 AMHow should I modify this query to identify the table name?
SELECT nameFROM sys.procedures
WHERE Object_definition(object_id) LIKE '%LastIDNumber%'
ORDER BY Name
Thank you.
That's not a reliable way to identify dependencies. What happens if the object name appears commented out, or enclosed in quotes, in the stored procedure definition? You should listen to Lowell's advice earlier in this thread.
John
I have already confirmed that there is not an issue with the object name being commented out or enclosed in quotes.
I was told that the query listed above is exactly what they needed.
Thank you so much for the help that you provided me. -:)
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 23, 2017 at 9:41 am
Welsh Corgi - Monday, January 23, 2017 9:31 AMJohn Mitchell-245523 - Monday, January 23, 2017 8:55 AMWelsh Corgi - Monday, January 23, 2017 8:33 AMHow should I modify this query to identify the table name?
SELECT nameFROM sys.procedures
WHERE Object_definition(object_id) LIKE '%LastIDNumber%'
ORDER BY Name
Thank you.
That's not a reliable way to identify dependencies. What happens if the object name appears commented out, or enclosed in quotes, in the stored procedure definition? You should listen to Lowell's advice earlier in this thread.
John
I have already confirmed that there is not an issue with the object name being commented out or enclosed in quotes.
I was told that the query listed above is exactly what they needed.
Thank you so much for the help that you provided me. -:)
Maybe you wrote in invisible ink. How do you know there is no issue? And even if you do, how do you know there won't be in the future?
Who told you that the query is what they needed (and who are "they")? Just because someone tells you something, it doesn't necessarily mean it's right. Listen to good advice when it's offered. And good luck - I'm out.
John
January 23, 2017 at 10:06 am
John Mitchell-245523 - Monday, January 23, 2017 9:41 AMWelsh Corgi - Monday, January 23, 2017 9:31 AMJohn Mitchell-245523 - Monday, January 23, 2017 8:55 AMWelsh Corgi - Monday, January 23, 2017 8:33 AMHow should I modify this query to identify the table name?
SELECT nameFROM sys.procedures
WHERE Object_definition(object_id) LIKE '%LastIDNumber%'
ORDER BY Name
Thank you.
That's not a reliable way to identify dependencies. What happens if the object name appears commented out, or enclosed in quotes, in the stored procedure definition? You should listen to Lowell's advice earlier in this thread.
John
I have already confirmed that there is not an issue with the object name being commented out or enclosed in quotes.
I was told that the query listed above is exactly what they needed.
Thank you so much for the help that you provided me. -:)
Maybe you wrote in invisible ink. How do you know there is no issue? And even if you do, how do you know there won't be in the future?
Who told you that the query is what they needed (and who are "they")? Just because someone tells you something, it doesn't necessarily mean it's right. Listen to good advice when it's offered. And good luck - I'm out.
John
I beg you pardon but I do not believe I got a simple query that includes the Stored Procedure name and Table name?
Thank you.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 23, 2017 at 1:09 pm
John Mitchell-245523 - Monday, January 23, 2017 9:41 AMWelsh Corgi - Monday, January 23, 2017 9:31 AMJohn Mitchell-245523 - Monday, January 23, 2017 8:55 AMWelsh Corgi - Monday, January 23, 2017 8:33 AMHow should I modify this query to identify the table name?
SELECT nameFROM sys.procedures
WHERE Object_definition(object_id) LIKE '%LastIDNumber%'
ORDER BY Name
Thank you.
That's not a reliable way to identify dependencies. What happens if the object name appears commented out, or enclosed in quotes, in the stored procedure definition? You should listen to Lowell's advice earlier in this thread.
John
I have already confirmed that there is not an issue with the object name being commented out or enclosed in quotes.
I was told that the query listed above is exactly what they needed.
Thank you so much for the help that you provided me. -:)
Maybe you wrote in invisible ink. How do you know there is no issue? And even if you do, how do you know there won't be in the future?
Who told you that the query is what they needed (and who are "they")? Just because someone tells you something, it doesn't necessarily mean it's right. Listen to good advice when it's offered. And good luck - I'm out.
John
They is someone that has been around for 20 years and is the lead developer.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 23, 2017 at 1:52 pm
Just so you know, you really haven't provided enough information to really help you with an answer. You have the benefit of having access to your servers, databases, and coworkers where as we have just the information you provide.
Please, ask yourself, if all I have is that which I post could I answer the question? What else do I need to know if I am going to answer this question?
The best way to accomplish this is to set up a an empty sandbox database and build up the database objects and sample data that represents the problem domain, makes sure all your scripts for creating the objects and data can successfully run in an empty database and recreate your sandbox. Post those scripts along with the code you have written in an attempt to solve the problem but isn't working and then post a separate table and data with your hand generated results you are expecting to get from the sample data so we have something to test against.
I have read through this thread and really have no idea what you are trying to accomplish so I have no real idea of where to start in order to help you.
January 23, 2017 at 2:21 pm
Lynn Pettis - Monday, January 23, 2017 1:52 PMJust so you know, you really haven't provided enough information to really help you with an answer. You have the benefit of having access to your servers, databases, and coworkers where as we have just the information you provide.
Please, ask yourself, if all I have is that which I post could I answer the question? What else do I need to know if I am going to answer this question?
The best way to accomplish this is to set up a an empty sandbox database and build up the database objects and sample data that represents the problem domain, makes sure all your scripts for creating the objects and data can successfully run in an empty database and recreate your sandbox. Post those scripts along with the code you have written in an attempt to solve the problem but isn't working and then post a separate table and data with your hand generated results you are expecting to get from the sample data so we have something to test against.I have read through this thread and really have no idea what you are trying to accomplish so I have no real idea of where to start in order to help you.
ok, thanks.
I did not think that what I was asking was that complicated.
Identify Stored Procedures with text = 'LastIDNumber' and include the table name.
That is what I was asking for. Simply query DMS's to get that information.
It is not specific to any database and there is no need to create a sandbox.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 23, 2017 at 2:24 pm
Welsh Corgi - Monday, January 23, 2017 9:16 AMI hate to ask but how do I modify the following code to include the Table Name.I would greatly appreciate any help.
Thank you.
SELECT nameFROM sys.procedures
WHERE Object_definition(object_id) LIKE '%LastIDNumber%'
ORDER BY Name
Simply put, you can't do this with a query. You would use that query to identify all procedures that reference your lookup table. From there it is going to require a human to look at that code and decide what table(s) are affected. You could probably write code to do this but it would take days if not weeks to accommodate all the possible permutations. It would be faster to simply open each procedure and document what you find. It is really hard to offer much concrete advice because we don't really know what you are trying to do here. Maybe it is to identify all the tables that get their Primary Key value from that lookup table or maybe it is to identify how much work would be involved to use an identity instead. With some context we can probably help but with just vague explanations it is really hard to offer much more than a vague explanation of what we might try to do.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 23, 2017 at 2:38 pm
Sean Lange - Monday, January 23, 2017 2:24 PMWelsh Corgi - Monday, January 23, 2017 9:16 AMI hate to ask but how do I modify the following code to include the Table Name.I would greatly appreciate any help.
Thank you.
SELECT nameFROM sys.procedures
WHERE Object_definition(object_id) LIKE '%LastIDNumber%'
ORDER BY Name
Simply put, you can't do this with a query. You would use that query to identify all procedures that reference your lookup table. From there it is going to require a human to look at that code and decide what table(s) are affected. You could probably write code to do this but it would take days if not weeks to accommodate all the possible permutations. It would be faster to simply open each procedure and document what you find. It is really hard to offer much concrete advice because we don't really know what you are trying to do here. Maybe it is to identify all the tables that get their Primary Key value from that lookup table or maybe it is to identify how much work would be involved to use an identity instead. With some context we can probably help but with just vague explanations it is really hard to offer much more than a vague explanation of what we might try to do.
The feedback is taken well.
The database is huge. There are over an astronomical number of dependent objects per table.
What you are telling me is what I expected.
I was trying to make sure that I came to the correct conclusion.
What I have provided has been well received and I do not need to provide the table name.
As suspected I was informed that what I was trying to achieve was a fools errand and a manual slog.
I regret that I did not provide the information that was needed.
Thanks to everyone that responded to my query.
God Bless.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply