I had a scenario recently where I had to transfer set of objects having history data from my production server to another server. The naming convention followed for project required use of _HIS suffix for the history tables which made my matters easy.
I made use of filter feature of SSMS to identify the history tables.
The filter option can be selected as shown below. This option is available from various object folders like tables, stored procedures, functions etc. inside object explorer in SSMS.
This will launch the filter window where you need to specify your filter conditions.
The required filter values can be filled into the Value box and required operator can be chosen in the available dropdown which will have options as below
- Contains – For pattern match
- Equals – For exact match and
- Does not contain – For exclusion.
For this illustration I’m checking for objects with names containing the word State in them.
Once filter is applied the object explorer view changes to below
As you see the folder header will have the word filtered added to denote that a filter has been applied and you will see only filtered objects in the list.
Once you get filtered list, then you can do your required actions on the list.
In my case, I wanted to script them out so I went to the object explorer details window and chose script as option
This gave me scripts of all the required objects in a new window as follows
This will provide you with a quick way of selecting a filtered list of objects and scripting them out using SSMS object explorer. This will save you some effort compared to the programmatic way of doing this using catalog views.
