Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How to delete SQL Column via ADO.NET

Posted on 2009-02-20
7
Medium Priority
?
416 Views
Last Modified: 2012-05-06
I am looking for a way to remove a column with t-sql through ADO.NET. I have gotten to a point where it chrips at me about having to remove a constraint...is there a way to force it anyway? If not, is there a way for me to find the name of that constraint via ADO so I can remove it and the column?
Thanks~
0
Comment
Question by:melegant99
  • 3
  • 3
7 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23696533
Are you executing a "alter table <tablename> drop column <columnname>" command?

Why would you want to modify the database from the front end code?
0
 

Author Comment

by:melegant99
ID: 23696599
Yes, that is t-sql that I am using.

It is a long story, but I need to be able to add and remove 'fields' that will act as columns because of the way it will report in crystal.

0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23698124
There are several things you will have to account for.  Constraints (default, foreign key, check) and indexes.

What db engine and what version?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:melegant99
ID: 23698179
SQL 2005.

It will never be a PK or a FK, only a varchar field. But the 'default' is the contraint I need to remove...how to find the name dynamically and remove?
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 1600 total points
ID: 23698823
Here you go...


create procedure up_ForcedDropColumn
 @tableschema  sysname
,@tablename    sysname
,@ColumnName   sysname
as
declare 
      @SQL     nvarchar(max)
     ,@oid     int
     ,@tnc     nvarchar(max)
 
set @tnc = N'[' + @tableschema + N'].[' + @tablename + N']'
set @oid = object_id(@tnc)
if @oid is null
   return -1
 
if columnproperty(@oid, @columnName ,'allowsnull') is null
   return -2
 
select @SQL = N'alter table ' + @tnc + N' drop constraint [' + o.[name] + N']'
from sys.sysconstraints co
  join sys.columns c
    on co.id=c.[object_id]
    and co.colid=c.column_id
  join sys.objects o
    on co.constid = o.[object_id]
where co.id = @oid
  and c.[name] = @columnname
 
exec sp_executesql @SQL
 
set @SQL = 'alter table [' + @tableschema + N'].[' + @tablename + N'] drop column ['+ @columnname + N']'
 
exec sp_executesql @SQL
 
return 0
go
begin tran
declare @r int
exec @r=up_ForcedDropColumn N'dbo',N'yourtable',N'vc1'
select @r
 
exec @r=up_ForcedDropColumn N'dbo',N'yourtable',N'vc2'
select @r
rollback

Open in new window

0
 
LVL 26

Assisted Solution

by:Anurag Thakur
Anurag Thakur earned 400 total points
ID: 23699959
sp copied from sqlservercentral.com
http://www.sqlservercentral.com/scripts/Miscellaneous/31737/
-- Author: Andy Brown (andy.brown@mm-games.com)
 
IF EXISTS (SELECT 	1 
		FROM 	sysobjects 
		WHERE 	[name] = 'spDropColumnAndConstraints'
		AND 	xtype = 'P')
	DROP PROCEDURE spDropColumnAndConstraints
GO
 
CREATE PROCEDURE spDropColumnAndConstraints
	@table sysname = 'DefaultTable',
	@column sysname = 'DefaultColumn',
	@DropConstraints int = 0,
	@DropColumn int = 0
AS 
BEGIN
	SET NOCOUNT ON
 
	DECLARE @tableID int
	DECLARE @constraint sysname
	DECLARE @constrainttype nchar(1)
	DECLARE @sql nvarchar(4000)
	
	SET @tableID = object_id(@table)
	
	DECLARE cur_constraints CURSOR FOR
		SELECT 	Cons.xtype, 
			Cons.[name]
			FROM	[sysobjects] as Cons WITH(NOLOCK)
			INNER JOIN	
				(select	[id],
					colid 
					FROM	syscolumns WITH(NOLOCK)
					WHERE	id = @tableID
					AND 	name = @column) AS Cols
				ON	Cons.parent_obj = Cols.id
				AND 	Cons.info = Cols.colid
					
			WHERE	Cons.xtype in ('C', 'F', 'PK', 'UQ', 'D')
			AND 	@tableID = Cons.parent_obj
	
	IF EXISTS (SELECT	1 
			FROM	sysobjects WITH(NOLOCK)
			INNER JOIN
				syscolumns WITH(NOLOCK) 
				ON	sysobjects.[id] = syscolumns.[id]
			WHERE	syscolumns.name = @column 
			AND	sysobjects.name = @table)
	BEGIN
		OPEN cur_constraints
 
		FETCH NEXT FROM cur_constraints INTO @constrainttype, @constraint
		WHILE (@@FETCH_STATUS = 0)
		BEGIN
			SELECT @sql = N'ALTER TABLE '+@table+N' DROP CONSTRAINT '+@constraint
			IF @DropConstraints = 1
			BEGIN
				EXEC sp_executesql @sql
				PRINT 'DROPPING THE CONSTRAINT:'+@constraint+' (id:'+CAST(object_id(@constraint) AS VARCHAR)+') OF TYPE:'+@constrainttype+' FROM TABLE:'+@table
			END
			ELSE
				PRINT 'FOUND THE CONSTRAINT:'+@constraint+' (id:'+CAST(object_id(@constraint) AS VARCHAR)+') OF TYPE:'+@constrainttype+' ON TABLE:'+@table
			FETCH NEXT FROM cur_constraints INTO @constrainttype, @constraint
		END
 
		SET @sql = N'ALTER TABLE dbo.'+@table+N' DROP COLUMN '+@column
		IF @DropColumn = 1
		BEGIN
			PRINT 'DROPPING COLUMN:'+@column+' FROM TABLE:'+@table
			EXEC sp_executesql @sql
		END
		ELSE
			PRINT 'FOUND COLUMN:'+@column+' IN TABLE:'+@table
		CLOSE cur_constraints
		DEALLOCATE cur_constraints
	END
	ELSE
		PRINT 'The column "'+@column+'" or the table "'+@table+'" does not exist.'
END
GO

Open in new window

0
 

Author Closing Comment

by:melegant99
ID: 31549441
Thanks
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question