Dropping table Column in SQL server 6.5

I'm trying to drop a table column in SQL Server 6.5. I used the following
command and got error:
ALTER TABLE tablename
DROP COLUMN columnname
GO
It works in SQL Server 2000 version
Please I need help.
Thanks.
Ebon.Hi,
You cant delete a column in sql 6.5
Only way is :-
1. put the data into a new table (select * into table_backup from
real_table)
2. script the table and dependant objetcs
3. change the table script with out the unwanted column
4. Insert into table from table_backup
5. create dependant objects , indexes..
Thanks
Hari
MCDBA
"Egbon" <vnjowusi@.gosps.com> wrote in message
news:OCNIa$CoEHA.1248@.TK2MSFTNGP09.phx.gbl...
> I'm trying to drop a table column in SQL Server 6.5. I used the following
> command and got error:
> ALTER TABLE tablename
> DROP COLUMN columnname
> GO
> It works in SQL Server 2000 version
> Please I need help.
> Thanks.
> Ebon.
>|||Many thanks! Hari.
Egbon.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OtjNXHFoEHA.2588@.TK2MSFTNGP12.phx.gbl...
> Hi,
> You cant delete a column in sql 6.5
> Only way is :-
> 1. put the data into a new table (select * into table_backup from
> real_table)
> 2. script the table and dependant objetcs
> 3. change the table script with out the unwanted column
> 4. Insert into table from table_backup
> 5. create dependant objects , indexes..
> Thanks
> Hari
> MCDBA
> "Egbon" <vnjowusi@.gosps.com> wrote in message
> news:OCNIa$CoEHA.1248@.TK2MSFTNGP09.phx.gbl...
> > I'm trying to drop a table column in SQL Server 6.5. I used the
following
> > command and got error:
> >
> > ALTER TABLE tablename
> > DROP COLUMN columnname
> > GO
> >
> > It works in SQL Server 2000 version
> > Please I need help.
> >
> > Thanks.
> >
> > Ebon.
> >
> >
>

dropping all statistics

Hi,
I'm wondering if there is a command I can use to drop all system & user
created statistics on a table? And then I can loop through the database and
get rid of all the statistics for the entire database. Thus, if it's not a
command but some SQL code then I can use that too.
I'm running some upgrade scripts for my application and it often fails on
dropping or altering tables based on statistics it doesn't know about.
Thanks,
matt
Try below. Untested, just wrote it. Change the PRINT to EXEC(@.sql) to actually execute the DROP
statements:
DECLARE @.tblname sysname, @.statname sysname, @.sql nvarchar(2000)
DECLARE c CURSOR FOR
SELECT object_name(id), name FROM sysindexes WHERE INDEXPROPERTY(id, name, 'IsStatistics') = 1
OPEN c
FETCH NEXT FROM c INTO @.tblname, @.statname
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.sql = 'DROP STATISTICS [' + @.tblname + '].[' + @.statname + ']'
PRINT @.sql
FETCH NEXT FROM c INTO @.tblname, @.statname
END
CLOSE c
DEALLOCATE c
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"matt" <matt@.discussions.microsoft.com> wrote in message
news:6784F0CC-E641-45C8-A3B6-355C537E1FEC@.microsoft.com...
> Hi,
> I'm wondering if there is a command I can use to drop all system & user
> created statistics on a table? And then I can loop through the database and
> get rid of all the statistics for the entire database. Thus, if it's not a
> command but some SQL code then I can use that too.
> I'm running some upgrade scripts for my application and it often fails on
> dropping or altering tables based on statistics it doesn't know about.
> Thanks,
> matt
|||Thanks! That works perfectly. All I needed was the index property to check
for stats, but I'll take the script. All worked well.
Matt
"Tibor Karaszi" wrote:

> Try below. Untested, just wrote it. Change the PRINT to EXEC(@.sql) to actually execute the DROP
> statements:
> DECLARE @.tblname sysname, @.statname sysname, @.sql nvarchar(2000)
> DECLARE c CURSOR FOR
> SELECT object_name(id), name FROM sysindexes WHERE INDEXPROPERTY(id, name, 'IsStatistics') = 1
> OPEN c
> FETCH NEXT FROM c INTO @.tblname, @.statname
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.sql = 'DROP STATISTICS [' + @.tblname + '].[' + @.statname + ']'
> PRINT @.sql
> FETCH NEXT FROM c INTO @.tblname, @.statname
> END
> CLOSE c
> DEALLOCATE c
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "matt" <matt@.discussions.microsoft.com> wrote in message
> news:6784F0CC-E641-45C8-A3B6-355C537E1FEC@.microsoft.com...
>
>

dropping all statistics

Hi,
I'm wondering if there is a command I can use to drop all system & user
created statistics on a table? And then I can loop through the database and
get rid of all the statistics for the entire database. Thus, if it's not a
command but some SQL code then I can use that too.
I'm running some upgrade scripts for my application and it often fails on
dropping or altering tables based on statistics it doesn't know about.
Thanks,
mattTry below. Untested, just wrote it. Change the PRINT to EXEC(@.sql) to actual
ly execute the DROP
statements:
DECLARE @.tblname sysname, @.statname sysname, @.sql nvarchar(2000)
DECLARE c CURSOR FOR
SELECT object_name(id), name FROM sysindexes WHERE INDEXPROPERTY(id, name, '
IsStatistics') = 1
OPEN c
FETCH NEXT FROM c INTO @.tblname, @.statname
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.sql = 'DROP STATISTICS [' + @.tblname + '].[' + @.statname + ']'
PRINT @.sql
FETCH NEXT FROM c INTO @.tblname, @.statname
END
CLOSE c
DEALLOCATE c
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"matt" <matt@.discussions.microsoft.com> wrote in message
news:6784F0CC-E641-45C8-A3B6-355C537E1FEC@.microsoft.com...
> Hi,
> I'm wondering if there is a command I can use to drop all system & user
> created statistics on a table? And then I can loop through the database a
nd
> get rid of all the statistics for the entire database. Thus, if it's not
a
> command but some SQL code then I can use that too.
> I'm running some upgrade scripts for my application and it often fails on
> dropping or altering tables based on statistics it doesn't know about.
> Thanks,
> matt|||Thanks! That works perfectly. All I needed was the index property to check
for stats, but I'll take the script. All worked well.
Matt
"Tibor Karaszi" wrote:

> Try below. Untested, just wrote it. Change the PRINT to EXEC(@.sql) to actu
ally execute the DROP
> statements:
> DECLARE @.tblname sysname, @.statname sysname, @.sql nvarchar(2000)
> DECLARE c CURSOR FOR
> SELECT object_name(id), name FROM sysindexes WHERE INDEXPROPERTY(id, name,
'IsStatistics') = 1
> OPEN c
> FETCH NEXT FROM c INTO @.tblname, @.statname
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.sql = 'DROP STATISTICS [' + @.tblname + '].[' + @.statname + ']
'
> PRINT @.sql
> FETCH NEXT FROM c INTO @.tblname, @.statname
> END
> CLOSE c
> DEALLOCATE c
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "matt" <matt@.discussions.microsoft.com> wrote in message
> news:6784F0CC-E641-45C8-A3B6-355C537E1FEC@.microsoft.com...
>
>

dropping all statistics

Hi,
I'm wondering if there is a command I can use to drop all system & user
created statistics on a table? And then I can loop through the database and
get rid of all the statistics for the entire database. Thus, if it's not a
command but some SQL code then I can use that too.
I'm running some upgrade scripts for my application and it often fails on
dropping or altering tables based on statistics it doesn't know about.
Thanks,
mattTry below. Untested, just wrote it. Change the PRINT to EXEC(@.sql) to actually execute the DROP
statements:
DECLARE @.tblname sysname, @.statname sysname, @.sql nvarchar(2000)
DECLARE c CURSOR FOR
SELECT object_name(id), name FROM sysindexes WHERE INDEXPROPERTY(id, name, 'IsStatistics') = 1
OPEN c
FETCH NEXT FROM c INTO @.tblname, @.statname
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.sql = 'DROP STATISTICS [' + @.tblname + '].[' + @.statname + ']'
PRINT @.sql
FETCH NEXT FROM c INTO @.tblname, @.statname
END
CLOSE c
DEALLOCATE c
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"matt" <matt@.discussions.microsoft.com> wrote in message
news:6784F0CC-E641-45C8-A3B6-355C537E1FEC@.microsoft.com...
> Hi,
> I'm wondering if there is a command I can use to drop all system & user
> created statistics on a table? And then I can loop through the database and
> get rid of all the statistics for the entire database. Thus, if it's not a
> command but some SQL code then I can use that too.
> I'm running some upgrade scripts for my application and it often fails on
> dropping or altering tables based on statistics it doesn't know about.
> Thanks,
> matt|||Thanks! That works perfectly. All I needed was the index property to check
for stats, but I'll take the script. All worked well.
Matt
"Tibor Karaszi" wrote:
> Try below. Untested, just wrote it. Change the PRINT to EXEC(@.sql) to actually execute the DROP
> statements:
> DECLARE @.tblname sysname, @.statname sysname, @.sql nvarchar(2000)
> DECLARE c CURSOR FOR
> SELECT object_name(id), name FROM sysindexes WHERE INDEXPROPERTY(id, name, 'IsStatistics') = 1
> OPEN c
> FETCH NEXT FROM c INTO @.tblname, @.statname
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.sql = 'DROP STATISTICS [' + @.tblname + '].[' + @.statname + ']'
> PRINT @.sql
> FETCH NEXT FROM c INTO @.tblname, @.statname
> END
> CLOSE c
> DEALLOCATE c
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "matt" <matt@.discussions.microsoft.com> wrote in message
> news:6784F0CC-E641-45C8-A3B6-355C537E1FEC@.microsoft.com...
> > Hi,
> >
> > I'm wondering if there is a command I can use to drop all system & user
> > created statistics on a table? And then I can loop through the database and
> > get rid of all the statistics for the entire database. Thus, if it's not a
> > command but some SQL code then I can use that too.
> >
> > I'm running some upgrade scripts for my application and it often fails on
> > dropping or altering tables based on statistics it doesn't know about.
> >
> > Thanks,
> > matt
>
>

Dropping all connections to a database

Is there an easy command I can use within a sql script that will drop all
connections to a database, without having to go the SEM and dropping them via
Management/Current Activity/Process info?
Thanks.
DF
Use "ALTER DATABASE".
Example:
alter database northwind
set single_user with ROLLBACK IMMEDIATE
AMB
"Doug F." wrote:

> Is there an easy command I can use within a sql script that will drop all
> connections to a database, without having to go the SEM and dropping them via
> Management/Current Activity/Process info?
> Thanks.
> DF
|||Doug
Try this
alter database [dbname]
set restricted_user
with rollback immediate
Regards
John
"Doug F." wrote:

> Is there an easy command I can use within a sql script that will drop all
> connections to a database, without having to go the SEM and dropping them via
> Management/Current Activity/Process info?
> Thanks.
> DF
|||Alejandro and John - thank you both.
Doug F.
"John Bandettini" wrote:
[vbcol=seagreen]
> Doug
> Try this
> alter database [dbname]
> set restricted_user
> with rollback immediate
> Regards
> John
> "Doug F." wrote:
sql

Dropping all connections to a database

Is there an easy command I can use within a sql script that will drop all
connections to a database, without having to go the SEM and dropping them vi
a
Management/Current Activity/Process info?
Thanks.
DFUse "ALTER DATABASE".
Example:
alter database northwind
set single_user with ROLLBACK IMMEDIATE
AMB
"Doug F." wrote:

> Is there an easy command I can use within a sql script that will drop all
> connections to a database, without having to go the SEM and dropping them
via
> Management/Current Activity/Process info?
> Thanks.
> DF|||Doug
Try this
alter database [dbname]
set restricted_user
with rollback immediate
Regards
John
"Doug F." wrote:

> Is there an easy command I can use within a sql script that will drop all
> connections to a database, without having to go the SEM and dropping them
via
> Management/Current Activity/Process info?
> Thanks.
> DF|||Alejandro and John - thank you both.
Doug F.
"John Bandettini" wrote:
[vbcol=seagreen]
> Doug
> Try this
> alter database [dbname]
> set restricted_user
> with rollback immediate
> Regards
> John
> "Doug F." wrote:
>

Dropping all connections to a database

Is there an easy command I can use within a sql script that will drop all
connections to a database, without having to go the SEM and dropping them via
Management/Current Activity/Process info?
Thanks.
DFUse "ALTER DATABASE".
Example:
alter database northwind
set single_user with ROLLBACK IMMEDIATE
AMB
"Doug F." wrote:
> Is there an easy command I can use within a sql script that will drop all
> connections to a database, without having to go the SEM and dropping them via
> Management/Current Activity/Process info?
> Thanks.
> DF|||Doug
Try this
alter database [dbname]
set restricted_user
with rollback immediate
Regards
John
"Doug F." wrote:
> Is there an easy command I can use within a sql script that will drop all
> connections to a database, without having to go the SEM and dropping them via
> Management/Current Activity/Process info?
> Thanks.
> DF|||Alejandro and John - thank you both.
Doug F.
"John Bandettini" wrote:
> Doug
> Try this
> alter database [dbname]
> set restricted_user
> with rollback immediate
> Regards
> John
> "Doug F." wrote:
> > Is there an easy command I can use within a sql script that will drop all
> > connections to a database, without having to go the SEM and dropping them via
> > Management/Current Activity/Process info?
> >
> > Thanks.
> >
> > DF

Drop User Command

What is the stored procedure for drop a user? Drop_user
userx...
Thanks,
Brady Snow
McKinney, Texas
See:
sp_dropuser
sp_revokedbaccess
sp_droplogin
in Books Online (BOL)
Rohtash Kapoor
http://www.sqlmantra.com
"Brady Snow" <anonymous@.discussions.microsoft.com> wrote in message
news:1905501c41bee$fd37b770$a401280a@.phx.gbl...
> What is the stored procedure for drop a user? Drop_user
> userx...
> Thanks,
> Brady Snow
> McKinney, Texas
|||Hi,
In SQL Server you will be having Login and Users.
Login : Login to authenticate inside SQL server when you use SQL server
authnetication
User: Who got previlege to access the databases
So before deleting the Login you have to drop the user
Command to drop user:
sp_dropuser <user_name>
Command to drop Login
sp_droplogin <login_name>
Apart from this refere the below commands in books online:
1. sp_revokelogin <Loginame>
2.sp_revokedbaccess <user_name>
Thanks
Hari
MCDBA
"Brady Snow" <anonymous@.discussions.microsoft.com> wrote in message
news:1905501c41bee$fd37b770$a401280a@.phx.gbl...
> What is the stored procedure for drop a user? Drop_user
> userx...
> Thanks,
> Brady Snow
> McKinney, Texas

DROP DATABASE problem

I am facing a trouble with DROP DATABASE command. Let me explain it this way:
Actually I am building an installation package which executes a series of SQL scripts to do the database changes on the target machine. Steps are briefly listed below:
1 - Create a database with simple CREATE DATABASE command
2 - Restore the database created above with RESTORE DATABASE command (with replace option)
3 - Configure other objects such as logins etc etc.
Now, everything works fine as long as all the scripts execute correctly.

However, I have seen instances when RESTORE DATABASE fails with "timeout expired" error. Well, I overcame this problem by simply increasing the timeout limit. But in that course what I observed is that when a restore database command "fails", then deleting the database immediately after that (as a part of rollback process) does not delete the database files (i.e., I can't see them thru management studio but..... physical *.mdf and *.ldf files still remain) Normally, when I use DROP DATABASE command with a database in "ONLINE" state these files are perfectly deleted, but when the database is in a "RESTORING" state, the physical files aren't getting deleted...
I don't know what's going wrong. Any ideas?
Any help will be greatly appreciated

Can't you just add a check for the files in your setup package, and if it's in a rollback state, just delete those files if found..?

/Kenneth

|||I can certainly do that. But I am keeping that as the last resort. Mainly because, that will involve querying the registry to get the instance names, then the data folders for those instances and then going to that location to check for the existence of mdf files. This would do my job, but then I beleive this is not the standard way to do that. Such processes may go out-of-sync very easily.

I was just trying to figure out why these files aren't deleted when database is in a restoring state. Or is it possible to correct this in any way?|||

I'm having a bit of trouble reproducing this...

Assuming SQL Server 2k 8.00.760 Dev Ed, and assuming 'Loading' is the same as your 'Restoring' state...

If I restore a db with norecovery option, it's in Loading state.
When I do a DROP DATABASE on that db, the files are deleted also.... as they should

It seems to be working ok from this end...

/Kenneth

|||Kenneth, I am not sure about "Loading" state but I was able to reproduce this error on two machines running instances of SQL Server 2005.
Method I used to reproduce the "restoring" state was writing a program (in my case C#) which connects to the DB and executes the "restore database" command with extremely low command timeout (i tried with 1 and 3 seconds, since database backup file's sixe is around 300 MB so it certainly takes more than that and a timeout occurs). So, when timeout occurs, the database is left in "restoring" state.... then I use the "drop database" command.. database entries are deleted but files remain. Probably, with "Loading" state this problem is not occurring. I am still not able to figure out what's wrong. Could you try reproducing that with database in "restoring" state?|||

I haven't had the time to do any actual testing, but if I was to speculate a bit, I believe that this is the intended behaviour..

If I understand it correctly, the 'problem' is after a 'failed' restore - that is, the restore process has been abnormally terminated for some reason..?
I would then assume that the state of the db would then be considered as 'offline'.

If that is indeed the case, then this quote from BOL:

Dropping a database deletes the database from an instance of SQL Server and deletes the physical disk files used by the database. If the database or any one of its files are offline when it is dropped, the disk files are not deleted. These files can be deleted manually by using Windows Explorer.

...my guess is that the above is what you're experiencing.
It does 'feel' reasonable, I think.

/Kenneth

|||

I think that the process you outlined for locating the database files (querying the registry etc...) may be a little over-complex.

As long as the database is attached to the instance then you can query the master.sys.master_files Catalog View (or master.dbo.sysaltfiles in SQL Server 2000) to determine the locations of the database's files prior to dropping the database and deleting the files (via xp_cmdshell).

Chris

|||Kenneth, In that case the only option I have is to ensure myself that the database files are deleted after every failed restore.
Chris, I just found out the method you described to find out the db filename. In fact, am new to SQL Server and so less aware of admin things.
Anyway, Thanks to both of you for all the time and help extended to me.

DROP DATABASE problem

I am facing a trouble with DROP DATABASE command. Let me explain it this way:
Actually I am building an installation package which executes a series of SQL scripts to do the database changes on the target machine. Steps are briefly listed below:
1 - Create a database with simple CREATE DATABASE command
2 - Restore the database created above with RESTORE DATABASE command (with replace option)
3 - Configure other objects such as logins etc etc.
Now, everything works fine as long as all the scripts execute correctly.

However, I have seen instances when RESTORE DATABASE fails with "timeout expired" error. Well, I overcame this problem by simply increasing the timeout limit. But in that course what I observed is that when a restore database command "fails", then deleting the database immediately after that (as a part of rollback process) does not delete the database files (i.e., I can't see them thru management studio but..... physical *.mdf and *.ldf files still remain) Normally, when I use DROP DATABASE command with a database in "ONLINE" state these files are perfectly deleted, but when the database is in a "RESTORING" state, the physical files aren't getting deleted...
I don't know what's going wrong. Any ideas?
Any help will be greatly appreciated

Can't you just add a check for the files in your setup package, and if it's in a rollback state, just delete those files if found..?

/Kenneth

|||I can certainly do that. But I am keeping that as the last resort. Mainly because, that will involve querying the registry to get the instance names, then the data folders for those instances and then going to that location to check for the existence of mdf files. This would do my job, but then I beleive this is not the standard way to do that. Such processes may go out-of-sync very easily.

I was just trying to figure out why these files aren't deleted when database is in a restoring state. Or is it possible to correct this in any way?|||

I'm having a bit of trouble reproducing this...

Assuming SQL Server 2k 8.00.760 Dev Ed, and assuming 'Loading' is the same as your 'Restoring' state...

If I restore a db with norecovery option, it's in Loading state.
When I do a DROP DATABASE on that db, the files are deleted also.... as they should

It seems to be working ok from this end...

/Kenneth

|||Kenneth, I am not sure about "Loading" state but I was able to reproduce this error on two machines running instances of SQL Server 2005.
Method I used to reproduce the "restoring" state was writing a program (in my case C#) which connects to the DB and executes the "restore database" command with extremely low command timeout (i tried with 1 and 3 seconds, since database backup file's sixe is around 300 MB so it certainly takes more than that and a timeout occurs). So, when timeout occurs, the database is left in "restoring" state.... then I use the "drop database" command.. database entries are deleted but files remain. Probably, with "Loading" state this problem is not occurring. I am still not able to figure out what's wrong. Could you try reproducing that with database in "restoring" state?|||

I haven't had the time to do any actual testing, but if I was to speculate a bit, I believe that this is the intended behaviour..

If I understand it correctly, the 'problem' is after a 'failed' restore - that is, the restore process has been abnormally terminated for some reason..?
I would then assume that the state of the db would then be considered as 'offline'.

If that is indeed the case, then this quote from BOL:

Dropping a database deletes the database from an instance of SQL Server and deletes the physical disk files used by the database. If the database or any one of its files are offline when it is dropped, the disk files are not deleted. These files can be deleted manually by using Windows Explorer.

...my guess is that the above is what you're experiencing.
It does 'feel' reasonable, I think.

/Kenneth

|||

I think that the process you outlined for locating the database files (querying the registry etc...) may be a little over-complex.

As long as the database is attached to the instance then you can query the master.sys.master_files Catalog View (or master.dbo.sysaltfiles in SQL Server 2000) to determine the locations of the database's files prior to dropping the database and deleting the files (via xp_cmdshell).

Chris

|||Kenneth, In that case the only option I have is to ensure myself that the database files are deleted after every failed restore.
Chris, I just found out the method you described to find out the db filename. In fact, am new to SQL Server and so less aware of admin things.
Anyway, Thanks to both of you for all the time and help extended to me.

Drop database on a different SQL Server.

Hi,

I am new to SQL Server 2005.Till now, I have been using a SP to execute DROP DATABASE command to drop databases on my existing database server.

but now i want to delete a database which is on a different SQL Server 2005 instance on a different machine. but i am not sure how to do this.

Can anyone please help me on this?

Any help would be appreciated.

Thanx in advance.

Kawal

If you have the remote server credential connect your SQL Mang. Studio with the target server & execute the same script.

|||

Thanks for the reply!

i can do that but my problem is different.

I am deleting database from my application which calls a VB component to delete the database. SP is called from this VB component and this SP resides on Admin DB which is on , say server1. database that has to be deleted is on a different server running a different SQL Server instance.

Is this thing possible in any way?

Waiting for your reply.

|||As far as I know, in this case, you have to recreate the sproc on target server.

|||and can you please tell how to do that? as i tols earlier, i am new to SQL Server

Drop Column problem

Hello,

I'm just returning to MS SQL Server after two years of dealing with
Sybase ASE. I need to drop a column, using the alter table command.
I keep getting an error indicating that a constraint is using the
column. Here is the create script for the table
create table mytable
(col1 char(1) not null,
col2 char(1) default 'A' not null
)

Here is the alter table command:
alter table mytable drop column col2

When I run it I get the following error:
Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF__mytable__col2__114A936A' is dependent on column
'col2'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN col2 failed because one or more objects access
this column.

Is there anyway to tell the database to drop all column constraints
when the column is deleted?

Thanks,

James K.Unfortunately you have to drop the default first. Give your default a
meaningful name so that it's easier to refer to it in other statements:

create table mytable
(col1 char(1) not null,
col2 char(1) constraint DF_mytable_col2 default 'A' not null
)

ALTER TABLE mytable DROP CONSTRAINT DF_mytable_col2
ALTER TABLE mytable DROP COLUMN col2

--
David Portas
SQL Server MVP
--|||Hi

I don't think there is a way to easily do this, the syntax of the ALTER
table does not allow it without extra work.

You can drop the constraint before the column in the same statement, but to
do this you would need to know the name. As you haven't specified a name in
your create table statement the system generates one for you. It is
therefore easier to create the defaults in an alter table statement , you
can then specify the default constraint name.

create table mytable
(col1 char(1) not null,
col2 char(1) not null
)

ALTER TABLE mytable
ADD CONSTRAINT DF_mytable_col2 default 'A' FOR col2

alter table mytable
drop constraint DF_mytable_col2,
column col2

John
"James Knowlton" <jlknowlton@.hotmail.com> wrote in message
news:bde3b38b.0407010716.62a5b53a@.posting.google.c om...
> Hello,
> I'm just returning to MS SQL Server after two years of dealing with
> Sybase ASE. I need to drop a column, using the alter table command.
> I keep getting an error indicating that a constraint is using the
> column. Here is the create script for the table
> create table mytable
> (col1 char(1) not null,
> col2 char(1) default 'A' not null
> )
> Here is the alter table command:
> alter table mytable drop column col2
> When I run it I get the following error:
> Server: Msg 5074, Level 16, State 1, Line 1
> The object 'DF__mytable__col2__114A936A' is dependent on column
> 'col2'.
> Server: Msg 4922, Level 16, State 1, Line 1
> ALTER TABLE DROP COLUMN col2 failed because one or more objects access
> this column.
> Is there anyway to tell the database to drop all column constraints
> when the column is deleted?
> Thanks,
> James K.

Drop All Views

Is there a command or a way to drop all views of a database with one command or must one do a drop command for each view?

You must do a separate DROP for each VIEW.

However, you can do it in a loop where you don't have to know the VIEW name in advance.

|||

I have a procedure to do this in this file: http://drsql.org/Documents/utility.drop_objects_procs.zip

It is called: utility.codedObjects$remove and you pass it a value of 'VIEW' for the @.object_type_desc parameter

I have had a few problems downloading it in IE7, but none with Firefox...

Louis

|||

one simple method , select 'Drop view '+Name from sysobjects. You can create a sp as input parameter object type and you can make it more dynamic.

run this select statement in QA and copy paste the result to QA and run

Madhu

|||

Arnie,

Thanks for confirming what I thought was true.

Mary

|||

Louis,

Thanks for all the valuable information.

Mary

|||

Madhu,

Thanks for the information it is a great help.

Mary

Drop all table

Hi everybody,

I need some help in SQL Server. I am looking for a command that will "Drop
all user table" in a
user database.

Can anyone help me?

Thank you very much
SabrinaTo add to Erland's response, below is a script developed for SQL 2000
that will drop schema bound views and functions as sell as foreign keys
beforehand. This will still need to be run iteratively in the case of
nested schema bound objects.

IF DB_NAME() IN ('master', 'msdb', 'model', 'distribution')
BEGIN
RAISERROR('Not for use on system databases', 16, 1)
GOTO Done
END

SET NOCOUNT ON

DECLARE @.DropStatement nvarchar(4000)
DECLARE @.SequenceNumber int
DECLARE @.LastError int
DECLARE @.TablesDropped int

DECLARE DropStatements CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
--views
SELECT
1 AS SequenceNumber,
N'DROP VIEW ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) AS DropStatement
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = N'VIEW' AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsSchemaBound') = 1 AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsMSShipped') = 0
UNION ALL
--procedures and functions
SELECT
2 AS SequenceNumber,
N'DROP PROCEDURE ' +
QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME) AS DropStatement
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE
ROUTINE_TYPE = N'FUNCTION' AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsSchemaBound') = 1 AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsMSShipped') = 0
UNION ALL
--foreign keys
SELECT
3 AS SequenceNumber,
N'ALTER TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) +
N' DROP CONSTRAINT ' +
CONSTRAINT_NAME AS DropStatement
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
CONSTRAINT_TYPE = N'FOREIGN KEY'
UNION ALL
--tables
SELECT
4 AS SequenceNumber,
N'DROP TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) AS DropStatement
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = N'BASE TABLE' AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsMSShipped') = 0
ORDER BY SequenceNumber

OPEN DropStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM DropStatements INTO @.SequenceNumber, @.DropStatement
IF @.@.FETCH_STATUS = -1 BREAK
BEGIN
RAISERROR('%s', 0, 1, @.DropStatement) WITH NOWAIT
--EXECUTE sp_ExecuteSQL @.DropStatement
SET @.LastError = @.@.ERROR
IF @.LastError > 0
BEGIN
RAISERROR('Script terminated due to unexpected error', 16, 1)
GOTO Done
END
END
END
CLOSE DropStatements
DEALLOCATE DropStatements

Done:

GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"Sabrina" <missy2bfw@.aol.com> wrote in message
news:3f4a15d6$0$249$4d4ebb8e@.read.news.de.uu.net.. .
> Hi everybody,
> I need some help in SQL Server. I am looking for a command that will
"Drop
> all user table" in a
> user database.
> Can anyone help me?
> Thank you very much
> Sabrina
>