Automation for Smart Rename

ggrater@hotmail.com · April 26, 2011 12:23PM

I have a database constructed with acronyms. All tables and columns begin with a three character acronym followed by capitalized columns and table names. I have created a mapping table that maps all tables and columns from the acronym insantity to camel case.

Is there an API for Smart Rename that would allow me programmically roll through the mapping table and execute a Smart Rename for each table name and column name?

Of course, there are all sorts of caveats to this approach, but I'm hoping someone knows of an API (or some "send keys") method of doing this.

Eddie D · April 27, 2011 1:25PM

Thank you for your post into the forum.

I have added your suggestion to an existing feature request, reference SP-3057.

I cannot guarantee the success of the feature request but if approval is given, hopefully you will see it in a future version of SQL Prompt.

Many Thanks
Eddie

ggrater@hotmail.com · April 27, 2011 2:53PM

Thanks Eddie!

As a suggestion, make the API callable via TSQL (maybe through the CLR via a function taking OldName, NewName and ObjectType) such that the API returns the SQL Script necessary to preform the Smart Rename. Then we can pull all that together and execute it as a large script (or set of scripts).

For Instance, I'd like to be able to do the following.

1) loop through each table and column from a source that provides the old and new name.
2) for each old value, call Red Gate's API to get the SQL required to perform the Smart Rename.
3) sp_executeSQL the returned SQL.
4) loop to the next old/new item.

Thanks!

Eddie D · May 4, 2011 2:20PM

Thank you for your reply.

I have updated the Feature Request, SP-3057, with your latest comments and suggestions.

Many Thanks
Eddie

tdennis · August 2, 2011 5:45PM

Please add my vote for this feature. I would like to be able to call some kind of function from T-SQL for each column in a database to output a list of objects that are dependent on it (so I can see which columns are not being referenced by any stored procedures).

The Dependencies list from Smart Rename is perfect, except it takes way too long to manually check hundreds of columns.

Thank you,
Traci

aolcot · August 3, 2011 4:10AM

Hi Traci, I was intrigued by your post this morning, so I quickly created this to see if I could achieve what you was after using tsql...

SELECT object_name(object_id) AS 'Tablename', name AS 'ColumnName',
(
SELECT DISTINCT object_name(object_id) FROM sys.sql_dependencies
WHERE class IN (0, 1) AND referenced_minor_id > 0 AND referenced_major_id = C.object_id
FOR XML PATH('Object'), ROOT('Dependencies'), TYPE
) AS 'DependencyXML'
FROM sys.columns C
WHERE EXISTS
(
SELECT 1 FROM sys.sql_dependencies
WHERE class IN (0, 1) AND referenced_minor_id > 0 AND referenced_major_id = C.object_id
)

I'm sure this could be refined and written better, but I hope this may help as a starter...

tdennis · August 3, 2011 11:40AM

Thank you so much for your reply! I tried your query and unfortunately, the sys.sql_dependencies is missing most of the stored procedures in the database. A google search revealed procedures that create temp tables, then run a query that joins in the temp table, will not record those dependencies due to deferred name resolution. If I create the temp table outside the proc, then exec sp_refreshsqlmodule '<procname>', the dependency information does get recorded. Almost every procedure in this database uses a generically named temp table with different column definition, so I may try to programmatically extract the create temp table statements for each proc so it could be re-created with the correct definition just prior to exec sp_refreshsqlmodule. I'll let you know if I make any progress on it, and again, thank you for taking the time to write up that query :-)

Traci

JAZ · February 9, 2021 10:12PM

i know this is an old post... anything on this feature?

Automation for Smart Rename

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk