November 23, 2009 at 12:51 pm
Hi,
I'm wondering how you search all the columns of a particular table for a value. We received data that was coded '99999' for all nulls; I need to replace all instances of '99999' with NULL.
It seems like
SELECT * FROM {table x} Where {some variable for value}=99999 should do the trick.
But I haven't been able to find any built in variable for a column's value. I HAVE found some stored procedures out there. Is it necessary to write a stored procedure for this?
Also, I'm sure this is a really newbie question, but is using SELECT * bad? If so, is it a question of query optimization?
Thanks,
Ron
November 23, 2009 at 1:04 pm
rsteckly (11/23/2009)
Hi,I'm wondering how you search all the columns of a particular table for a value. We received data that was coded '99999' for all nulls; I need to replace all instances of '99999' with NULL.
It seems like
SELECT * FROM {table x} Where {some variable for value}=99999 should do the trick.
But I haven't been able to find any built in variable for a column's value. I HAVE found some stored procedures out there. Is it necessary to write a stored procedure for this?
Also, I'm sure this is a really newbie question, but is using SELECT * bad? If so, is it a question of
query optimization?
Thanks,
Ron
It is not required to write a stored procedure for doing this.
If replacing is all what you need, then use this query.
UPDATE TABLE SET COLUMN = NULL WHERE COLUMN = '99999'
I could not understand what you meant by stored procedures out there.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 23, 2009 at 1:18 pm
Well, granted this is different in that it returns the column names of those containing the queried value. I found it here: http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/the-ten-most-asked-sql-server-questions--1#2
Here goes:
CREATE PROCEDURE FindMyData_Number
@DataToFind NVARCHAR(4000),
@ExactMatch BIT = 0
AS
SET NOCOUNT ON
DECLARE @Temp TABLE(RowId INT IDENTITY(1,1), TableName sysname, ColumnName SysName, DataType VARCHAR(100), DataFound BIT)
DECLARE @IsNumber BIT
DECLARE @ISDATE BIT
IF ISNUMERIC(CONVERT(VARCHAR(20), @DataToFind)) = 1
SET @IsNumber = 1
ELSE
SET @IsNumber = 0
INSERT INTO @Temp(TableName, ColumnName, DataType)
SELECT C.Table_Name, C.Column_Name, C.Data_Type
FROM Information_Schema.Columns AS C
INNER Join Information_Schema.Tables AS T
ON C.Table_Name = T.Table_Name
WHERE Table_Type = 'Base Table'
And Data_Type In ('float','real','decimal','money','smallmoney','bigint','int','smallint','tinyint','bit')
DECLARE @i INT
DECLARE @max-2 INT
DECLARE @TableName sysname
DECLARE @ColumnName sysname
DECLARE @sql NVARCHAR(4000)
DECLARE @PARAMETERS NVARCHAR(4000)
DECLARE @DataExists BIT
DECLARE @SQLTemplate NVARCHAR(4000)
SELECT @SQLTemplate = CASE WHEN @ExactMatch = 1
THEN 'If Exists(Select *
From [ReplaceTableName]
Where Convert(VarChar(40), [ReplaceColumnName])
= ''' + @DataToFind + '''
)
Set @DataExists = 1
Else
Set @DataExists = 0'
ELSE 'If Exists(Select *
From [ReplaceTableName]
Where Convert(VarChar(40), [ReplaceColumnName])
Like ''%' + @DataToFind + '%''
)
Set @DataExists = 1
Else
Set @DataExists = 0'
END,
@PARAMETERS = '@DataExists Bit OUTPUT',
@i = 1
SELECT @i = 1, @max-2 = MAX(RowId)
FROM @Temp
WHILE @i <= @max-2
BEGIN
SELECT @sql = REPLACE(REPLACE(@SQLTemplate, 'ReplaceTableName', TableName), 'ReplaceColumnName', ColumnName)
FROM @Temp
WHERE RowId = @i
PRINT @sql
EXEC SP_EXECUTESQL @sql, @PARAMETERS, @DataExists = @DataExists OUTPUT
IF @DataExists =1
UPDATE @Temp SET DataFound = 1 WHERE RowId = @i
SET @i = @i + 1
END
SELECT TableName, ColumnName
FROM @Temp
WHERE DataFound = 1
go
November 23, 2009 at 1:50 pm
Well, I am sorry for the code that I posted in the previous reply, it does not solve the entire problem. I did not see that you need to search all columns in a table until you posted the link (to less than dot Blog) to the stored proc.
Your select query has aggravated my confusion......:-)
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 23, 2009 at 4:50 pm
http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm
The site has a script and a discussion about it.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply