Zamena stringa u SQL procedurama, viewima i funkcijama

Našao na netu korisnu skriptu i malo je modifikovao .... pronalazi traženi string u procedurama, viewima i funkcijama i generiše novu skriptu koja radi njihov alter:

use DATABASE_NAME
go

-- set "Result to Text" mode by pressing Ctrl+T
SET NOCOUNT ON

DECLARE @sqlToRun nvarchar(MAX), @searchFor NVARCHAR(100), @replaceWith VARCHAR(100)

-- text to search for
SET @searchFor = 'SEARCH_FOR'
-- text to replace with
SET @replaceWith = 'REPLACE_WITH'

-- this will hold stored procedures text
DECLARE @temp TABLE (spText VARCHAR(MAX))

DECLARE curHelp CURSOR FAST_FORWARD
FOR
-- get text of all stored procedures that contain search string
-- I am using custom escape character here since i need to espape [ and ] in search string
SELECT DISTINCT 'sp_helptext '''+OBJECT_SCHEMA_NAME(id)+'.'+OBJECT_NAME(id)+''' '
FROM syscomments WHERE TEXT LIKE '%' + REPLACE(REPLACE(@searchFor,']','\]'),'[','\[') + '%' ESCAPE '\'
ORDER BY 'sp_helptext '''+OBJECT_SCHEMA_NAME(id)+'.'+OBJECT_NAME(id)+''' '

OPEN curHelp

FETCH next FROM curHelp INTO @sqlToRun

WHILE @@FETCH_STATUS = 0
BEGIN
   --insert stored procedure text into a temporary table
   INSERT INTO @temp
   EXEC (@sqlToRun)
 
   -- add GO after each stored procedure
   INSERT INTO @temp
   VALUES ('GO')
 
   FETCH next FROM curHelp INTO @sqlToRun
END

CLOSE curHelp
DEALLOCATE curHelp

-- find and replace search string in stored procedures
-- also replace CREATE PROCEDURE with ALTER PROCEDURE
UPDATE @temp
SET spText = REPLACE(REPLACE(spText,'CREATE PROCEDURE', 'ALTER PROCEDURE'),@searchFor,@replaceWith)

UPDATE @temp
SET spText = REPLACE(REPLACE(spText,'CREATE VIEW', 'ALTER VIEW'),@searchFor,@replaceWith)

UPDATE @temp
SET spText = REPLACE(REPLACE(spText,'CREATE FUNCTION', 'ALTER FUNCTION'),@searchFor,@replaceWith)


SELECT spText FROM @temp
-- now copy and paste result into new window
-- then make sure everything looks good and run
GO

Izvršavanje procedura sa OWNER privilegijama

Da bi se SP izvršila sa OWNER privilegijama potrebno je dodati direktivu WITH EXECUTE AS OWNER. Primer procedure koja izvršava string sa Owner privilegijama:

ALTER PROCEDURE [dbo].[UTL_ExecNGK]
@sql nvarchar(500)
WITH EXECUTE AS OWNER
AS
BEGIN
SET NOCOUNT ON;

EXECUTE sp_executesql @sql

END

Debug mod za MINI DLNA na POPCORNHOUR

Prvo treba isključiti servis:
# cd /share/Apps/local/etc/init.d
# ./minidlna stop

zatim pokrenuti Minidlna u debug modu:
# cd /share/Apps/local/sbin
# ./minidlnad -R -d -f /share/Apps/local/etc/minidlna/minidlna.conf

Rebuild svih indexa u bazi

USE ImeBaze

DECLARE @TableName nvarchar(255)

DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT  @TableName + '.....'
DBCC DBREINDEX(@TableName,' ',0) -- FillFactor=0

FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor

DEALLOCATE TableCursor

PRINT 'Finished!'