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 :)

Disable browser's back button ?

you can disable the back button, they say.

didn't test the code yet, will be back with feedback

< script type="text/javascript">

function noBack(){window.history.forward()}
noBack();
window.onload=noBack;
window.onpageshow=function(evt){if(evt.persisted)noBack()}
window.onunload=function(){void(0)}

< / script>

you can feedback also :)

Session state can only be used when enablesessionstate is set to true

I had a JavaScript function, that called a WebService, and I had this "Session state can only be used when enablesessionstate is set to true ..." error over and over again.

Everything was all right, in the web.config and in the "< % @ Page " too.

It was still giving me the error.

I made it work doing this:
start -- control panel -- administrative tools -- services -- asp.net state server

by default it is stopped. i started it.
that's it.

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 ;)