not finding 'CASCADE'

LukeB

hi
when I use SQL Search to find 'CASCADE' it does not find it in the table constraints/foreign keys. 

for example I want to find where I have setup 

ON UPDATE CASCADE

ON DELETE CASCADE
as part of a FK for tables related to another.

eg. SQLSearch is not finding 'CASCADE" for this FK :

ALTER TABLE [dbo].[sub_WellAirLift]  WITH NOCHECK ADD  CONSTRAINT [FK_sub_WellAir__MASTER] FOREIGN KEY([Hole])

REFERENCES [dbo].[MASTER] ([Hole])

ON UPDATE CASCADE

ON DELETE CASCADE

GO

ALTER TABLE [dbo].[sub_WellAirLift] CHECK CONSTRAINT [FK_sub_WellAir_MASTER]

GO

Jon_Kirkwood

Hi @LukeB

Thank you for reaching out on the Redgate forums regarding your SQL Search query.

Can you verify what sort of object is holding this code snippet?
SQL Search doesn't index Table DDL so if this is within a table create/alter script I would not expect it to find a hit on the value.

If this is stored within a function, procedure or similar object type then it would index the text and return a result.

If you can confirm your object type that is containing this text; we can ascertain if there is a concern with the software or it is expected behaviour.

LukeB

hi Jon

I guess, it is a TABLE object ? since it is a alter table statement for a Foreign Key.

I sort of get what you are saying, but if i search for a field name, say, 'RowID' SQLSearch will find that field name in the table(s) that it is used in ...

Jon_Kirkwood

That's right, table objects will index the name & columns which can return results when searched.
Limitations in searching can be found on this page: https://documentation.red-gate.com/ss/how-to-search

Object Type Limitations
When searching in 'Other' object types, SQL Search doesn't find some object types, including roles, certificates, and full-text catalogs. 
This is because it only indexes objects in the following Object Catalog Views:

• sys.objects

• sys.sql_modules

• sys.check_constraints

• sys.default_constraints

• sys.computed_columns

Any object types that aren't in these views won't be found by SQL Search.

Checking through these views I could not find any links to an alter table statement, so I do not believe that SQL Search would be indexing the data you are trying to locate.

LukeB

ok - I need to somehow find all tables that have  ON UPDATE CASCADE or ON DELETE CASCADE... Have a lot of tables to look through 

Jon_Kirkwood

Whilst it may not be possible through SQL Search, it may be possible to use SQL Compare to get results.

 

Conducting a comparison between your database and an empty scripts folder would create an output of all scripts used in your database.

Then using a search tool to scan all files for instances of your text "ON UPDATE CASCADE" could return all instances where this text is used

 

If you don't currently use SQL Compare you can download a trial version which should assist in this inquiry.

SQL Compare is provided as part of our SQL Toolbelt Essentials Bundle, which can be accessed here: https://www.red-gate.com/products/sql-toolbelt-essentials/

 

 

Use a search program to find specific text within the folder:

LukeB

super, tx