Global Find and Replace of Fields Names 3

[TomCarnahan]

I am new to SQL Server 2000 Enterprise. I have a number of tables, views, sprocs, function, contraints, triggers, etc. defined. Now I am realizing that the names I chose for some fields were somewhat cryptic.

Coming from the MS Access world, I am familiar with Speed Ferrett (c) that allows you to cross reference and find all occurences of the use of a field and replace it with a name that is more meaningful.

Question: as sophisticated as SQL Server is, it seems like there should be some feature internal to the program that would allow you to track down all occurences of a word that you want to change and change it without having to buy a third party tool

Is anyone familiar with such a feature? or could point me to some code that does that sort of thing?

Thanks,

Tom

--- Tom

 

[dbomrrsm]

If you know the name of the object this SP will help you find all occurences of it:

CREATE PROC sp_FindDBObjectByName 
@objname	varchar(100)
as
DECLARE @dbname 	varchar(100)
DECLARE @_tSQL		varchar(2000)
--do if exists here
IF exists(select * FROM master..sysobjects WHERE name = '_tTable') DROP table _tTable
CREATE TABLE _tTable	
(
databasename	varchar(100),
objectname	varchar(100),
db_object_type	varchar(100),
create_date	datetime
)
DECLARE db_scan CURSOR FOR
SELECT name FROM master..sysdatabases ORDER BY name
	
OPEN db_scan
FETCH next FROM db_scan INTO @dbname
WHILE @@fetch_status = 0


    begin
    	SET @_tSQL = 'insert INTO _tTable ' +
    		 'select ' + char(39) + @dbname + char(39) + ', name, xtype, ' +
    		 'crdate FROM [' + @dbname + ']..[sysobjects]'
    	PRINT @_tSQL
    	EXEC (@_tSQL)
    	FETCH next FROM db_scan INTO @dbname
END 
CLOSE db_scan
DEALLOCATE db_scan
 
SELECT databasename, objectname, 
	CASE db_object_type
		WHEN 'C' 	THEN 'CHECK constraint'
		WHEN 'D' 	THEN 'Default OR DEFAULT constraint'
		WHEN 'F' 	THEN 'FOREIGN KEY constraint'
		WHEN 'L' 	THEN 'Log'
		WHEN 'FN' 	THEN 'Scalar function'
		WHEN 'IF' 	THEN 'Inlined table-function'
		WHEN 'P' 	THEN 'Stored procedure'
		WHEN 'PK' 	THEN 'PRIMARY KEY CONSTRAINT (type IS K)'
		WHEN 'RF' 	THEN 'Replication filter stored PROCEDURE '
		WHEN 'S' 	THEN 'System table'
		WHEN 'TF' 	THEN 'Table function'
		WHEN 'TR' 	THEN 'Trigger'
		WHEN 'U' 	THEN 'User table'
		WHEN 'UQ' 	THEN 'UNIQUE CONSTRAINT (type IS K)'
		WHEN 'V ' 	THEN 'View'
		WHEN 'X' 	THEN 'Extended stored procedure'		
	ELSE db_object_type
	END AS 'db object',
	create_date FROM _tTable
	WHERE objectname LIKE '%' + @objname + '%'
	ORDER BY databasename, db_object_type 
DROP TABLE _tTable
GO

[blue]DBomrrsm[/blue]

 

[donutman]

Finally, the demonstration of the valid use of both dynamic SQL and cursors in one SP! My only criticism would be to change the Drop _tTable to an error message and RETURN...you never know what someone else might be doing.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[dbomrrsm]

donutman

should have said up front that the above assumes that _tTable does not already exist.

did you like the code ?

[blue]DBomrrsm[/blue]

 

[donutman]

Yes

 

[dbomrrsm]

Cheers - wasnt after a star but thanks anyway !

[blue]DBomrrsm[/blue]

 

[TomCarnahan]

Thanks dbomrrsm ... I'll give it a try!



--- Tom

 

[donutman]

Tom,
BTW, realize that the code doesn't include objects that use the object (e.g. a view that uses a particular table).
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]