What are the challenges you face when working across database platforms? Take the survey
Options

not finding 'CASCADE'

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

Tagged:

Answers

  • Options
    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.
    Jon Kirkwood | Technical Support Engineer | Redgate Software
  • Options
    LukeBLukeB Posts: 5 Bronze 2
    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 ...
  • Options
    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.
    Jon Kirkwood | Technical Support Engineer | Redgate Software
  • Options
    LukeBLukeB Posts: 5 Bronze 2
    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 
  • Options

    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:

    Jon Kirkwood | Technical Support Engineer | Redgate Software
  • Options
    LukeBLukeB Posts: 5 Bronze 2
    super, tx

Sign In or Register to comment.