--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
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,
SELECT * FROM table WHERE CONTAINS((Name,discription), ' "Mountain" OR "Road*" ')
SELECT * FROM table WHERE CONTAINS(Name, '"chain*" NEAR "full*"');
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
SELECT @@IDENTITY AS 'Identity'
SET IDENTITY_INSERT table ON
--insert something
SET IDENTITY_INSERT table OFF
UPDATE Table1 SET Table1.NekoPolje=Table2.NekoPolje FROM Table2 WHERE Table2.Id=Table1.ID
UPDATE t
SET FieldName =
(
SELECT NameName2
FROM table2
WHERE ID = t.ID
)
FROM table1 t;
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'
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')
EXEC sys.sp_fulltext_load_thesaurus_file 1050;
GO
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
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;
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
SELECT *
FROM sys.dm_db_index_physical_stats
(DB_ID(N'DatabaseName'), NULL, NULL, NULL, 'DETAILED')
ORDER BY avg_fragmentation_in_percent desc
/***********************************************************************************************************************
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/
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
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
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]
DBCC CHECKIDENT (TableName, RESEED, 15)
EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'
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
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
USE DatabaseName
GO
EXEC sp_fulltext_database 'enable'
GO
CREATE FULLTEXT CATALOG FTS_DatabaseName
CREATE FULLTEXT INDEX ON DatabaseName.dbo.TableName
(
ColumnName
Language 1033
)
KEY INDEX IndexName;
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 () ')
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)
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)
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
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
SELECT * FROM sys.dm_fts_parser ('-9-', 1050,0,0)
RESTORE DATABASE WITH RECOVERY
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
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
CREATE FULLTEXT CATALOG AwCat WITH ACCENT_SENSITIVITY=OFF GO
ALTER FULLTEXT CATALOG AwCat REBUILD WITH ACCENT_SENSITIVITY=ON GO
--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
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
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]
ARITHABORT helps sometimes :)
SET ARITHABORT ON;
EXEC sp_MyStoreProcedure ...
SELECT MyField
FROM MyTable
ORDER BY MyField
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;
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
USE [DBName]
GO
EXEC sp_MSforeachtable @command1="print '?' DBCC INDEXDEFRAG ('?', ' ', 80)"
GO
EXEC sp_updatestats
GO
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
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)
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;
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
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
SELECT name,
DATABASEPROPERTYEX(name, 'Recovery') as model,
DATABASEPROPERTYEX(name, 'Status') as Status
FROM master.dbo.sysdatabases
WHERE DATABASEPROPERTYEX(name, 'Recovery')='FULL'
ORDER BY name
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';
EXEC sp_msForEachDB '
PRINT ''
USE [?];
DROP USER myUser;
CREATE USER myUser FOR LOGIN myLogin;
EXEC sp_addrolemember ''''db_owner'''', myUser; ''
'
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
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
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'"
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
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
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
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
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$])
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'
set dateformat dmy
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'
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
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
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;
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;
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';
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
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
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';
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';
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!')
*/
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
DECLARE @t TABLE (
ID int,
name varchar(50)
)
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]
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;
USE MyDB;
ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE MyDB COLLATE Croatian_CI_AS;
ALTER DATABASE MyDB SET MULTI_USER;