January 19, 2017 at 8:27 am
I need to Identify objects that do not use an Identity Column but a Lookup Table to generate an Unique Identifier.
I need to identify Stored Procedures, Views and Triggers.
The table name and the LastIDNumber are stored in the lookup table.
Unfortunately LastIDNumber may have a different name.
I'm looking for a script to examine each table. For example If I search on sys.procedures I get all tables.
I want to restrict my search to one table.
The following does not cut it:
SELECT DISTINCT OBJECT_NAME(OBJECT_ID),
object_definition(OBJECT_ID)
FROM sys.Procedures
WHERE object_definition(OBJECT_ID) LIKE '%' + 'LastIDNumber' + '%'
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 19, 2017 at 8:48 am
The column name in the lookup table is LID_ID_NB.
How can I search table by table?
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 19, 2017 at 9:26 am
Query the sys.columns system view. It has a column, is_identity. If a table is not using the identity function, you can see it there.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 19, 2017 at 9:36 am
Yes I know how to identify objects that do not have an identify column.
I need to identify all SP's, Views and triggers that are impacted.
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 19, 2017 at 9:42 am
Welsh Corgi - Thursday, January 19, 2017 9:36 AMYes I know how to identify objects that do not have an identify column.I need to identify all SP's, Views and triggers that are impacted.
Thank you.
Do you have foreign key constraints between the non-identity tables and the lookup table?
John
January 19, 2017 at 9:56 am
Welsh Corgi - Thursday, January 19, 2017 9:36 AMYes I know how to identify objects that do not have an identify column.I need to identify all SP's, Views and triggers that are impacted.
Thank you.
Probably identify the tables that have them and then look at the dependency views. This might help.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 19, 2017 at 9:58 am
If I understand you correctly, you want search across all tables and identify which columns contain key values from a primary key column contained in another table. There is no correlation in foreign key column names to the primary key, and no declared foreign key constraints.
I guess one way would be to do something like the following:
if exists (select 1 from TableA join Lookup on Lookup.ID = TableA.col1) print 'TableA.col1';
if exists (select 1 from TableB join Lookup on Lookup.ID = TableB.col2) print 'TableB.col2';
etc.
etc.
If the above is too labor intensive, you could do something like query the INFORMATION_SCHEMA.COLUMNS view and use that to dynamically generate the above statement for each table / column having the same data type as your ID column.
But really this sounds like a fools errand.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 19, 2017 at 10:05 am
Eric M Russell - Thursday, January 19, 2017 9:58 AMIf I understand you correctly, you want search across all tables and identify which columns contain key values from a primary key column contained in another table. There is no correlation in foreign key column names to the primary key, and no declared foreign key constraints.I guess one way would be to do something like the following:
if exists (select 1 from TableA join Lookup on Lookup.ID = TableA.col1) print 'TableA.col1';
if exists (select 1 from TableB join Lookup on Lookup.ID = TableB.col2) print 'TableB.col2';
etc.
etc.If the above is too labor intensive, you could do something like query the INFORMATION_SCHEMA.COLUMNS view and use that to dynamically generate the above statement for each table / column having the same data type as your ID column.
But really this sounds like a fools errand.
A fools errand?
Sounds crazy to me what do you think?
The database is a mess. Created 20 years ago.
What syntax do use?
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 19, 2017 at 10:20 am
if the referenced table has a specific column name (in the example [StatisticsName]), this will find all objects that are referencing the object(ie table) in question.
SELECT
SCHEMA_NAME(so.SCHEMA_ID) AS SchemaName,
so.name AS ObjectName,
so.type_desc,
sed.referenced_server_name,
sed.referenced_database_name,
sed.referenced_schema_name,
sed.referenced_entity_name,*
FROM sys.sql_expression_dependencies sed
INNER JOIN sys.objects so
ON sed.referencing_id = so.OBJECT_ID
INNER JOIN sys.columns colz ON sed.referenced_id = colz.object_id
WHERE colz.name = 'StatisticsName'
Lowell
January 19, 2017 at 10:56 am
I 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%'
January 19, 2017 at 2:24 pm
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 want to search each table one by one..
I tables and the MaxID are in the lookup table.
Thanks for all of the replies.
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 22, 2017 at 6:22 am
Welsh Corgi - Thursday, January 19, 2017 2:24 PMDesNorton - 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 want to search each table one by one..
I tables and the MaxID are in the lookup table.
Thanks for all of the replies.
Thanks so what do I do after I run the script. One table has 1024 dependent objects.
SELECT
SchemaName = OBJECT_SCHEMA_NAME(sm.object_id)
, ObjectName = OBJECT_NAME(sm.object_id)
FROM sys.sql_modules sm
WHERE sm.[definition] LIKE '%YourLookupTablename%'
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 22, 2017 at 7:00 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 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?
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 12:20 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?
I have no idea of how you would automate something like this. That script is where I start when I need to find all dependent/affected objects.
From here on in, it's a manual slog. I add all of the found objects to Excel, then open each object, and visually validate how/if it will affect what I am busy with, making notes in Excel. If it calls another object, i insert that object into Excel, (indented), and follow that one down the rabbit hole.
January 23, 2017 at 2:09 am
DesNorton - Monday, January 23, 2017 12:20 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?
I have no idea of how you would automate something like this. That script is where I start when I need to find all dependent/affected objects.
From here on in, it's a manual slog. I add all of the found objects to Excel, then open each object, and visually validate how/if it will affect what I am busy with, making notes in Excel. If it calls another object, i insert that object into Excel, (indented), and follow that one down the rabbit hole.
I do not follow your process.
Yes this is a manual slog and a fools errand.
But how can I convince someone of this?
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 - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply