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

Dos - glue (sql) files together

I wanted to concatenate files together
I had like 20 sql files
I knew there had to be a way ... other than the "hard" way.

Romica helped me out ;)

this is what he did:
opened a CMD
and go:

type *.sql >> t.sql

done

innerHTML not working in ie7

I had a bug
It looked like innerHTML is not working in ie7.
It was somthing like this:
function ShowLblError(msg)
{
document.getElementById("<%=lblError.ClientID%>").innerHTML = msg;
}

It worked fine in IE6, but not in IE7.
Finally I found out that it is working in IE7 too, I just had a panel with some weird height (it was ok in in IE6, ugly in IE7), and I just couldn't see the Label.

So innerHTML IS working in ie7
:)

Yahoo Messenger disappears

A friend of mine had this problem, and asked me to try to help him out.
After he logged on to Yahoo Messenger, the Messenger disappeared. Just plain and simple "closed himself" :)

I looked into it, even opened the Task Manager, and saw that the application just vanishes from there too.

I had no idea what to do at first, but I saw some forums saying that it was a virus, or something like this, related to the PC-s safety.
People was saying about using Hijackthis and stuff like that.

What I did was: installed Spybot Search and Destroy. Ran it once, found 18 problems. I never even checked what was all that, I just clicked on "Fix selected problems". Problem fixed ;)

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