SQL Script to Change All Table References in All Stored Procedures

SQL script to change all table references in all stored procedures

DO NOT RELY ON INFORMATION_SCHEMA.ROUTINES because ROUTINE_DEFINITION is only nvarchar(4000). You need to sys.sql_modules where definition is nvarchar(max)

try any of these to find the procedure that you need to modify:

SELECT DISTINCT
    LEFT(s.name+'.'+o.name, 100) AS Object_Name,o.type_desc --, m.definition
    FROM sys.sql_modules        m
        INNER JOIN sys.objects  o ON m.object_id=o.object_id
        INNER JOIN sys.schemas  s ON o.schema_id=s.schema_id
    WHERE m.definition Like '%'+@SearchValue+'%'
    ORDER BY 1

SELECT
    OBJECT_SCHEMA_NAME(m.object_id)+'.'+OBJECT_NAME(m.object_id) --, m.definition
    FROM sys.sql_modules  m
    WHERE m.definition like '%whatever%'

SELECT
    OBJECT_SCHEMA_NAME(m.object_id)+'.'+OBJECT_NAME(m.object_id), o.type_desc
        --,m.definition
    FROM sys.sql_modules        m
        INNER JOIN sys.objects  o ON m.object_id=o.object_id
    WHERE m.definition like '%whatever%'

you can uncomment m.definition to list out the content, but I find it better to just ID all the procedures and then review them manually, because you don't want to run UPDATE commands on the system tables. Script out the necessary procedures, make the changes (search/replace or manually), and then run the scripts!!!

Quickest/Easiest way to use Search/Replace through all stored procedures

To search: if you need to find database objects (e.g. tables, columns, triggers) by name - have a look at the FREE Red-Gate tool called SQL Search which does this - it searches your entire database for any kind of string(s).

It's a great must-have tool for any DBA or database developer - did I already mention it's absolutely FREE to use for any kind of use?

This tool doesn't support replacing text, however - but even just being able to find all the relevant stored procedures (or other DB objects) is very helpful indeed!

How to change schema name of a table in all stored procedures

Get a list of dependent objects by right-clicking on the table before you change the schema and then look at what is dependent on the table, make a list and then change those. There is, however, always a possibility that you'll miss something because it is possible to break the dependencies SQL server tracks.

But the best way would be to script the database out into a file and then do a search for the table name, make a list of all of the sprocs where it needs to be changed and then add those to the script to change the schema of the table.

How to change schema of all tables, views and stored procedures in MSSQL

Yes, it is possible.

To change the schema of a database object you need to run the following SQL script:

ALTER SCHEMA NewSchemaName TRANSFER OldSchemaName.ObjectName

Where ObjectName can be the name of a table, a view or a stored procedure. The problem seems to be getting the list of all database objects with a given shcema name. Thankfully, there is a system table named sys.Objects that stores all database objects. The following query will generate all needed SQL scripts to complete this task:

SELECT 'ALTER SCHEMA NewSchemaName TRANSFER [' + SysSchemas.Name + '].[' + DbObjects.Name + '];'
FROM sys.Objects DbObjects
INNER JOIN sys.Schemas SysSchemas ON DbObjects.schema_id = SysSchemas.schema_id
WHERE SysSchemas.Name = 'OldSchemaName'
AND (DbObjects.Type IN ('U', 'P', 'V'))

Where type 'U' denotes user tables, 'V' denotes views and 'P' denotes stored procedures.

Running the above script will generate the SQL commands needed to transfer objects from one schema to another. Something like this:

ALTER SCHEMA NewSchemaName TRANSFER OldSchemaName.CONTENT_KBArticle;
ALTER SCHEMA NewSchemaName TRANSFER OldSchemaName.Proc_Analytics_Statistics_Delete;
ALTER SCHEMA NewSchemaName TRANSFER OldSchemaName.Proc_CMS_QueryProvider_Select;
ALTER SCHEMA NewSchemaName TRANSFER OldSchemaName.COM_ShoppingCartSKU;
ALTER SCHEMA NewSchemaName TRANSFER OldSchemaName.CMS_WebPart;
ALTER SCHEMA NewSchemaName TRANSFER OldSchemaName.Polls_PollAnswer;

Now you can run all these generated queries to complete the transfer operation.

How to identify all stored procedures referring a particular table

SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%TableNameOrWhatever%'

BTW -- here is a handy resource for this type of question: Querying the SQL Server System Catalog FAQ

Find all stored procedures that reference a specific column in some table

One option is to create a script file.

Right click on the database -> Tasks -> Generate Scripts

Then you can select all the stored procedures and generate the script with all the sps. So you can find the reference from there.

Or

-- Search in All Objects
SELECT OBJECT_NAME(OBJECT_ID),
definition
FROM sys.sql_modules
WHERE definition LIKE '%' + 'CreatedDate' + '%'
GO

-- Search in Stored Procedure Only
SELECT DISTINCT OBJECT_NAME(OBJECT_ID),
object_definition(OBJECT_ID)
FROM sys.Procedures
WHERE object_definition(OBJECT_ID) LIKE '%' + 'CreatedDate' + '%'
GO

Source SQL SERVER – Find Column Used in Stored Procedure – Search Stored Procedure for Column Name

Snowflake SQL Procedure to Alter all tables in a database or schema

Answer

create or replace procedure enable_change_tracking_st()
returns varchar
language javascript
as     
$$  
    var my_sql_command = "select table_name from DEMO_DB.INFORMATION_SCHEMA.TABLES where table_schema = 'PUBLIC'"
    var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
    var r = statement1.execute();

    while(r.next()) {
        var table_name = r.getColumnValue(1);
        var sub_q = "ALTER table "+ table_name +" SET CHANGE_TRACKING = TRUE;"
        var statement2 = snowflake.createStatement( {sqlText: sub_q} );
        var r2 = statement2.execute();
    }
return 'Done';
$$
;

Mass Renaming of Tables and Stored Procedures

SQLRefactor from RedGate has a smart rename feature. It updates all the references to the renamed objects!

See here:http://www.red-gate.com/products/SQL_Refactor/features.htm

Rename all stored procedures in SQL in a single query

You can dump a script to do all of this and then just run the script

select STRING_AGG(CAST(
    'exec sp_rename @objname = N' + QUOTENAME(s.name + '.' + p.name, '''') + ', @newname = N' + QUOTENAME(LOWER(STUFF(p.name, 1, 3, 'proc_')), '''') + ', @objtype = ''OBJECT'';'
    AS nvarchar(max)), '
')
from sys.procedures p
join sys.schemas s on s.schema_id = p.schema_id
where p.name like 'sp[_]%';

You can even put that into a variable and run it through sp_executesql if you are that (fool-)hardy.

---

Related Topics