remove columns stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

remove columns stored procedure

Author

Topic

sherlock_oz
Starting Member

12 Posts

Posted - 2007-05-06 : 19:42:58

Just wondering if i could get some help with this,

I have a large table of decimal values, many of the columns contain only zero values. I would like to write a
stored procedure which loops through these columns, takes the SUM and if it returns zero , then deletes the column from the table.

Any help would be appreciated, thanks in advance, this site has already helped me heaps

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-06 : 22:16:15

"deletes the column from the table"
What do you mean by delete the column from table ? You want to drop the column ? How about those rows with that column contain non zero value ?

sherlock_oz
Starting Member

12 Posts

Posted - 2007-05-06 : 22:22:47

Thanks for replying,

yes i do mean drop the column from the table, as for other values
in the column, if the sum of the column is zero , then it cannot contain any value greater than zero.

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-07 : 08:39:53

[code]
if ( <your condition> )
begin
alter table <table name> drop column <column name>
end
go
[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-07 : 08:56:02

quote:
Originally posted by sherlock_oz

Just wondering if i could get some help with this,

I have a large table of decimal values, many of the columns contain only zero values. I would like to write a
stored procedure which loops through these columns, takes the SUM and if it returns zero , then deletes the column from the table.

Any help would be appreciated, thanks in advance, this site has already helped me heaps

Is this One time effort or you want to do it often?

Madhivanan

Failing to plan is Planning to fail

sherlock_oz
Starting Member

12 Posts

Posted - 2007-05-07 : 09:21:53

In reply to Madhivanan, this is a one off event per table,
these tables contains hundreds of columns, maybe 10% is of interest.
Rest is all zeros, im trying to strip all columns of irrelevant data out to condense the working data.

-------------------------------------------

//Lets see if i can get this right Khtan

-----------------

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
-- Add the parameters for the stored procedure here
<@Column_name, sysname, @Column_name> <String,

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
if ( SUM Column_name = 0 )
begin
alter table <table_name> drop column <column_name>
end
go

END
GO
------------------------------------------
Would that be correct, im not clear on the parameters ??

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-05-07 : 12:52:52

this is not a good idea. a proc that modifies tables by adding/dropping columns is dangerous. if there's a bug, you'll lose data you didn't mean to. also the schema of your system should not ever change at runtime. you are building on a very shaky foundation if you do that.

what I would do in this situation is write a script that generates sql as output, then carefully review the generated sql for correctness before executing it. once you are confident it is correct, execute it in SSMS.

and don't forget about step zero: make a backup of your db first!

www.elsasoft.org

sherlock_oz
Starting Member

12 Posts

Posted - 2007-05-07 : 17:16:20

Thanks Jezemine for the words of caution, however the use i have for this is not your normal use of sql server.
It is not possible for me to lose any data as i will always have all the input files and can simply recreate the tables if need be.
Im just using sql for stat analysis and other functions on this information.
Any chance you could help me with my problem ? If will make what i am doing easier for me to read if i can drop all these columns of zeros

Thanks

svicky9
Posting Yak Master

232 Posts

Posted - 2007-05-07 : 18:00:42

Hi Sherlock,

This procedure should work.

It loops through all cols and then deletes the column where sum(col) is zero.

Take a Backup of the Table

alter procedure deletecolumnsifzero @tablename varchar(1000)
as

set nocount on

declare @n int
declare @sql nvarchar(1000)
declare @sum_cols nvarchar(1000)
declare @c_id nvarchar(100)

set @n = 0

declare c1 cursor for
select column_name from information_schema.columns
where
table_name like @tablename

--Cursor Starts
open c1

fetch next from c1
into @c_id

while @@fetch_status = 0

begin

set @sql=''
set @sql='select @sum_cols = sum('+@c_id+') from ['+@tablename+']'
exec sp_Executesql @sql,N'@sum_cols int out,@tablename nvarchar(100)',@sum_cols out,@tablename

if(@sum_cols = 0)

begin

set @n=@n+1
set @sql=''
set @sql= @sql+'alter table ['+@tablename+'] drop column ['+@c_id+']'
exec sp_executesql @sql

end

fetch next from c1
into @c_id

end

close c1
deallocate c1

Let me know if it works!!!

Vic

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-05-07 : 18:04:44

You'll have to use dynamic sql, and pass both the column_name and table_name to the proc. Don't have time right now to do the work for you, but in the meantime you might have a read here:

http://sommarskog.se/dynamic_sql.html

EDIT: looks like Vic did the work for you. let me reiterate that something like this should NEVER exist in a production database.

www.elsasoft.org

sherlock_oz
Starting Member

12 Posts

Posted - 2007-05-08 : 05:25:12

Vic,

]

That is a beautiful thing !

Thanks so much, please provide details of your favourite charity i will make a small donation.
I will do the reading on the cursor parts of this as i dont understand that yet.

Jezemine ... I will read the stuff on dynamic sql , thanks for pointing me at that.
I also agree that this should never be used in a conventional database appication, but it is the functionality i needed for a rather odd application of SQL i guess.

Thanks once again

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-08 : 08:16:21

I sure hope this column can only contain positive or only contain negative values ... because unless that's the case, a column can have a total of $0 but have many rows with values OTHER than zero ..... right?

- Jeff
http://weblogs.sqlteam.com/JeffS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-08 : 09:19:15

~~set @sql=''~~
set @sql = 'select @sum_cols = count(*) from ' + quotename(@tablename) + ' where ' + quotename(@c_id) + ' <> 0'
exec sp_Executesql @sql, N'@sum_cols int out, @tablename nvarchar(100)', @sum_cols out, @tablename

Peter Larsson
Helsingborg, Sweden

svicky9
Posting Yak Master

232 Posts

Posted - 2007-05-08 : 10:32:42

Please make any donations to oxfam if u can...cheers :-)

Vic

http://vicdba.blogspot.com

sherlock_oz
Starting Member

12 Posts

Posted - 2007-05-09 : 01:52:41

Donation to oxfam australia has been made

Thanks again folks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources