Unique index nad nullabilnim poljem

CREATE UNIQUE NONCLUSTERED INDEX idx_tabela_polje ON tabela(polje) WHERE polje IS NOT NULL

Promena fizičkog imena fajla na SQL serveru

-- Detach Current Database
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'MyDB'
GO
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
-- To Rename Physical Files */
EXEC xp_cmdshell 'RENAME "D:\SQLData\MSSQL10_50.SQL2008\MSSQL\DATA\MyDB.mdf", "MyDB_NEW.mdf"'
GO
EXEC xp_cmdshell 'RENAME "D:\SQLData\MSSQL10_50.SQL2008\MSSQL\DATA\MyDB.ldf", "MyDB_NEW_log.ldf"'
GO
-- Attach Renamed ProductsDB Database Online */
CREATE DATABASE MyDB ON
( FILENAME = N'D:\SQLData\MSSQL10_50.SQL2008\MSSQL\DATA\MyDB_NEW.mdf' ),
( FILENAME = N'D:\SQLData\MSSQL10_50.SQL2008\MSSQL\DATA\MyDB_NEW_log.ldf' )
FOR ATTACH
GO

Provera ispravnosti JMBG-a

Provera ispravnosti JMBG-a na SQL server strani :

CREATE FUNCTION fnCheckJMBG
(
@JMBG nvarchar(20)
)
RETURNS bit
AS
BEGIN

DECLARE @sum int
DECLARE @index int
DECLARE @numbers TABLE(ID int, Value int);

SET @index=1

WHILE @index <= 13
BEGIN
  INSERT INTO @numbers (ID, Value)
VALUES(@index, CAST(SUBSTRING (@JMBG, @index, 1) AS int))
           SET @index = @index + 1
END

SET @sum = (SELECT Value FROM @numbers WHERE ID=13)+
(SELECT Value FROM @numbers WHERE ID=1)*7 +
(SELECT Value FROM @numbers WHERE ID=2)*6 +
(SELECT Value FROM @numbers WHERE ID=3)*5 +
(SELECT Value FROM @numbers WHERE ID=4)*4 +
(SELECT Value FROM @numbers WHERE ID=5)*3 +
(SELECT Value FROM @numbers WHERE ID=6)*2 +
(SELECT Value FROM @numbers WHERE ID=7)*7 +
(SELECT Value FROM @numbers WHERE ID=8)*6 +
(SELECT Value FROM @numbers WHERE ID=9)*5 +
(SELECT Value FROM @numbers WHERE ID=10)*4 +
(SELECT Value FROM @numbers WHERE ID=11)*3 +
(SELECT Value FROM @numbers WHERE ID=12)*2

RETURN CASE WHEN (@sum % 11)= 0 THEN 1 ELSE 0 END

END
GO

Brisanje tabela ponekad nije lak posao

Brisanje tabela, posebno onih koje imaju puno relacija ka drugim tabelama, nije lak posao. Ovo je moje rešenje za brisanje tabele @TableName zajedno sa njenim foreign key-evima:


DECLARE @sqlCommand nvarchar(MAX), @sqlString nvarchar(MAX)

DECLARE cur CURSOR LOCAL FOR
SELECT
'ALTER TABLE ' +  OBJECT_SCHEMA_NAME(parent_object_id) +
'.[' + OBJECT_NAME(parent_object_id) +
'] DROP CONSTRAINT ' + name AS sqlString
FROM sys.foreign_keys
WHERE referenced_object_id = OBJECT_ID(@TableName)

OPEN cur

FETCH NEXT FROM cur INTO @sqlString

WHILE @@FETCH_STATUS = 0 BEGIN

EXEC (@sqlString)
FETCH NEXT FROM cur INTO @sqlString
END

CLOSE cur
DEALLOCATE cur

SET @sqlCommand = 'DROP TABLE dbo.[' + @TableName + ']'
EXEC (@sqlCommand)

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