VBA & SQL SERVER

[Cordeyo]

Is there a way to programmatically modify the structure of views in SQL Server through VBA or VB? I have nearly 200 views that have a where conditions which contain the word 'GWOT' that needs to be changed to 'OCO'. I also have some views which have fields named ???_GWOT which need to be renamed ???_OCO. Google doesn't seem to want to cooperate.

Thanks for your answers,
Cordeyo

 

[garyb2008]

Hi Cordeyo

There may be better tools out there to update views like this and part of me is also thinking youd have to be pretty carefull updating views automatically: watch that their tested appropriately afterward. 200 views is quite a lot, is this an opportunity to rework the system to use a few well thought out stored procedures?

Anyway, you can use VBA/VB and the ADO object library to execute T-SQL statements against an SQL database. Theres lots of websites with details on using ADO () and heres a few STARTING points that MAY be usefull for the T-SQL to send to the database....

returning SQL definitions of a view

http://msdn.microsoft.com/en-us/library/aa933450(SQL.80).aspx

saving a modified view:

http://msdn.microsoft.com/en-us/library/aa933140(SQL.80).aspx

 

[Cordeyo]

Gary,

Thanks for the tips. After a little more research, this is what I have decided to do. I have discovered this SQL which will get me the text I need to change.

SELECT DISTINCT [NAME], xtype, O.ID, O.ID, [TEXT], 'GWOT' = SUBSTRING([TEXT], PATINDEX('%GWOT%', [TEXT]), 4)
FROM SYSOBJECTS O, SYSCOMMENTS C
WHERE xtype = 'V' AND PATINDEX('%GWOT%', [TEXT]) > 0
ORDER BY [NAME]

My plan is to use VB6 (ADO) or VB.2005 (DataReader) with a form that has 2 rich text boxes, (rtOriginal, rtUpdated), and a combo box. I was going to use VBA but I don't have any rich text controls that work in VBA and I don't feel like making my own. The combo box will contain the names of all of the views which meet my criteria. I will select a view name and place the [TEXT] from that view in rtOriginal. Then I will use Replace and put that text in rtUpdated. This way I can check for any discrepancies before I make the change permanent. Any thoughts?


Cordeyo

 

[Cordeyo]

But this is what I did in SQL Server 2005

-- =============================================
-- Author:		Cordeyo
-- Create date: 4-22-09 11:55 AM
-- Description:	
-- =============================================
ALTER PROCEDURE [dbo].[sp_CHANGE_PROCEDURE_TEXT]
	@PROCEDURE VARCHAR(50), @OLDTEXT VARCHAR(50), @NEWTEXT VARCHAR(50)
WITH 
	EXECUTE AS CALLER 
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @PROCEDURETEXT VARCHAR(8000);
	DECLARE @NEWPROCEDURE VARCHAR(8000);

	-- To build new text
	DECLARE @PROCEDURELENGTH BIGINT;           -- size of the procedure text
	DECLARE @LEFTSTRING VARCHAR(8000);         -- left side before @OLDTEXT 
	DECLARE @RIGHTSTRING VARCHAR(8000);      -- right side after @OLDTEXT 
	DECLARE @SEARCHLENGTH INT;                         -- length of the text to search for
	DECLARE @STARTPOSITION INT;                         -- where search string starts
	DECLARE @ENDPOSITION INT;                            -- where search string ends

	SET @STARTPOSITION = (SELECT PATINDEX('%'+@OLDTEXT+'%', TEXT)
		FROM SYSCOMMENTS C, SYSOBJECTS O 
		WHERE (O.ID = C.ID) AND (xtype = 'P') AND (NAME = @PROCEDURE)) 

	IF (@STARTPOSITION = 0)
		BEGIN
			PRINT @OLDTEXT + ' NOT FOUND!'
			RETURN
		END
	ELSE
		-- Get all stored procedure text into variable 
		SET @PROCEDURETEXT = (SELECT TEXT
														FROM SYSCOMMENTS C, SYSOBJECTS O 
														WHERE (O.ID = C.ID) AND (xtype = 'P') AND (NAME = @PROCEDURE))

		-- May need to change CREATE PROCEDURE to ALTER PROCEDURE
		SET @STARTPOSITION = PATINDEX('%CREATE PROCEDURE%', @PROCEDURETEXT)
		SET @PROCEDURELENGTH = LEN(@PROCEDURETEXT)

		IF @STARTPOSITION > 0
			BEGIN                        -- build new text which replaces CREATE with ALTER
				SET @SEARCHLENGTH = 16
				SET @ENDPOSITION = @STARTPOSITION + @SEARCHLENGTH
				SET @PROCEDURETEXT = 'ALTER PROCEDURE' + RIGHT(@PROCEDURETEXT, @PROCEDURELENGTH - (@ENDPOSITION - 1))
			END

			-- Prep for user requested change
			SET @STARTPOSITION = PATINDEX('%'+@OLDTEXT+'%', @PROCEDURETEXT)
			SET @PROCEDURELENGTH = LEN(@PROCEDURETEXT)

			-- Loop until all occurences are changed
			WHILE @STARTPOSITION > 0
				BEGIN
					-- Now build new text which replaces user parameter
					IF (@STARTPOSITION > 0)
						BEGIN            
							IF @STARTPOSITION > 0
								BEGIN
									SET @SEARCHLENGTH = LEN(@OLDTEXT)
									SET @ENDPOSITION = @STARTPOSITION + @SEARCHLENGTH
									SET @LEFTSTRING = LEFT(@PROCEDURETEXT, (@STARTPOSITION - 1))
									SET @RIGHTSTRING = RIGHT(@PROCEDURETEXT, @PROCEDURELENGTH - (@SEARCHLENGTH + (@STARTPOSITION - 1)))
									-- The new procedure text
									SET @PROCEDURETEXT = @LEFTSTRING + @NEWTEXT + @RIGHTSTRING
									-- Is there another change needed
									SET @STARTPOSITION = PATINDEX('%'+@OLDTEXT+'%', @PROCEDURETEXT)
									SET @PROCEDURELENGTH = LEN(@PROCEDURETEXT)
								END
							END
					END
	EXEC (@PROCEDURETEXT)

END