ip: 100.28.231.85 DKs blog - SQL (T-SQL) examples

DK's Blog

SQL (T-SQL) examples

Most common T-SQL usage (for MSSQL)

 

Unusual examples of inserting data in SQL (MS SQL Server T-SQL)

--example 1 insert into table
INSERT INTO tablename (name,price) VALUE ('joe', 100)


--example 2 insert into table from another one (input data should be same type as input)
INSERT INTO tablename (name,price)
SELECT name, value FROM table2 WHERE 1=1


--example 3 insert into table which will be created
SELECT name , price INTO table1 FROM table2 WHERE 1=1

 


Receiving result from SQL as scalar here is example:

SELECT id FROM table

returns this:
1
2
3
4
5

 

SELECT CAST(id AS varchar(10)) + ',' FROM table WHERE 1=1 FOR XML PATH('')

result is this:
1,2,3,4,5,

 


Full text search example (MS SQL Server T-SQL):

SELECT * FROM table WHERE CONTAINS((Name,discription), ' "Mountain" OR "Road*" ')
SELECT * FROM table WHERE CONTAINS(Name, '"chain*" NEAR "full*"');

 


Example of thesaurus + full text search (MS SQL Server T-SQL)

SELECT * FROM Person WHERE CONTAINS(*,'FORMSOF(Thesaurus,"Tony")')

 

thesaurus file for croatian is here:
\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\FTData\tshrv.xml

 


How to show last record inserted in table (MS SQL Server T-SQL):

SELECT @@IDENTITY AS 'Identity'

 


Allow insert in AutoIncrement field (identity)
Keep in mind that only INSERT will work UPDATE won't!

SET IDENTITY_INSERT table ON
--insert something
SET IDENTITY_INSERT table OFF

 


Example of update table1 from table2 (update table from another table)

UPDATE Table1 SET Table1.NekoPolje=Table2.NekoPolje FROM Table2 WHERE Table2.Id=Table1.ID

 

Example of update subquery
subquery MUST return only 1 value

 

UPDATE t 
SET FieldName =
(
 SELECT NameName2 
 FROM table2
 WHERE ID = t.ID
)
FROM table1 t;

 


How to create linked servers (all steps + usage)

DestinationServerName - name of destination server (put it in hosts if is not in same subnet) 111.111.111.111 - IP of DestinationServerName login/password -self explanatory

 

sp_addlinkedserver 'DestinationServerName', '', 'SQLNCLI', NULL, NULL, 'SERVER=111.111.111.111', NULL
sp_addlinkedsrvlogin 'DestinationServerName', 'false', NULL, 'login', 'password'
SELECT * FROM DestinationServerName.database.dbo.table

 

to show all linked servers:

EXEC sp_linkedservers

 

second version of mysql mssql linked server T-SQL

sp_addlinkedserver 'DestinationServerName', 'MySQL', 'MSDASQL', Null, Null, 'Driver={MySQL ODBC 5.1 Driver};DB=DBName;SERVER=111.111.111.111;uid=USRENAME;pwd=PASS;Port=3306;OPTION=3' 
sp_addlinkedsrvlogin 'DestinationServerName', 'false', NULL, 'USRENAME', 'PASS'

 


Linked server MSSQL - MySQL

1. create ODBC on MSSQL server

2. on MSSQL server -> Server Objects -> Linked Servers -> Add new

3. choose name for linked server, server type: other data source:  Microsoft OLE DB  Provides for ODBC Drivers

 

SQL to use MySQL server:

 

SELECT * FROM OPENQUERY(LinkedServerName, 'SELECT * FROM tableName')

 


reload thesaurus for croatian (1050) english is 1033 (MS SQL Server T-SQL)

 

EXEC sys.sp_fulltext_load_thesaurus_file 1050;
GO

 


show all stopwords for one language 1050 - croatian 1033 - english (MS SQL Server T-SQL)

SELECT ssw.stopword, slg.name
FROM sys.fulltext_system_stopwords ssw
  JOIN sys.fulltext_languages slg
    ON slg.lcid = ssw.language_id
WHERE slg.lcid=1050

Show all stopwords in stoplist (MS SQL Server T-SQL)

SELECT sl.name     as StopListName,
       sw.stopword as StopWord,
       lg.alias    as LanguageAlias,
       lg.name     as LanguageName,
       lg.lcid     as LanguageLCID
FROM sys.fulltext_stopwords sw
  JOIN sys.fulltext_stoplists sl ON sl.stoplist_id = sw.stoplist_id
  JOIN master.sys.syslanguages lg ON lg.lcid = sw.language_id; 

Nice link about stopwords


Show Indexes which are heavily fragmented (MS SQL Server T-SQL)

SELECT OBJECT_NAME(i.object_id) AS TableName
    ,i.name AS TableIndexName
    ,phystat.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats 
    (DB_ID(N'Name_Of_Database'), NULL, NULL, NULL, 'DETAILED') phystat 
    INNER JOIN sys.indexes i ON i.object_id = phystat.object_id
      AND i.index_id = phystat.index_id
WHERE phystat.avg_fragmentation_in_percent > 10
--AND   phystat.avg_fragmentation_in_percent < 100 --leftout show them in order ...
ORDER BY avg_fragmentation_in_percent desc

 


Show statistics for database, order by average fragmentation in percent (MS SQL Server T-SQL)

SELECT * 
FROM sys.dm_db_index_physical_stats 
  (DB_ID(N'DatabaseName'), NULL, NULL, NULL, 'DETAILED')
ORDER BY avg_fragmentation_in_percent desc

 


Nice store procedure for index defragmentation in MS SQL, requires little T-SQL knowledge

/***********************************************************************************************************************
        Version 1.0
         19 Aug 2010

         Gregory Ferdinanddsen
         greg@ferdinandsen.com


             This SP will rebuild/reorg indexes.

         Parameters:
             @DB = Either 'All' or the name of one DB. If 'All' all databases on the server are examined; otherwise the name of a single DB.
             @Stats = Statistical Sampling Method (Limited, Sampled, or Detailed) for determining what index will be impacted.
                 --LIMITED - It is the fastest mode and scans the smallest number of pages.
                         For an index, only the parent-level pages of the B-tree (that is, the pages above the leaf level) are scanned
                 --SAMPLED - It returns statistics based on a 1 percent sample of all the pages in the index or heap.
                         If the index or heap has fewer than 10,000 pages, DETAILED mode is used instead of SAMPLED.
                 --DETAILED - It scans all pages and returns all statistics.
             @MinPageCount = Since index with few pages usually don't defrag (and a table scan is preferred), ignores small indexes
             @MaxPageCount = Maximum number of index pages to be considered. This can preclude very large indexes
             @Fill Factor = Specifies a percentage that indicates how full the Database Engine should make the leaf level of each index page
                     during index creation or alteration. fillfactor must be an integer value from 1 to 100. The default is 0.
             @PAD_Index = The percentage of free space that is specified by FILLFACTOR is applied to the intermediate-level pages of the index.
                 If FILLFACTOR is not specified at the same time PAD_INDEX is set to ON, the fill factor value stored in sys.indexes is used.
             @SortInTempDB = The intermediate sort results that are used to build the index are stored in tempdb.
                 If tempdb is on a different set of disks than the user database, this may reduce the time needed to create an index.
                 However, this increases the amount of disk space that is used during the index build.
             @Online = Online rebuild, for editions that support online rebuild (for editions that do not support online rebuild, this is ignored)
             @ReBuildTheshold = The threshold for deciding to rebuild v reorg (MSFT recomend's 30)
             @ReOrgThreshold = The threshold for deciding to rebuild v reorg (MSFT recomend's 5)
             @MaxFrag = The maximum amount of fragmentation to defrag (i.e. you don't want to defrag an index over 80%)
             @ChangeRecoveryModel = Set's the DB's in simple recovery mode prior to starting, reverts back to original mode on completion.
  
             NB:
             @Fill_Factor, @PAD_Index will only be applied to index that are rebuilt (Fragmentation >= @ReBuildTheshold)
  
  
             Alter Index -- http://technet.microsoft.com/en-us/library/ms188388.aspx
             sys.dm_db_index_physical_stats -- http://msdn.microsoft.com/en-us/library/ms188917.aspx
  
         examples:
             exec dbadmin..sp_Defrag_Indexes, @FillFactor = 75, @PAD_Index = 'true', @Stats = 'Detailed'
  
             exec dbadmin..sp_Defrag_Indexes
                 @DB = 'changepoint',
                 @FillFactor = 65,
                 @PAD_Index = 'true',
                 @Stats = 'Detailed',
                 @ChangeRecoveryModel = 'true',
                 @minpagecount = 150
                 
                 
exec [dbo].[sp_Defrag_Indexes]
@DB = 'zase',
@FillFactor = 90,
@Stats = 'LIMITED',
@ChangeRecoveryModel = 'true'
                 
 ***********************************************************************************************************************/

  
 create procedure [dbo].[sp_Defrag_Indexes]
     (
     @DB varchar(256) = 'all',
     @Stats varchar(8) = 'sampled',
     @MinPageCount int = 20,
     @MaxPageCount float = 1000000000000000, --A very large default number
     @FillFactor int = NULL,
     @PAD_Index varchar(8) = 'false',
     @SortInTempDB varchar(8) = 'true',
     @OnlineReq varchar(8) = 'true',
     @ReBuildTheshold real = 30.0,
     @ReOrgThreshold real = 5.0,
     @MaxFrag real = 100.0,
     @ChangeRecoveryModel varchar(8) = 'false'
     )
  
     AS
  
     declare @SQLCmd AS varchar (8000)
     declare @SQLCmdBk AS varchar(4096)
     declare @SQLCmdWith AS varchar(4096)
     declare @SQLCmdFill varchar(512)
     declare @SQLCmdOnline varchar(512)
     declare @SQLCmdPad varchar(512)
     declare @SQLCmdSort varchar(512)
     declare @SQLCmdRecovery varchar(512)
     declare @exit varchar(8)
     declare @ErrorTxt AS varchar(128)
     declare @SQLEdition AS varchar(64)
     declare @Online AS varchar(8)
     declare @DBName AS varchar(256)
     declare @ObjectID int
     declare @IndexID int
     declare @PartitionNum AS bigint
     declare @Frag AS float
     declare @PageCount AS bigint
     declare @PartitionCount AS bigint
     declare @ParititionNum AS bigint
     declare @IndexName AS varchar(128)
     declare @SchemaName AS varchar(128)
     declare @ObjectName AS varchar(128)
     declare @ParmDef nvarchar(512)
     declare @SQLCmdID AS nvarchar(1024)
     declare @RecoveryModel AS varchar(16)
  
     --Verify that proper parameters were passed to SP
     IF @Stats NOT IN ('limited', 'sampled', 'detailed')
         begin
             RaisError ('@Stats must be "limited", "sampled", or "detailed"', 16, 1)
             RETURN
         end
  
     IF @PAD_Index NOT IN ('true', 'false')
         begin
             RaisError ('@PAD_Index must be "true" or "false"', 16, 1)
             RETURN
         end
  
     IF @SortInTempDB NOT IN ('true', 'false')
         begin
             RaisError ('@SortInTempDB must be "true" or "false"', 16, 1)
             RETURN
         end
  
     IF @OnlineReq NOT IN ('true', 'false')
         begin
             RaisError ('@OnlineReq must be "true" or "false"', 16, 1)
             RETURN
         end
  
     IF @FillFactor NOT BETWEEN 0 AND 100
         begin
             RaisError ('@FillFactor must be between 0 and 100', 16, 1)
             RETURN
         end
  
     IF @ReBuildTheshold NOT BETWEEN 1 AND 100
         begin
             RaisError ('@ReBuildTheshold must be between 1 and 100', 16, 1)
             RETURN
         end
  
     IF @ReOrgThreshold NOT BETWEEN 1 AND 100
         begin
             RaisError ('@ReOrgThreshold must be between 1 and 100', 16, 1)
             RETURN
         end
  
     --There would be nothing returned if MaxFrag was less than the reorg threshold.
     IF @MaxFrag NOT BETWEEN @ReOrgThreshold AND 100
         begin
             RaisError ('@MaxFrag must be between the @ReOrgThreshold value (default of 5) and 100', 16, 1)
             RETURN
         end
  
     IF @MinPageCount < 0
         begin
             RaisError ('@MinPageCount must be positive', 16, 1)
             RETURN
         end
  
     IF @MaxPageCount < 10
         begin
             RaisError ('@MaxPageCount must be greater than 10', 16, 1)
             RETURN
         end
  
     IF @ChangeRecoveryModel NOT IN ('true', 'false')
         begin
             RaisError ('@ChangeRecoveryModel must be "true" or "false"', 16, 1)
             RETURN
         end
  
     IF @MinPageCount > @MaxPageCount
         begin
             RaisError ('@MinPageCount cannot be greater than @MaxPageCount', 16, 1)
             RETURN
         end
  
     IF @DB <> 'All'
         begin
             IF NOT EXISTS (SELECT name FROM sys.DATABASES WHERE name = @DB)
                 begin
                     SET @ErrorTxt = 'The supplied database (' + @DB + ') does not exist.'
                     RaisError (@ErrorTxt, 16, 1)
                     RETURN
                 end
         end
  
     --You cant have rebuild be at a lower level than reorg
      IF @ReBuildTheshold < @ReOrgThreshold SET @ReOrgThreshold = @ReBuildTheshold - 0.01
 
     --Determine SQL Edition (for online rebuild -- Enterprise and Developer support online rebuild)
      SET @SQLEdition = cast(ServerProperty('Edition') AS varchar)
      SET @SQLEdition =
          case
              when @SQLEdition = 'Enterprise Edition' then 'Enterprise'
              when @SQLEdition = 'Standard Edition' then 'Standard'
              when @SQLEdition = 'Developer Edition' then 'Developer'
          end
      IF @SQLEdition = 'Enterprise' OR @SQLEdition = 'Developer'
          begin
              SET @Online = 'true'
          end
      else SET @Online = 'false'
   
      --If only one database, then go to the innser cursor (and exit that cursor before the fetch next command)
      SET @Exit = 'false'
      IF @DB <> 'All'
          begin
              SET @Exit = 'true'
              SET @DBName = @DB
              goto ExecuteForEachDatabase
          end
   
      --Outer Cursor for DBName
      declare DatabaseNames cursor
          FOR SELECT name FROM sys.DATABASES
   
          open DatabaseNames
          fetch next FROM DatabaseNames INTO @DBName
   
          while @@fetch_status <> -1
              begin
  ExecuteForEachDatabase:
                  --Delete the Temp Table
                  IF EXISTS (SELECT * FROM tempdb.sys.objects WHERE name = '#Fragmentation' AND type IN('U'))
                      begin
                          DROP TABLE #Fragmentation
                      end
   
                  --Determine Recovery Model
                  SET @RecoveryModel = cast(DatabasePropertyEx(@DBName, 'Recovery') AS varchar(16))
                  IF @RecoveryModel IN ('FULL', 'BULK_LOGGED') AND @ChangeRecoveryModel = 'true'
                      begin
                          SET @SQLCmdRecovery = 'alter database ' + @DBName + ' set recovery simple with no_wait'
                          print @DBName + ' recovery model set to simple.'
                          exec (@SQLCmdRecovery)
                      end
   
                  --Index_ID of 0 is a heap index, no need to defrag
                  SELECT object_id AS ObjectID, index_id AS IndexID, partition_number AS PartitionNum, avg_fragmentation_in_percent AS Frag
                      INTO #Fragmentation
                      FROM sys.dm_db_index_physical_stats (DB_ID(@DBName), NULL, NULL , NULL, @Stats)
                      WHERE avg_fragmentation_in_percent >= @ReOrgThreshold AND avg_fragmentation_in_percent < = @MaxFrag
                          AND index_id > 0
                          AND Page_Count >= @MinPageCount AND Page_Count <= @MaxPageCount
   
                  --Inner Cursor (objects)
                  declare CurPartitions cursor
                      FOR SELECT * FROM #Fragmentation
   
                      open CurPartitions
                      fetch next FROM CurPartitions INTO @ObjectID, @IndexID, @ParititionNum, @Frag
   
                      while @@fetch_status <> -1
                          begin
                              SET @SQLCmdID = 'select @ObjectName = quotename(obj.name), @SchemaName = quotename(sch.name) from ' + @DBName +
                                  '.sys.objects as obj join ' + @DBName + '.sys.schemas as sch on sch.schema_id = obj.schema_id where obj.object_id = @ObjectID'
                              --select @ObjectName = quotename(obj.name), @SchemaName = quotename(sch.name)
                              --    from sys.objects as obj
                              --    join sys.schemas as sch on sch.schema_id = obj.schema_id
                              --    where obj.object_id = @ObjectID
                              SET @ParmDef = N'@ObjectID int, @ObjectName sysname output, @SchemaName sysname output'
                              exec sp_executesql @SQLCmdID, @ParmDef, @ObjectID= @ObjectID, @ObjectName = @ObjectName output, @SchemaName = @SchemaName output
                                  
   
                              --select @IndexName = quotename(name)
                              --    from sys.indexes
                              --    where object_id = @ObjectID and index_id = @IndexID
                              SET @SQLCmdID = 'select @IndexName = quotename(name) from ' + @DBName + '.sys.indexes where object_id = @ObjectID and index_id = @IndexID'
                              SET @ParmDef = N'@ObjectId int, @IndexId int, @IndexName sysname output'
                             exec sp_executesql @SQLCmdID, @ParmDef, @ObjectId = @ObjectId, @IndexId = @IndexId, @IndexName = @IndexName output
   
                              --select @PartitionCount = count (*)
                              --    from sys.partitions
                              --    where object_id = @ObjectID and index_id = @IndexID
                              SET @SQLCmdID = 'select @PartitionCount = count (*) from ' + @DBName + '.sys.partitions where object_id = @ObjectID and index_id = @IndexID'
                              SET @ParmDef = N'@ObjectId int, @IndexId int, @PartitionCount int output'
                              exec sp_executesql @SQLCmdID, @ParmDef, @ObjectId = @ObjectId, @IndexId = @IndexId, @PartitionCount = @PartitionCount output
   
                              --ReOrg
                              SET @SQLCmdBk = NULL
                              IF @frag < @ReBuildTheshold
                                  begin
                                      SET @SQLCmdBk = 'alter index ' + @IndexName + ' on [' + @DBName + '].' + @SchemaName + '.' + @ObjectName + ' reorganize'
                                  end
                              IF @frag >= @ReBuildTheshold
                                  begin
                                      SET @SQLCmdBk = 'alter index ' + @IndexName + ' on [' + @DBName + '].' + @SchemaName + '.' + @ObjectName + ' rebuild'
                                  end
   
                              --set options
                              IF @FillFactor IS NOT NULL SET @SQLCmdFill = 'fillfactor = ' + cast(@FillFactor AS varchar(3))+ ', '
                              IF @Online = 'true' AND @OnlineReq = 'true' SET @SQLCmdOnline = 'online = on, '
                              IF @PAD_Index = 'true' SET @SQLCmdPad = 'PAD_Index = on, '
                              IF @SortInTempDB = 'true' SET @SQLCmdSort = 'Sort_in_TempDB = on, '
   
                              IF @PartitionCount > 1 SET @SQLCmdBk = @SQLCmdBk + ' partition = ' + cast(@partitionnum AS nvarchar(10))
   
                              SET @SQLCmdWith = ' with ('
   
                              --With options only apply to rebuilds, not to re-org
                              IF @frag >= @ReBuildTheshold
                                  begin
                                      IF @SQLCmdFill IS NOT NULL SET @SQLCmdWith = @SQLCmdWith + @SQLCmdFill
                                      IF @SQLCmdOnline IS NOT NULL SET @SQLCmdWith = @SQLCmdWith + @SQLCmdOnline
                                      IF @SQLCmdPad IS NOT NULL SET @SQLCmdWith = @SQLCmdWith + @SQLCmdPad
                                      IF @SQLCmdSort IS NOT NULL SET @SQLCmdWith = @SQLCmdWith + @SQLCmdSort
                                  end
   
                              IF @SQLCmdWith <> ' with (' SET @SQLCmdWith = LEFT(@SQLCmdWith, len(@SQLCmdWith) - 1) + ')'
                              IF @SQLCmdWith <> ' with (' SET @SQLCmd = @SQLCmdBk + @SQLCmdWith
                              else SET @SQLCmd = @SQLCmdBk
   
                              --Print and execute
                              exec (@SQLCmd)
                              print @SQLCmd
   
                              fetch next FROM CurPartitions INTO @ObjectID, @IndexID, @ParititionNum, @Frag
                          end --CurPartitions
                      close CurPartitions
                      deallocate CurPartitions
                      DROP TABLE #Fragmentation
   
                      --If DB was in Full or Bulk_Logged and tlogging was disabled, then re-enable
                      IF @RecoveryModel IN ('FULL', 'BULK_LOGGED') AND @ChangeRecoveryModel = 'true'
                          begin
                              SET @SQLCmdRecovery = 'alter database ' + @DBName + ' set recovery ' + @RecoveryModel + ' with no_wait'
                              print @DBName + ' recovery model set to ' + @RecoveryModel + ' recovery model.'
                              exec (@SQLCmdRecovery)
                          end
                      IF @Exit = 'true' RETURN
   
                  fetch next FROM DatabaseNames INTO @DBName
              end --DatabaseNames
      close DatabaseNames
      deallocate DatabaseNames

 

http://snipplr.com/view/41197/index-defrag-script-210052008/


Defragment all indexes in MS SQL database

keep in mind that this action locks indexes while defragmenting ....

--defrag indexes
USE [databse_name]
GO
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO

 


 T-SQL transaction in begin/try block example (for MS SQL 2005+)

BEGIN TRY
	BEGIN TRAN
		--execute SQL which include delete/insert/update and could go wrong
		INSERT INTO table (a) VALUES (1)
	COMMIT TRAN
END TRY
BEGIN CATCH
	--in case of SQL error do ROLLBACK and execute what you want
	SELECT error_number() as ErrorNumber, error_message() as ErrorMsg
	PRINT ('transaction rolled back')
	ROLLBACK TRAN
END CATCH

 


Show table space usage with this T-SQL query

 

SELECT
  X.[name],
  REPLACE(CONVERT(varchar, CONVERT(money, X.[rows]), 1), '.00', '') AS [rows],
  REPLACE(CONVERT(varchar, CONVERT(money, X.[reserved]), 1), '.00', '') AS [reserved],
  REPLACE(CONVERT(varchar, CONVERT(money, X.[data]), 1), '.00', '') AS [data],
  REPLACE(CONVERT(varchar, CONVERT(money, X.[index_size]), 1), '.00', '') AS [index_size],
  REPLACE(CONVERT(varchar, CONVERT(money, X.[unused]), 1), '.00', '') AS [unused]
FROM
  (
  SELECT 
    CAST(object_name(id) AS varchar(50)) AS [name],
    SUM(CASE WHEN indid < 2 THEN CONVERT(bigint, [rows]) END) AS [rows],
    SUM(CONVERT(bigint, reserved)) * 8 AS reserved,
    SUM(CONVERT(bigint, dpages)) * 8 AS data,
    SUM(CONVERT(bigint, used) - CONVERT(bigint, dpages)) * 8 AS index_size,
    SUM(CONVERT(bigint, reserved) - CONVERT(bigint, used)) * 8 AS unused
  FROM sysindexes WITH (NOLOCK)
  WHERE sysindexes.indid IN (0, 1, 255)
    AND sysindexes.id > 100
    AND object_name(sysindexes.id) <> 'dtproperties'
  GROUP BY sysindexes.id WITH ROLLUP
  ) AS X
ORDER BY X.[name]

 


Reset autoindent number to new value, if value is 15 next inserted value will be 16!

 

DBCC CHECKIDENT (TableName, RESEED, 15)

 


Example of truncating all tables in database

 

EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'

 


Find which store procedure have some text (FindText)

 

SELECT DISTINCT SO.NAME
FROM SYSOBJECTS SO (NOLOCK)
  INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
  AND SO.Type = 'P'
  AND SC.Text LIKE '%FindText%'
ORDER BY SO.Name

 


Example of update with subquery, subquery should return only one result!

 

UPDATE person
SET name = (SELECT naziv
            FROM country c INNER JOIN person p ON c.idDrzava=p.id 
            WHERE idDrzava = p.id)
WHERE name IS NULL

 


How to enable Full-Text index on MS SQL server

 

USE DatabaseName
GO
EXEC sp_fulltext_database 'enable'
GO
CREATE FULLTEXT CATALOG FTS_DatabaseName

 

How to create Full-Text index on column in MS SQL database

 

CREATE FULLTEXT INDEX ON DatabaseName.dbo.TableName
(
  ColumnName
  Language 1033
)
KEY INDEX IndexName;

 

How to delete all character except alfanumerics, - , _ and space

 

ALTER FUNCTION [dbo].[fnRemoveBadCharacter]
(
    @BadString nvarchar(20)
)
RETURNS nvarchar(20)
AS
BEGIN
            DECLARE @nPos INTEGER
            SELECT @nPos = PATINDEX('%[^a-zA-Z0-9_ -]%', @BadString)

            WHILE @nPos > 0
            BEGIN
                        SELECT @BadString = STUFF(@BadString, @nPos, 1, '')
                        SELECT @nPos = PATINDEX('%[^a-zA-Z0-9_ -]%', @BadString)
            END

            RETURN @BadString
END

--select dbo.fnRemoveBadCharacter('15 - 29 ()  ')

 


How to truncate transaction log which you are unable to truncate :)

In my case log could not be truncated because replication left some transaction open in log
here is what I've done

 

USE MyDB
GO

--what is in transaction log
DBCC LOGINFO('MyDB')
GO

--are there any active transactions?
DBCC opentran (MyDB)
GO

--this will DROP ALL transacions in log
exec sp_repldone null, null, 0, 0, 1

--create checkpoint
CHECKPOINT

--shrink log
DBCC shrinkfile (MyDB_log, 0)

How to fetch all rows which are child from parent row (specified in @Id) and certain level

 

DECLARE @Id int=4;
WITH Hierachy(IdKategorija, nadkategorija, naziv, Level)
AS
(
    SELECT IdKategorija, nadkategorija, naziv, 0 as Level
    FROM kategorija c
    WHERE c.IdKategorija = @Id -- insert parameter here
    
    UNION ALL
    
    SELECT c.IdKategorija, c.nadkategorija, c.naziv, ch.Level + 1
    FROM kategorija c
      INNER JOIN Hierachy ch ON c.nadkategorija = ch.IdKategorija
)
 
SELECT IdKategorija FROM Kategorija WHERE IdKategorija in 
  (SELECT IdKategorija FROM Hierachy WHERE Level >= 2)


How to fetch all rows which are child from parent row (specified in @Id) and certain level

Pivot for cross-tabulation reports

SELECT *
FROM
  (SELECT IDKategorija, Vrijednost, IDPolja 
     FROM KategorijaCustom ) AS source PIVOT
       (
         max(vrijednost)
         FOR IDPolja in ([24],[25],[26],[564]
       )
   ) as pvt

How to kill all processess that lock one database (so it can be restored or something)

 

DECLARE @DatabaseName nvarchar(50)
DECLARE @SPId int
DECLARE @SQL nvarchar(100)

SET @DatabaseName = N'DatabaseName'
DECLARE my_cursor CURSOR FAST_FORWARD FOR
SELECT SPId FROM MASTER..SysProcesses
WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId

OPEN my_cursor

FETCH NEXT FROM my_cursor INTO @SPId

WHILE @@FETCH_STATUS = 0
BEGIN
 SET @SQL = 'KILL ' + CAST(@SPId as nvarchar(10))
 print @SQL
 EXEC sp_executeSQL @SQL
 --KILL @SPId -- Causing Incorrect syntax near '@spid'.

 FETCH NEXT FROM my_cursor INTO @SPId
END

CLOSE my_cursor
DEALLOCATE my_cursor

 


Show how the word is split in full text search

 

SELECT * FROM sys.dm_fts_parser ('-9-', 1050,0,0)

How  to restore database that is stuck at recovering

 

RESTORE DATABASE  WITH RECOVERY

 

 


How to make your CLR functions expecially Regular expression replace, here is example with every step, for more info see this site

Here is all steps for copy & paste, you need .NET framework 2 or any higher but path will change then.

1. make function in C# or VB (my examples are in VB.NET), create regexReplace.vb file i c:\ in any text editor (notepad is fine). I choose C:\ for simplicity you can choose any other directory

 

Imports System
Imports System.Data.Sql
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlTypes
Imports System.Runtime.InteropServices
Imports System.Text.RegularExpressions
Imports System.Collections
 
Namespace Globaldizajn.TSql
    Public Class RegularExpressionFunctions
         
        Public Shared Function RegExReplace( ByVal pattern As SqlString, ByVal input As SqlString, 
ByVal replacement  As SqlString) As SqlString
            Dim rgx As Regex
            Dim result As SqlString = input
            
            try
                rgx = New Regex(pattern)
                result = rgx.Replace(input, replacement)
            catch ex As Exception
            end try

            Return result
        End Function
         
        Public Shared Function regexContain(ByVal text As SqlString, ByVal pattern As SqlString)
 As SqlInt32
            Dim ret As SqlInt32 = 0

            Try
                Dim TextStr As String = text.ToString()
                Dim PatternStr As String = pattern.ToString()
                Dim mc As MatchCollection = Regex.Matches(TextStr, PatternStr)

                If mc.Count > 0 Then ret = 1
            Catch
                ret = 0
            End Try

            Return ret
        End Function
    End Class
End Namespace

 

2. compile regexReplace.vb

cd c:\

c:\Windows\Microsoft.NET\Framework64\v2.0.50727\vbc.exe /t:library C:\regexReplace.vb

3. copy regexReplace.dll on MS-SQL server, I copy dll on C:\windows directory on MS-SQL server

4. open Microsoft SQL Server Management Studio, choose database where you want to have function installed. Open new query end copy this

 

sp_configure 'clr enabled', 1
RECONFIGURE WITH OVERRIDE
 
IF EXISTS ( SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.RegExReplace') )
   DROP FUNCTION dbo.RegExReplace
GO
 
IF EXISTS ( SELECT 1 FROM sys.assemblies asms WHERE asms.name = N'RegExFunction' )
   DROP ASSEMBLY [RegExFunction]
 
CREATE ASSEMBLY RegExFunction FROM 'c:\windows\RegExReplace.DLL'
GO
 
CREATE FUNCTION RegExReplace
   (
    @Pattern NVARCHAR(4000),
    @Input NVARCHAR(MAX),
    @replacement NVARCHAR(4000)
   )
RETURNS  NVARCHAR(MAX)
AS EXTERNAL NAME
   RegExFunction.[Globaldizajn.TSql.RegularExpressionFunctions].RegExReplace
GO

 

This ih usage example:

 

SELECT dbo.RegExReplace('<.*?>', Field1, '') 
FROM Table1

 

I create this function on master table and use it everywhere like this

 

SELECT master.dbo.RegExReplace('<.*?>', Field1, '') 
FROM Table1

 


 

How to search string in Store Procedure

taken from here

 

USE AdventureWorks
GO

--Searching for Empoloyee table
SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Employee%'
GO

--Searching for Empoloyee table and RateChangeDate column together
SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Employee%'
  AND OBJECT_DEFINITION(OBJECT_ID) LIKE '%RateChangeDate%'
GO

 

 


How to disable / enable in fulltext search accent sensitivity (čć -> c)

 

CREATE FULLTEXT CATALOG AwCat WITH ACCENT_SENSITIVITY=OFF
GO

 

ALTER FULLTEXT CATALOG AwCat REBUILD WITH ACCENT_SENSITIVITY=ON
GO

 

 


How to create login (with RW permissions) on MS SQL Server which can see only one database when logged

 

--create login
USE [master]
CREATE LOGIN [MyUser] WITH PASSWORD=N'MyPassword', DEFAULT_DATABASE=[MyDB], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
GO

--deny all to login
DENY VIEW ANY DATABASE to [MyUser]
GO

--add RW permissions for MyUser to access MyDB
ALTER AUTHORIZATION ON DATABASE:: [MyDB] to [MyUser]
GO

 

 


Complex update SQL query, inner join and subquery interconnected

 

UPDATE a SET stanje = s.raspolozivo
FROM artikli a
INNER JOIN (
 SELECT stanje.artikl_id, sum(stanje.raspolozivo) as raspolozivo
 FROM [Maras].[dbo].[artikli]
 INNER JOIN stanje ON stanje.artikl_id = artikli.id
 GROUP BY stanje.artikl_id
) s ON s.artikl_id = a.id

 


How to create user with access to only one database

user MUST BE A OWNER othervise user will not see any database even he will be able to enter database and use it.

create LOGIN [MyLogin] WITH PASSWORD='MyPassword', CHECK_POLICY = OFF;
USE master;
GO
DENY VIEW ANY DATABASE TO [MyLogin]; 
USE master;
GO
ALTER AUTHORIZATION ON DATABASE::[MyDatabase] TO [MyLogin];
GO
-- ignore this, this is if you need select to table access
GRANT SELECT ON [dbo].[tableName] to [MyLogin]

 

 


If Store procedure is timeout from .NET code but from SSMS it executes fast

ARITHABORT helps sometimes :)

 

SET ARITHABORT ON;
EXEC sp_MyStoreProcedure ...

 

 


How to do paging  in MS SQL 2012, easy way

 

SELECT MyField 
FROM MyTable
ORDER BY MyField
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

 


How to create 2 login one for read-only and another with read-write persmission to database using T-SQL

this will create 2 logins for given database DBName 2 logins

1. "DBNameRO" with password ".DBName" for read only access
2. "DBNameRW" with password ".DBName" for read write access

DECLARE @baza as nvarchar(1000)='DBName'
DECLARE @sql as nvarchar(4000)='EXEC sp_addlogin ''' + @baza + 'RW'', ''.' + @baza + ''', ''' +  @baza + '''; USE [' + @baza + '] CREATE USER [' + @baza + 'RW] FOR LOGIN [' + @baza + 'RW]; EXEC sp_addrolemember ''db_datawriter'', ''' + @baza + 'RW''; EXEC sp_addrolemember ''db_datareader'', ''' + @baza + 'RW'';'
DECLARE @sql2 as nvarchar(4000)='EXEC sp_addlogin ''' + @baza + 'RO'', ''.' + @baza + ''', ''' +  @baza + '''; USE [' + @baza + '] CREATE USER [' + @baza + 'RO] FOR LOGIN [' + @baza + 'RO]; EXEC sp_addrolemember ''db_datareader'', ''' + @baza + 'RO'''
--SELECT @sql union SELECT @sql2
EXEC sp_executesql @sql
EXEC sp_executesql @sql2

 


How to defrag index on mssql

 

USE [DBName]
GO
EXEC sp_MSforeachtable @command1="print '?' DBCC INDEXDEFRAG  ('?', ' ', 80)"
GO
EXEC sp_updatestats
GO 

How to get all IDs form children records

 

DECLARE @Id int=19;

WITH Hierachy(Idkategorija, nadkategorija, Level)
AS
(
   SELECT Idkategorija, nadkategorija, 0 AS Level
   FROM kategorija c
   WHERE c.Idkategorija = @Id

   UNION ALL

   SELECT c.Idkategorija, c.nadkategorija, ch.Level + 1
   FROM kategorija c
     INNER JOIN Hierachy ch ON c.nadkategorija = ch.Idkategorija
)

SELECT Idkategorija, nadkategorija, level FROM Hierachy

 


How to grant permission to only one store procedure inside database to user

here is example of t-sql

 

USE Cinestar2; 
GRANT EXECUTE ON OBJECT::dbo.proc_AddRemoveNewsletter
    TO sternum;
GO

 

To grant permissions on a stored procedure (from MS site)

  1. In Object Explorer, connect to an instance of Database Engine and then expand that instance.
  2. Expand Databases, expand the database in which the procedure belongs, and then expand Programmability.
  3. Expand Stored Procedures, right-click the procedure to grant permissions on, and then click Properties.
  4. From Stored Procedure Properties, select the Permissions page.
  5. To grant permissions to a user, database role, or application role, click Search.
  6. In Select Users or Roles, click Object Types to add or clear the users and roles you want.
  7. Click Browse to display the list of users or roles. Select the users or roles to whom permissions should be granted.
  8. In the Explicit Permissions grid, select the permissions to grant to the specified user or role. For a description of the permissions, see Permissions (Database Engine).

 


How to display (show) all user owned object for a single datatbase

Show what principal MyUser owns

SELECT DBPrincipal_2.name as role, DBPrincipal_1.name as owner 
FROM sys.database_principals as DBPrincipal_1 
  INNER JOIN sys.database_principals as DBPrincipal_2 ON DBPrincipal_1.principal_id = DBPrincipal_2.owning_principal_id 
WHERE DBPrincipal_1.name = 'MyUser'


Or try this (from stackexchange)

WITH objects_cte AS
(
    SELECT
        o.name,
        o.type_desc,
        CASE
            when o.principal_id is null then s.principal_id
            else o.principal_id
        END AS principal_id
    FROM sys.objects o
        INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
    WHERE o.is_ms_shipped = 0
        AND o.type in ('U', 'FN', 'FS', 'FT', 'IF', 'P', 'PC', 'TA', 'TF', 'TR', 'V')
)
SELECT
    cte.name,
    cte.type_desc,
    dp.name
FROM objects_cte cte
    INNER JOIN sys.database_principals dp on cte.principal_id = dp.principal_id
WHERE dp.name = 'MyUser';

 

Change schema db_owner to dbo

ALTER AUTHORIZATION ON SCHEMA::db_owner TO dbo;

 


How to count number of recursions from SQL also depth is displayed if you want to use SELECT * instead od SELECT COUNT(*)

table looks like this:
table name: category

category.ID
category.ParentID
category.name

category.ParentID have ID of parrent category
 

WITH recur AS (
  SELECT ID,ParentID,0 as depth FROM category
  UNION ALL
  SELECT r1.Id,r2.ParentID,r1.depth + 1 FROM recur r1
    INNER JOIN category r2 ON (r1.ParentID=r2.ID)
)
--show number of recusive count for category.ID=5
SELECT count(*) FROM recur WHERE ParentID = 5

 


How to get rid of error 'Replication-Replication Distribution Subsystem: agent (null) failed. The publication  does not exist.' Error: 14151, Severity: 18: State: 1.

this is job that is still running even there is no that replication any more, this is how I fix this

1. find job NAME by searching job command, if your job was 'MyDB_version3'

USE master;
GO

SELECT job.job_id,name,command
FROM msdb.dbo.sysjobs job 
  INNER JOIN msdb.dbo.sysjobsteps steps ON job.job_id = steps.job_id
WHERE job.enabled = 1 AND command LIKE '%MyDB%'

2. This SQL will return 1 or more jobs, analyze command field and find job_ID and NAME, after you find problematic job delete it:

USE msdb;
GO

EXEC sp_delete_job  @job_name = N'YourJobName';
GO

 


How to show only databases which have full model

 

SELECT name,  
       DATABASEPROPERTYEX(name, 'Recovery') as model, 
       DATABASEPROPERTYEX(name, 'Status') as Status
FROM   master.dbo.sysdatabases 
WHERE DATABASEPROPERTYEX(name, 'Recovery')='FULL'
ORDER BY name

 


How to delete user from database (not login from server) and add user to database and set it as db_owner

this will drop user for DB, recreate user for login and set that user as db_owner

 

USE [myDB]; 
GO

DROP USER myUser; 
CREATE USER myUser FOR LOGIN myUser;

EXEC sp_addrolemember 'db_owner', myUser; 
PRINT 'MyDB user add';

 


T-SQL that create script to recreate DBuser for all databases (not login)

 

EXEC sp_msForEachDB '
PRINT ''
	USE [?]; 
	DROP USER myUser; 
	CREATE USER myUser FOR LOGIN myLogin; 
	EXEC sp_addrolemember ''''db_owner'''', myUser;   '' 
'

 


How to calculate real disk size that DB uses

Calculate DISK usage for whole DB and per table, sometime MS SQL under reports->disk usage show different size then reports->disk usage by top tables  this show real size per table in MB.

 

IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t;
IF OBJECT_ID('tempdb..#tt') IS NOT NULL DROP TABLE #tt;

CREATE TABLE #t(
	name varchar(100),
	rows int,
	reserved varchar(100),
	data varchar(100),
	index_size varchar(100),
	unused varchar(100)
);
CREATE TABLE #tt(
	name varchar(100),
	rows int,
	reserved int,
	data int,
	index_size int,
	unused int
);

EXEC sp_msforeachtable 'INSERT INTO #t (name,rows,reserved,data,index_size,unused) EXEC sp_spaceused [?]';

INSERT INTO #tt (name,rows,reserved,data,index_size,unused)
SELECT name
	,CAST(REPLACE(rows,' KB','000') AS int)
	,CAST(REPLACE(reserved,' KB','000') AS int)
	,CAST(REPLACE(data,' KB','000') AS int)
	,CAST(REPLACE(index_size,' KB','000') AS int)
	,CAST(REPLACE(unused,' KB','000') AS int)
FROM #t

--per table size rounded to discard decimal value
SELECT name, rows, CAST(CAST(ROUND(data/1000000.0,2) as numeric(15,2)) as varchar(100)) + ' MB' AS DataSizeMB, 
CAST(CAST(ROUND(reserved/1000000.0,2) as numeric(15,2)) as varchar(100)) + ' MB' AS DiskUsageMB
FROM #tt ORDER BY 4 DESC

--total DB sum
SELECT CAST(CAST(ROUND(SUM(reserved) / 1000000.0,2) as numeric(15,2)) as varchar(20)) + ' MB' AS TotalDBDiskSize,
CAST(CAST(ROUND(SUM(data) / 1000000.0,2) as numeric(15,2)) as varchar(20)) + ' MB' AS TotalDataDBSize
FROM #tt

 


Recursive function get all childs from parent ID

Get all ChildID from RootID, us it like this:
SELECT * FROM GetChildes(10)

IDKategorija - ID of category
NadKategorija - parentID
Kategorija - table category

CREATE FUNCTION GetChildes
(
	@root int
)
RETURNS @Result TABLE  (id int)
AS
BEGIN

  WITH cte AS 
  (
    SELECT a.IDKategorija , a.nadkategorija
    FROM Kategorija a
    WHERE IDKategorija = @root 
    UNION ALL
    SELECT a.IDKategorija , a.nadkategorija
    FROM Kategorija a 
      INNER JOIN cte c ON a.nadkategorija = c.IDKategorija 
  )

  INSERT INTO @Result
  SELECT IDKategorija 
  FROM cte

  RETURN

END

 


How to create script for backuping database from batch file

If you have SQL Express than this is easy solution just create backup.bat file which call from Tash Scheduler, you must have sqlcmd in system path variable or use absolute path to sqlcmd.

backup.bat:

sqlcmd -S localhost -E -Q "EXEC sp_BackupDatabases @backupLocation='C:\sql_backup\', @backupType='F', @databaseName='MyDB'" 

 


How to find out what characeter to replace

Since croatian is my native, conversion from mysql to tsql always ends up wrong. Specific croatian letters are replace with weird unreadable characters. Here is script which I use to replace those characters. This is how I hunt for broken characters.

1. find table in which is broken text in my case kategorija
2. if weird text is between 398 and 420 character then display only them.

 

DECLARE @wcount as int
DECLARE @index as int
DECLARE @len as int
DECLARE @char as nchar
DECLARE @string as nvarchar(max)

SELECT @string = opis FROM kategorija WHERE idkategorija = 104
SET @wcount= 0
SET @index = 398 --this is start of weird chars
SET @len=420 --end

WHILE @index <= @len
BEGIN
	SET @char = SUBSTRING(@string, @index, 1)
	SELECT @index AS pozicija, UNICODE (@char) AS ASCII, @char
	SELECT @index = @index + 1 
END

replace characters with

 

UPDATE kategorija SET RowName = replace( RowName, nchar(num1) + nchar(num2), 'ć') WHERE ID = 123

 


How to capture result from store procedure

Here is example to catch sp_who and sort it out

 

SELECT * INTO #t FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;', 'EXEC sp_who')
SELECT * FROM #t 
WHERE dbname LIKE 'm%'
--show only DB that start with 'm'
DROP TABLE #t


How to search database (all tables and all columns) for some text

Here is exaple of StoreProcedure which will search every column in every table for some test, as result it will show table name, column name, value

 

CREATE PROC FindAllOccurrenceOfText
@trazi as varchar(max)
AS
/*
 written by kosc for damir.globaldizajn.hr
 this SP search all text columns in DB for text

 usage:
   EXEC FindAllOccurrenceOfText 'comp'
   
 returns:
   TableName, ColumnName, Value

*/
BEGIN
	IF OBJECT_ID('tempdb..#r') IS NOT NULL DROP TABLE #r	
	CREATE TABLE #r (tablica varchar(max), kolumna varchar(max), vrijednost varchar(max))
	DECLARE @s TABLE (tablica varchar(max), kolumna varchar(max), upit varchar(max) )
	DECLARE @t TABLE (tablica varchar(max), rn int primary key identity )	

	INSERT INTO @t
	SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

	INSERT INTO @s
	SELECT COLUMN_NAME,TABLE_NAME
		,'INSERT INTO #r SELECT ''' + QUOTENAME(TABLE_NAME) + 
		''',''' + QUOTENAME(COLUMN_NAME) + ''',' + QUOTENAME(COLUMN_NAME) + 
		' FROM ' +  QUOTENAME(TABLE_NAME) + ' WHERE ' +  QUOTENAME(COLUMN_NAME) + ' LIKE ''%' + @trazi + '%'''
	FROM    INFORMATION_SCHEMA.COLUMNS
	WHERE (DATA_TYPE = 'nvarchar' OR DATA_TYPE = 'varchar' 
			OR DATA_TYPE = 'nchar' OR DATA_TYPE = 'char' )
		AND TABLE_NAME IN (SELECT tablica FROM @t)

	DECLARE @tablica varchar(max), @kolumna varchar(max), @upit varchar(max)

	DECLARE crs CURSOR 
		LOCAL STATIC READ_ONLY FORWARD_ONLY
	FOR 
	SELECT tablica,kolumna,upit FROM @s

	OPEN crs
	FETCH NEXT FROM crs INTO @tablica,@kolumna,@upit
	WHILE @@FETCH_STATUS = 0
	BEGIN 
		--PRINT @upit
		INSERT INTO #r
		EXEC (@upit)
	    
		FETCH NEXT FROM crs INTO @tablica,@kolumna,@upit
	END
	CLOSE crs
	DEALLOCATE crs


	SELECT * FROM #r

END

 


How to add login/user to access only one view in your DB

First disable user access to view all DBs then create login and bind user -> login in your DB

 

CREATE LOGIN MyLogin WITH PASSWORD = 'MyPass'
GO

USE MyDB
GO

--note that login and user CAN be same!
CREATE USER MyUser FOR LOGIN MyLogin
GO

GRANT SELECT ON MyView TO MyUser
GO

 


How to insert huge excel file to SQL server

Access/Excell drivers need to be installed on server
 

SELECT * INTO [TableName] FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=C:\myExcel.xlsx', [NameOfSheet$])

 


How to "convert" table defitnition to Class  with properties like table definition

in this example table name is Congress.

declare @TableName sysname = 'Congress'
declare @prop varchar(max)
PRINT 'Public Class ' + @TableName
declare props cursor for
select distinct ' public property ' + ColumnName + ' AS ' + ColumnType AS prop
from ( 
    select  
        replace(col.name, ' ', '_') ColumnName,  column_id, 
        case typ.name  
            when 'bigint' then 'long' 
            when 'binary' then 'byte[]' 
            when 'bit' then 'boolean' 
            when 'char' then 'string' 
            when 'date' then 'DateTime' 
            when 'datetime' then 'DateTime' 
            when 'datetime2' then 'DateTime' 
            when 'datetimeoffset' then 'DateTimeOffset' 
            when 'decimal' then 'decimal' 
            when 'float' then 'float' 
            when 'image' then 'byte[]' 
            when 'int' then 'integer' 
            when 'money' then 'decimal' 
            when 'nchar' then 'char' 
            when 'ntext' then 'string' 
            when 'numeric' then 'decimal' 
            when 'nvarchar' then 'string' 
            when 'real' then 'double' 
            when 'smalldatetime' then 'DateTime' 
            when 'smallint' then 'short' 
            when 'smallmoney' then 'decimal' 
            when 'text' then 'string' 
            when 'time' then 'TimeSpan' 
            when 'timestamp' then 'DateTime' 
            when 'tinyint' then 'byte' 
            when 'uniqueidentifier' then 'Guid' 
            when 'varbinary' then 'byte[]' 
            when 'varchar' then 'string' 
        end ColumnType 
    from sys.columns col join sys.types typ on col.system_type_id = typ.system_type_id 
    where object_id = object_id(@TableName) 
) t 
order by prop
open props
FETCH NEXT FROM props INTO @prop
WHILE @@FETCH_STATUS = 0
BEGIN
    print @prop
    FETCH NEXT FROM props INTO @prop
END
close props
DEALLOCATE props
PRINT 'End Class'

How to force SQL server to take date format in d.m.y

set dateformat dmy

 


How to create class from SQL table

 

DECLARE @TableName sysname = 'Congress'
DECLARE @prop varchar(max)
PRINT 'Public Class ' + @TableName
DECLARE props cursor for
SELECT DISTINCT ' public property ' + ColumnName + ' AS ' + ColumnType AS prop
from ( 
    SELECT  
        replace(col.name, ' ', '_') ColumnName,  column_id, 
        CASE typ.name  
            when 'bigint' then 'long' 
            when 'binary' then 'byte[]' 
            when 'bit' then 'boolean' 
            when 'char' then 'string' 
            when 'date' then 'DateTime' 
            when 'datetime' then 'DateTime' 
            when 'datetime2' then 'DateTime' 
            when 'datetimeoffset' then 'DateTimeOffset' 
            when 'decimal' then 'decimal' 
            when 'float' then 'float' 
            when 'image' then 'byte[]' 
            when 'int' then 'integer' 
            when 'money' then 'decimal' 
            when 'nchar' then 'char' 
            when 'ntext' then 'string' 
            when 'numeric' then 'decimal' 
            when 'nvarchar' then 'string' 
            when 'real' then 'double' 
            when 'smalldatetime' then 'DateTime' 
            when 'smallint' then 'short' 
            when 'smallmoney' then 'decimal' 
            when 'text' then 'string' 
            when 'time' then 'TimeSpan' 
            when 'timestamp' then 'DateTime' 
            when 'tinyint' then 'byte' 
            when 'uniqueidentifier' then 'Guid' 
            when 'varbinary' then 'byte[]' 
            when 'varchar' then 'string' 
        END ColumnType 
    from sys.columns col join sys.types typ on col.system_type_id = typ.system_type_id 
    where object_id = object_id(@TableName) 
) t 
order by prop
open props
FETCH NEXT FROM props INTO @prop
WHILE @@FETCH_STATUS = 0

BEGIN
    print @prop
    FETCH NEXT FROM props INTO @prop
END

close props

DEALLOCATE props
PRINT 'End Class'

 


How to alter all tables/Columns in database to another collation

in this example to Croatian_CI_AI

 

DECLARE @collate nvarchar(100);
DECLARE @table nvarchar(255);
DECLARE @column_name nvarchar(255);
DECLARE @column_id int;
DECLARE @data_type nvarchar(255);
DECLARE @max_length int;
DECLARE @row_id int;
DECLARE @sql nvarchar(max);
DECLARE @sql_column nvarchar(max);

SET @collate = 'Croatian_CI_AI';

DECLARE local_table_cursor CURSOR FOR

SELECT [name]
FROM sysobjects
WHERE OBJECTPROPERTY(id, N'IsUserTable') = 1

OPEN local_table_cursor
FETCH NEXT FROM local_table_cursor
INTO @table

WHILE @@FETCH_STATUS = 0
BEGIN

    DECLARE local_change_cursor CURSOR FOR

    SELECT ROW_NUMBER() OVER (ORDER BY c.column_id) AS row_id
        , c.name column_name
        , t.Name data_type
        , c.max_length
        , c.column_id
    FROM sys.columns c
    JOIN sys.types t ON c.system_type_id = t.system_type_id
    LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
    LEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
    WHERE c.object_id = OBJECT_ID(@table)
    ORDER BY c.column_id

    OPEN local_change_cursor
    FETCH NEXT FROM local_change_cursor
    INTO @row_id, @column_name, @data_type, @max_length, @column_id

    WHILE @@FETCH_STATUS = 0
    BEGIN

        IF (@max_length = -1) OR (@max_length > 4000) SET @max_length = 4000;

        IF (@data_type LIKE '%char%')
        BEGIN TRY
            SET @sql = 'ALTER TABLE ' + @table + ' ALTER COLUMN ' + @column_name + ' ' + @data_type + '(' + CAST(@max_length AS nvarchar(100)) + ') COLLATE ' + @collate
            PRINT @sql
            EXEC sp_executesql @sql
        END TRY
        BEGIN CATCH
          PRINT 'ERROR: Some index or constraint rely on the column' + @column_name + '. No conversion possible.'
          PRINT @sql
        END CATCH

        FETCH NEXT FROM local_change_cursor
        INTO @row_id, @column_name, @data_type, @max_length, @column_id

    END

    CLOSE local_change_cursor
    DEALLOCATE local_change_cursor

    FETCH NEXT FROM local_table_cursor
    INTO @table

END

CLOSE local_table_cursor
DEALLOCATE local_table_cursor

GO

 


How to find which database have highest queries by total CPU

 

SELECT TOP 100
    qs.total_worker_time/(qs.execution_count*60000000) as [Minutes Avg CPU Time],    
    qs.execution_count as [Times Run],
    qs.min_worker_time/60000000 as [CPU Time in Mins],
    SUBSTRING(qt.text,qs.statement_start_offset/2,
    (case when qs.statement_end_offset = -1 then len(convert(nvarchar(max), qt.text)) * 2
     else qs.statement_end_offset end -qs.statement_start_offset)/2) as [Query Text],
    db_name(qt.dbid) as [Database],
    object_name(qt.objectid) as [Object Name]
FROM sys.dm_exec_query_stats qs cross apply
     sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Minutes Avg CPU Time] DESC

 


How to search in all databases one column name

 

IF OBJECT_ID('tempdb..#t') IS NOT NULL
    DROP TABLE #t
else
    CREATE TABLE #t
    (
      Table_Catalog varchar(100)
      ,Table_Schema varchar(100)
      ,Table_Name varchar(100)
      ,Column_Name varchar(100)
      ,Data_Type varchar(100)
      ,Character_Maximum_Length int
    );
GO

exec sp_MSForEachDB @command1='USE [?];
INSERT INTO #t
SELECT 
    Table_Catalog 
    ,Table_Schema
    ,Table_Name
    ,Column_Name
    ,Data_Type
    ,Character_Maximum_Length
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME like ''%columnName%'''
GO

SELECT * FROM #t;

 


How to search in all databases one specific Function name or Store procedure name (TSQL)

 

IF OBJECT_ID('tempdb..#t') IS NOT NULL
    DROP TABLE #t
else
    CREATE TABLE #t
    (
      DBname varchar(200), 
      name varchar(200), 
      schema_name varchar(100), 
      type_desc varchar(50)
    );
GO

exec sp_MSForEachDB @command1='USE [?];
INSERT INTO #T (DBname, name, schema_name, type_desc) 
SELECT ''[?]'' AS DBname
  ,name 
  ,SCHEMA_NAME(schema_id) AS schema_name
  ,type_desc
FROM sys.objects
WHERE (type_desc LIKE ''%FUNCTION%'' or type_desc LIKE ''%PROCEDURE%'')
  AND name like ''%name2Search%'' ';
GO

SELECT * FROM #t;

 


How to list all tables and all field in a database

 

SELECT T.name AS Table_Name ,
       C.name AS Column_Name ,
       P.name AS Data_Type ,
       P.max_length AS Size ,
       CAST(P.precision AS VARCHAR) + '/' + CAST(P.scale AS VARCHAR) AS Precision_Scale
FROM   sys.objects AS T
       JOIN sys.columns AS C ON T.object_id = C.object_id
       JOIN sys.types AS P ON C.system_type_id = P.system_type_id
WHERE  T.type_desc = 'USER_TABLE';

 


UPDATE one table from same database with another name

When I fu*ed up something and I need to restore DB with another name a "copy" name from backup to production DB, ID is link

UPDATE p 
SET p.Name = pp.name
FROM OriginalDB.dbo.Products p, BackupDB.dbo.Products pp
WHERE p.id = pp.id AND p.name<>pp.name

 


Regular expression match function for MS Sql Server (from stack overflow)

tested on 2012R2

 

1. create file: regexpMatch.vb and copy this:

Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Text.RegularExpressions

Namespace CLR_Functions
    Public Class myFunctions
       
        Public Shared Function RegexContain(ByVal text As SqlString, ByVal pattern As SqlString) As SqlInt16
            Dim returnVal As SqlInt16 = 0

            Try
                Dim myText As String = text.ToString()
                Dim myPattern As String = pattern.ToString()
                Dim mc As MatchCollection = Regex.Matches(myText, myPattern)

                If mc.Count > 0 Then
                    returnVal = 1
                End If

            Catch
                returnVal = 0
            End Try

            Return returnVal
        End Function
    End Class
End Namespace

 

 

2. compile it with vbc.exe in my example

"c:\Program Files (x86)\Microsoft Visual Studio\2017\Professional\MSBuild\15.0\Bin\Roslyn\vbc.exe" /t:library regexpMatch.vb

 

3. copy regexpMatch.dll to SQL server C:\windows directory

 

4. open SQL server management studio

sp_configure 'clr enabled', 1
RECONFIGURE WITH OVERRIDE

EXEC sp_configure 'show advanced options', 1
RECONFIGURE;

EXEC sp_configure 'clr strict security', 0;
RECONFIGURE;


IF EXISTS ( SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.regexContain') )
   DROP FUNCTION dbo.regexContain
GO

IF EXISTS ( SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.regexContain') )
   DROP FUNCTION dbo.regexContain
GO
 
IF EXISTS ( SELECT 1 FROM sys.assemblies asms WHERE asms.name = N'regexContainFun' )
   DROP ASSEMBLY regexContainFun
GO

CREATE ASSEMBLY regexContainFun FROM 'c:\windows\regexContain.dll'
GO

CREATE FUNCTION regexContain
(
	@text NVARCHAR(4000),
	@pattern NVARCHAR(4000)
)
RETURNS int                      
AS EXTERNAL NAME
   regexContainFun.[Globaldizajn.TSql.RegularExpressionFunctions].regexContain
GO

 

5. How to use it 

SELECT *
FROM MyTable
WHERE master.dbo.regexContain(Name,'Ausa') = 1

 


Your MSDBData.mdf is big or huge or gigantic ? Your SQL installation is old ?

There could be a lot of backupfiles entries in MDSB

 

Find out which tables are problematic

USE msdb;
GO

SELECT OBJECT_NAME(object_id) As TableName, SUM(rows) AS NumberOfRows
FROM sys.partitions
WHERE index_id IN (0, 1)
GROUP BY object_id
ORDER BY NumberOfRows DESC;

 

If its backupfile and backupfilegroup delete old backup

EXEC sp_delete_backuphistory @oldest_date = '01/01/2020';

 


Enable Full Text Search if it's grayed out in SSMS(SQL Server Management Studio)

Sometimes Full Text Search is grayed out (in SSMS v18 and higher) and you need to explicitly enable it

 

USE [MyDB];
EXEC sp_fulltext_database 'enable';

 


TSql function for capitalizing first letter in sentence, for each sentance in string

First letter in sentence is capital all other are lowered, if you do not want to alter other letters then change:

SET @r = @r + LOWER(SUBSTRING(@str, @i, 1))

to

SET @r = @r + SUBSTRING(@str, @i, 1)

CREATE FUNCTION [dbo].[CapitalizeFirstLetterInSentance]
(
	@str VARCHAR(1000)--increase the variable size depending on your needs.
)
RETURNS VARCHAR(1000)
AS
BEGIN
	DECLARE @br INT = 0;
	DECLARE @i INT = 1;
	DECLARE @len INT = len(@str);
	DECLARE @karakter varchar(10)='';
	DECLARE @t varchar(10);
	DECLARE @LastIsDot INT = 1;
	DECLARE @r varchar(1000)=''; --result

	WHILE @i < @len + 1
	BEGIN
		SET @karakter = SUBSTRING(@str, @i, 1)
		SET @t = ''
		SELECT @t = @karakter WHERE @karakter like '[a-z]'

		IF @karakter = '.'
		BEGIN
			SET @LastIsDot = 1
			SET @r = @r + '.'
		END
		ELSE
		BEGIN
			IF @LastIsDot = 1 
			BEGIN
				IF LEN(@karakter)>0
					IF @t=@karakter
					BEGIN
						SET @r = @r + UPPER(SUBSTRING(@str, @i, 1))
						SET @LastIsDot = 0
					END
			END
			ELSE
			BEGIN
				SET @r = @r + LOWER(SUBSTRING(@str, @i, 1))
			END
		END
		SET @i = @i + 1;
	END

	return @r;
END

/*
--example to use:
select dbo.CapitalizeFirstLetterInSentance('today is a great day. no it is not!')

*/

 


How to create login/user with access to a few tables in one database on MS SQL server

user will be able to see all databases (public) but will be able to access only one database and only selected tables

 

CREATE LOGIN MyNewLogin WITH PASSWORD = 'fdsvbw54feweflvwkernm'
GO

USE MyDB
GO

--login and user CAN be same
CREATE USER MyNewLogin FOR LOGIN MyNewLogin 
GO

GRANT SELECT ON table1 TO MyNewLogin 
GRANT SELECT ON table2 TO MyNewLogin 
GRANT SELECT ON table3 TO MyNewLogin 
GRANT SELECT ON table4 TO MyNewLogin 
GO

 

 


How to create table variable in MS SQL server

DECLARE @t TABLE ( 
  ID int,
  name varchar(50)
)

 


How to delete duplicate records from table in MS SQL server

 

This is example of what will be deleted:

DECLARE @t TABLE (
  id INT,
  name VARCHAR(60),
  surname VARCHAR(60)
);

INSERT INTO @t (id,name,surname) VALUES (1,'1','1'),(2,'2','2'),(3,'2','2'),(4,'3','3');

SELECT * FROM @t;

WITH cte AS (
    SELECT id, name, surname,
        ROW_NUMBER() OVER (
            PARTITION BY 
                name, surname
            ORDER BY 
                name, surname
        ) row_num
     FROM @t
)
SELECT * FROM cte
WHERE row_num > 1;

 

This is example before and after deleting:

DECLARE @t TABLE (
  id INT,
  name VARCHAR(60),
  surname VARCHAR(60)
);

INSERT INTO @t (id,name,surname) VALUES (1,'1','1'),(2,'2','2'),(3,'2','2'),(4,'3','3');

SELECT * FROM @t;

WITH cte AS (
    SELECT id, name, surname,
        ROW_NUMBER() OVER (
            PARTITION BY 
                name, surname
            ORDER BY 
                name, surname
        ) row_num
     FROM @t
)
DELETE FROM cte
WHERE row_num > 1;

SELECT * from @t;

 


How to sete user to see only one DB when he logs in

 

use master
deny view any database to [MyUsr]

use [MyDb]
alter authorization on database:: [MyDb] to [MyUsr]

 


How to change Collation for DB and table with FullText Search, table: Kategorija, database: MyDB

 

USE MyDB;

ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

DROP FULLTEXT INDEX ON Kategorija;
DROP FULLTEXT CATALOG Kategorija;

ALTER DATABASE MyDB COLLATE Croatian_CI_AS;

ALTER TABLE Kategorija ALTER COLUMN naziv nvarchar(500) COLLATE Croatian_CI_AS;
ALTER TABLE Kategorija ALTER COLUMN opis nvarchar(max) COLLATE Croatian_CI_AS;
ALTER TABLE Kategorija ALTER COLUMN kratkiopis nvarchar(max) COLLATE Croatian_CI_AS;

ALTER DATABASE MyDB SET MULTI_USER;

CREATE FULLTEXT CATALOG Kategorija AS DEFAULT;
CREATE FULLTEXT INDEX ON dbo.Kategorija(naziv LANGUAGE 1050, opis LANGUAGE 1050, kratkiopis LANGUAGE 1050)
	KEY INDEX PK_Kategorija   
	ON Kategorija
	WITH STOPLIST = SYSTEM;  

 


How to change collation of DB

 

USE MyDB;

ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE MyDB COLLATE Croatian_CI_AS;
ALTER DATABASE MyDB SET MULTI_USER;

 

 

 

@2016