mssms dark there

https://www.sqlshack.com/setting-up-the-dark-theme-in-sql-server-management-studio/

worked

Split sql with xml

https://stackoverflow.com/questions/697519/split-function-equivalent-in-t-sql/1846561#1846561

DECLARE @xml xml, @str varchar(100), @delimiter varchar(10)
SET @str = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15'
SET @delimiter = ','
SET @xml = cast((''+replace(@str, @delimiter, '')+'') as xml)
SELECT C.value('.', 'varchar(10)') as value FROM @xml.nodes('X') as X(C)

Parse URL in SQL

I searched a lot, and I did not like anything I found. Then I saw this on sql-server-helper.com That site looks a little odd, I thought I would save this for myself, in case the site goes away, like some I used before stackoverflow.

CREATE FUNCTION [dbo].[ParseURLQueryString]
( @QueryString AS VARCHAR(MAX) )
RETURNS @QueryStringTable TABLE
( [Key] VARCHAR(100), [Value] VARCHAR(1000) )
AS
BEGIN
DECLARE @QueryStringPair VARCHAR(2000)
DECLARE @Key VARCHAR(100)
DECLARE @Value VARCHAR(1000)

WHILE LEN(@QueryString) > 0
BEGIN
SET @QueryStringPair = LEFT ( @QueryString, ISNULL(NULLIF(CHARINDEX('&', @QueryString) - 1, -1),
LEN(@QueryString)))
SET @QueryString = SUBSTRING( @QueryString, ISNULL(NULLIF(CHARINDEX('&', @QueryString), 0),
LEN(@QueryString)) + 1, LEN(@QueryString))

SET @Key = LEFT (@QueryStringPair, ISNULL(NULLIF(CHARINDEX('=', @QueryStringPair) - 1, -1),
LEN(@QueryStringPair)))
SET @Value = SUBSTRING( @QueryStringPair, ISNULL(NULLIF(CHARINDEX('=', @QueryStringPair), 0),
LEN(@QueryStringPair)) + 1, LEN(@QueryStringPair))

INSERT INTO @QueryStringTable ( [Key], [Value] )
VALUES ( @Key, @Value )
END

RETURN
END

And this is how you use it

SELECT * FROM [dbo].[ParseURLQueryString] ( 'fname=Barack&lname=Obama&addr=1600 Pennsylvania Ave NW&city=Washington&st=DC&zip=20500' )

SQL - update column with sequence number starting from 1

DECLARE @id INT;
SET @id = 0
UPDATE x SET @id = [ItemNo] = @id + 1

How to get the size of the tables in a database

How to get the size of the tables in a database

SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
TotalSpaceKB desc

Thanks to Andrew

CONVERT DATETIME seems all right, but still ...

A table with a varchar column that has date and other value types too.
A query with a simple CONVERT DATETIME

Conversion failed when converting date and/or time from character string.

Crashing. Over and over again. I start to call it X-Files.
Until I read THIS.

Not only I know why it happened (and sortof feel like SQL is "stupid" :D) but I get an interesting idea to work around the issue.

SQL split using Parsename

Interesting way to SPLIT stuff in SQL

Parsename, read all about it, here

DECLARE @FullName VARCHAR(100)
SET @FullName = 'John Doe'

SELECT PARSENAME(REPLACE(@FullName, ' ', '.'), 2) AS [FirstName],
PARSENAME(REPLACE(@FullName, ' ', '.'), 1) AS [LastName]

SQL for CNP :D

create table #cucu(bau varchar(10)) insert into #cucu (bau) values ('123') insert into #cucu (bau) values ('3888') insert into #cucu (bau) values ('2888') insert into #cucu (bau) values ('6888') insert into #cucu (bau) values ('300a') select * from #cucu where bau LIKE '[3,4,6][0-9][0-9][0-9]' drop table #cucu

SQL capitalize words

this is from L.E.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER FUNCTION [dbo].[fn_capitalize]
(
@str AS nvarchar(100)
)
RETURNS nvarchar(100)
AS
BEGIN

DECLARE
@ret_str AS varchar(100),
@pos AS int,
@len AS int

SELECT
@ret_str = N' ' + LOWER(@str),
@pos = 1,
@len = LEN(@str) + 1

WHILE @pos > 0 AND @pos < @len
BEGIN
SET @ret_str = STUFF(@ret_str,
@pos + 1,
1,
UPPER(SUBSTRING(@ret_str,@pos + 1, 1)))
SET @pos = CHARINDEX(N' ', @ret_str, @pos + 1)
END
RETURN RIGHT(@ret_str, @len - 1)

END

SQL - turn debug on-off

This is what I do in stored procedures to turn debug print stuff on and off

DECLARE @isDebug BIT; SET @isDebug = 0; IF @isDebug = 0 BEGIN SET NOCOUNT ON END
/* ... sql stuff ... */
IF @isDebug = 1 PRINT (@strSQL)

Add inserted IDs in a temp table

CREATE TABLE #cucu(bau int)
CREATE TABLE #cucuafter(bau int)

INSERT INTO #cucu(bau)
OUTPUT inserted.[bau] INTO #cucuafter
SELECT RateTypeId
FROM TARFSS_RateType

select * from #cucu
select * from #cucuafter

drop TABLE #cucu
drop table #cucuafter

SQL "not" for a variable

in c# i have a:

a = !a
(if false makes it true, if true makes it false)

in sql i want to do the same with a BIT variable, something like:


declare @a bit
set @a = 1
select @a
set @a = not (@a)
select @a


can i?

i could always do an IF, but this would "look better" :)

with the help of stackoverflow.com:
you can do either:
1. @a = @a ^ 1
or
2. @a = ~@a

i personally prefer #2

SQL Unique constraint

ALTER TABLE [CMST_Country]
ADD CONSTRAINT uc_CountryCode UNIQUE (Code)

SQL split

found this on the net

i like this very much

CREATE TABLE #t (UserName VARCHAR(50))

DECLARE @sql VARCHAR(MAX)
SELECT @sql = 'INSERT INTO #t SELECT ''' + REPLACE(@UserList, ',', ''' UNION SELECT ''') + ''''
PRINT (@sql)
EXEC (@sql)

SELECT * FROM #t

IF OBJECT_ID('tempdb..#t') IS NOT NULL BEGIN DROP TABLE #t END

you can feedback also :)

Select all from all tables all columns

I sometimes need to search for something that I know I added in one of the tables in a database, but i just don't know where I added it.
So for this one, I would need something to search in all the tables.
This case, only 'char', 'varchar', 'nchar', 'nvarchar' columns.

I searched on the internet, and found this guy.
The stored procedure is pretty cool, it works just the way I wanted.
I only added a SOUNDEX to it, so it can find even if you misspell the word.

it takes a while (around 10 seconds on a 400 tables database), but it is very cool.

I am thinking about how nice would it be to implement a search like this in your application (linking to the right screen, that might need a lot of parameters is probably the hard part)

So here's the code:

CREATE PROC ADMNSP_HotSearch
(
@SearchStr NVARCHAR(100)
)
AS
BEGIN
CREATE TABLE #Results (ColumnName NVARCHAR(370), ColumnValue NVARCHAR(3630), Accuracy INT)

SET NOCOUNT ON

DECLARE @TableName NVARCHAR(256), @ColumnName NVARCHAR(128), @SearchStr2 NVARCHAR(110), @mySQL VARCHAR(8000)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
SET @mySQL = 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) , 1
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE SOUNDEX(' + @ColumnName + ') = SOUNDEX(''' + @SearchStr + ''') AND ABS( LEN(' + @ColumnName + ') - LEN(''' + @SearchStr + ''') ) < 10 '

/*PRINT (@mySQL)*/

INSERT INTO #Results
EXEC (@mySQL)
END
END
END

UPDATE #Results SET Accuracy = 0 WHERE CHARINDEX(@SearchStr, ColumnValue) > 0

SELECT DISTINCT ColumnName, ColumnValue, Accuracy, CHARINDEX(@SearchStr, ColumnValue) AS [CHARINDEX] FROM #Results Order by Accuracy, ColumnValue
DROP TABLE #Results
END

Float vs. Decimal

I used float instead of decimal, I'll never do that again.
I found some stored procedures, that had numeric parameters used as varchar. I'll never do that either.
Check out why:

1st case:

declare @a as float
set @a = 13705.05
select @a

declare @b as varchar(100)
set @b = @a
select @b

2nd case:

declare @a as float
set @a = 137.86
select @a

declare @b as varchar(100)
set @b = @a
select @b

Run them, You'll get this:
1st case:

13705.05
13705

2nd case:

137.86
137.86

see my point?

How to add a constraint on a table that has 2 iD columns

How to add a constraint on a table that has 2 iD columns
The idea is that both id columns have allow nulls, but you would like to have an ID at least in one of them all the time.

This is how you do it:

ALTER TABLE [PTCTSS_FactorGroup] ADD
CONSTRAINT [CK_PTCTSS_FactorGroup_2cols] CHECK (((not([ContractSectionId] is null))) or ((not([CustomModuleId] is null))))
GO

Thanks 2 Cris ;)

Generate Stored procedures scripts for SP-s modified after a specific date

When I am installing my changes in an other environment (test envir in my case) i need to get scripts for the modified stored procedures only. When you have a lot of sp-s, and you only need scripts for 3-4 of them, then you should use a script like this one:

CREATE PROCEDURE [dbo].[GenerateLatestProcedures]
@DateFrom DATETIME

AS
BEGIN

DECLARE @spName NVARCHAR(128), @object_id INT
DECLARE myCursor CURSOR FOR
SELECT name
FROM sys.procedures
WHERE modify_date >= @DateFrom OR create_date >= @DateFrom
ORDER BY modify_date DESC

OPEN myCursor
FETCH NEXT FROM myCursor INTO @spName
WHILE @@fetch_status = 0
BEGIN

PRINT 'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[' + CONVERT(VARCHAR(255), @spName) + ']'') AND type in (N''P'', N''PC''))
DROP PROCEDURE [' + CONVERT(VARCHAR(255), @spName) + ']'

SELECT @object_id = object_id FROM sys.procedures WHERE NAME = @spName
PRINT OBJECT_DEFINITION(@object_id)

FETCH NEXT FROM myCursor INTO @spName
END
CLOSE myCursor
DEALLOCATE myCursor

END

SQL - add zeros to the left

Ever needed to add zeros to the left of your string?
For example: instead of "1", you need "0001" displayed.
All this from SQL ...

I think this is an easy way of doing that:

select replicate('0', 6-len(convert(varchar(50), 'aaa'))) + convert(varchar(50), 'aaa')

You need to change the '6' if longer, and 'aaa' is your string ...

what do you think?
comment!

SQL and Underscore

Recently one of our QA told me that searching for a record with _ in it, won't work.
Actually it works, but the thing is that the '_' ( underscore ) is a reserved SQL character, and you have to know how to use it.

Originally the '_' ( underscore ) was designed to:
Let's say you want to find the word 'synopsys' in your table. But you don't if it was 'synopsys' or 'sinopsys' :)
In this case you should do this:

select Title from YourTable where title like 's_nopsys'

And this will find them both. (if any)

Back to Our case.
What our code looked like was something like this:

select Title from YourTable where title like '%_%'

Of course it returned everything from the table ... so QA was kinda right ... filter was not working.
But if QA would've known to search for '[_]' ... then it would've worked ...


It is doable, of course, not to have to add the '[' and the ']' in the search box ... but ... does it worth it?
If you can explain your 'client', how to use the '_' ( underscore ), and how to use the % sign ... in the search query ...
well maybe you don't have to change any of your code, because your 'client' might be happy to use these extra features.

If you can't talk your 'client' into this, you can try something like SQL's escape:

select Title from YourTable where title like '%\_%' escape '\'

or, add some extra character, because next time they'll search for '\' :)
so maybe do something like this:

select Title from YourTable where title like '%' + char(13) + '_%' escape char(13)

If you have some thoughts on this ... tell me about it ...