October 22, 2008 at 2:44 am
Hi All
i am doing some maintenance work on a small database.
over the years the database has been maintained (incorrectly) by various junior developers. as a result there are a whole bunch of unused columns and tables in the database.
i have written the following code:
DECLARE @columns TABLE
(
id BIGINT IDENTITY(1, 1),
columnname VARCHAR(50)
)
INSERT INTO @columns ( columnname )
SELECT column_name
FROM INFORMATION_SCHEMA.columns
WHERE table_name = 'Person'
DECLARE @counter BIGINT
DECLARE @columnname VARCHAR(50)
DECLARE @rowcount BIGINT
DECLARE @emptyrowcount BIGINT
SET @counter = 1
SET @rowcount = ( SELECT COUNT(*)
FROM person
)
WHILE @counter <= ( SELECT MAX(id)
FROM @columns
)
BEGIN
SELECT @columnname = columnname
FROM @columns
WHERE id = @counter
EXECUTE
( 'SELECT count(' + @columnname + ') FROM person a where '
+ @columnname + ' is null or rtrim(ltrim(' + @columnname
+ ')) = '''' '
)
--todo:GET result INTO @emptyrowcount varaible
--Compare @rowcount AND @emptyrowcount
--IF they are equal DROP column
SET @counter = @counter + 1
END
As you can see from the todo list the rest of the task is vey easy, but what i need to know is
How Do I put the result of
EXECUTE
( 'SELECT count(' + @columnname + ') FROM person a where '
+ @columnname + ' is null or rtrim(ltrim(' + @columnname
+ ')) = '''' '
)
into @emptyrowcount in order to use it in the rest of the code.
i have tried:
set @emptyrowcount = (EXECUTE
( 'SELECT count(' + @columnname + ') FROM person a where '
+ @columnname + ' is null or rtrim(ltrim(' + @columnname
+ ')) = '''' '
))
-- and a few other variations.
What is the correct Syntax?
Thanks
Chris
October 27, 2008 at 6:32 am
Wat exectly you want ..please clarigy ur requirement ???
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 27, 2008 at 8:17 am
Chris Morton (10/22/2008)
How Do I put the result of
EXECUTE
( 'SELECT count(' + @columnname + ') FROM person a where '
+ @columnname + ' is null or rtrim(ltrim(' + @columnname
+ ')) = '''' '
)
into @emptyrowcount in order to use it in the rest of the code.
Chris
Look up sp_executesql. That allows you to pass parameters into and out of dynamic SQL. There are some good examples of it in Books Online.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply