Dropping stored procedures

Hello!
I've written a script to drop all the SPs from a given DB:
DECLARE @.name nvarchar(200)
DECLARE list CURSOR FOR
SELECT name FROM sysobjects where xtype = 'p'
OPEN list
FETCH NEXT FROM list INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.name = 'drop procedure ' + @.name
EXEC sp_executesql @.name
FETCH NEXT FROM list INTO @.name
END
CLOSE list
DEALLOCATE list
GO
When I'm running it from Query Analyzer it works fine, but when running it
from a small utility (which all id does is opening a connection to the DB
and running the same script), only some of the SPs are dropped.
I'm now looking at the utility, but is there any problem with the SQL scirpt
(or a better way to the task (drop all of the SPs in a DB, without knowing
the names of them all)).
Thanks!
RoeeTry to explicitly spcify the owner of the procedure in the Drop command.
--
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.
"Roee Friedman" <necnecnecnec@.hotmail.com> wrote in message
news:bppqas$1rcn6n$1@.ID-200860.news.uni-berlin.de...
> Hello!
> I've written a script to drop all the SPs from a given DB:
> DECLARE @.name nvarchar(200)
> DECLARE list CURSOR FOR
> SELECT name FROM sysobjects where xtype = 'p'
> OPEN list
> FETCH NEXT FROM list INTO @.name
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.name = 'drop procedure ' + @.name
> EXEC sp_executesql @.name
> FETCH NEXT FROM list INTO @.name
> END
> CLOSE list
> DEALLOCATE list
> GO
> When I'm running it from Query Analyzer it works fine, but when running it
> from a small utility (which all id does is opening a connection to the DB
> and running the same script), only some of the SPs are dropped.
> I'm now looking at the utility, but is there any problem with the SQL
scirpt
> (or a better way to the task (drop all of the SPs in a DB, without knowing
> the names of them all)).
> Thanks!
> Roee
>
>

Dropping scripts into sql 2005

Something that really irritates me with sql2005 is when I try to drag and drop a script into sql2005 I have to relog basically. Say I'm dropping in 20 different scripts, I have to relog and select the database every time for each script. Sql2000 did not do this, I drag and drop in scripts and whatever database I was currently in, it would use. Is this just a setting that I need to change or is it a horrible "enhancement"?

Thanks for your help.

Hi,

no I think currently this is not possible, one query windows per query will be opened and prompt you for the information where to connect to.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Dropping and recreating all statistics

I am having deadlock problems and I have noticed that on one table dropping
the statistics and recreating them helped. Is there a script or an easy way
to do this
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200610/1
sp_updatestats is what you're looking for.
On some servers I regularly rebuild all indexes (which also updates the
stats) and then issue DBCC FREEPROCCACHE to completely refresh the system.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||use UPDATE STATISTICS 'tablename', or drop statistics
tablename.statisticsname Followed by a create statistics
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ghunter via droptable.com" <u4529@.uwe> wrote in message
news:672d4e349b451@.uwe...
>I am having deadlock problems and I have noticed that on one table dropping
> the statistics and recreating them helped. Is there a script or an easy
> way
> to do this
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200610/1
>
|||I have tried that and then ran exec sp_helpstats and it showed no statistics
where on the table. What I want to do is delete all statistics on all tables
and them create them on all indexes and colums. I have 10,000 tables so I am
looking for a script.
Paul Ibison wrote:
>sp_updatestats is what you're looking for.
>On some servers I regularly rebuild all indexes (which also updates the
>stats) and then issue DBCC FREEPROCCACHE to completely refresh the system.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200610/1
|||could you point me to a script that automagically does all that for me?
Hilary Cotter wrote:[vbcol=seagreen]
>use UPDATE STATISTICS 'tablename', or drop statistics
>tablename.statisticsname Followed by a create statistics
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200610/1
|||sp_updatestats.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||sp_updatestats will run UPDATE STATISTICS on all tables. It won't create
stats on tables.
The Indexes should all have stats which will get updated using DBCC
DBREINDEX.
If you want to check that the stats have been updated, run DBCC
SHOW_STATISTICS:
eg: DBCC SHOW_STATISTICS ('District','pk_district')
The first column is called 'Updated' and has the date and time of the
update.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Maybe I am not making my question clear enough. Does sp_updatestats delete
and recreate the statistics. And what does sp_updatestats create statistics
on.
I was not aware that update stats would create statistics on something but
that it would only update them. and on top of that it will only update them
if they are out of date.
Paul Ibison wrote:
>sp_updatestats.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200610/1
|||sp_updatestats runs UPDATE STATISTICS on all user tables in the current
database - as far as I know this means that the statblob column is updated
in sysindexes. It doesn't create statistics on columns where there weren't
any previously existing. For indexes there'll already be statistics, and
there'll also be stats for those columns which have been explicitly created
(CREATE STATISTICS). If the key values for indexes have changed a lot or if
there is a significant change in the amount of data in the table then
updating the statistics is useful, and also recompiling the associated
stored procedures whose plans will be inaccurate.
HTH,
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
sql

Dropping and recreating all statistics

I am having deadlock problems and I have noticed that on one table dropping
the statistics and recreating them helped. Is there a script or an easy way
to do this
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1sp_updatestats is what you're looking for.
On some servers I regularly rebuild all indexes (which also updates the
stats) and then issue DBCC FREEPROCCACHE to completely refresh the system.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||use UPDATE STATISTICS 'tablename', or drop statistics
tablename.statisticsname Followed by a create statistics
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ghunter via SQLMonster.com" <u4529@.uwe> wrote in message
news:672d4e349b451@.uwe...
>I am having deadlock problems and I have noticed that on one table dropping
> the statistics and recreating them helped. Is there a script or an easy
> way
> to do this
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1
>|||I have tried that and then ran exec sp_helpstats and it showed no statistics
where on the table. What I want to do is delete all statistics on all tables
and them create them on all indexes and colums. I have 10,000 tables so I am
looking for a script.
Paul Ibison wrote:
>sp_updatestats is what you're looking for.
>On some servers I regularly rebuild all indexes (which also updates the
>stats) and then issue DBCC FREEPROCCACHE to completely refresh the system.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1|||could you point me to a script that automagically does all that for me?
Hilary Cotter wrote:
>use UPDATE STATISTICS 'tablename', or drop statistics
>tablename.statisticsname Followed by a create statistics
>>I am having deadlock problems and I have noticed that on one table dropping
>> the statistics and recreating them helped. Is there a script or an easy
>> way
>> to do this
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1|||sp_updatestats.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||sp_updatestats will run UPDATE STATISTICS on all tables. It won't create
stats on tables.
The Indexes should all have stats which will get updated using DBCC
DBREINDEX.
If you want to check that the stats have been updated, run DBCC
SHOW_STATISTICS:
eg: DBCC SHOW_STATISTICS ('District','pk_district')
The first column is called 'Updated' and has the date and time of the
update.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Maybe I am not making my question clear enough. Does sp_updatestats delete
and recreate the statistics. And what does sp_updatestats create statistics
on.
I was not aware that update stats would create statistics on something but
that it would only update them. and on top of that it will only update them
if they are out of date.
Paul Ibison wrote:
>sp_updatestats.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1|||sp_updatestats runs UPDATE STATISTICS on all user tables in the current
database - as far as I know this means that the statblob column is updated
in sysindexes. It doesn't create statistics on columns where there weren't
any previously existing. For indexes there'll already be statistics, and
there'll also be stats for those columns which have been explicitly created
(CREATE STATISTICS). If the key values for indexes have changed a lot or if
there is a significant change in the amount of data in the table then
updating the statistics is useful, and also recompiling the associated
stored procedures whose plans will be inaccurate.
HTH,
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Dropping and recreating all statistics

I am having deadlock problems and I have noticed that on one table dropping
the statistics and recreating them helped. Is there a script or an easy way
to do this
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200610/1sp_updatestats is what you're looking for.
On some servers I regularly rebuild all indexes (which also updates the
stats) and then issue DBCC FREEPROCCACHE to completely refresh the system.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||use UPDATE STATISTICS 'tablename', or drop statistics
tablename.statisticsname Followed by a create statistics
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ghunter via droptable.com" <u4529@.uwe> wrote in message
news:672d4e349b451@.uwe...
>I am having deadlock problems and I have noticed that on one table dropping
> the statistics and recreating them helped. Is there a script or an easy
> way
> to do this
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200610/1
>|||I have tried that and then ran exec sp_helpstats and it showed no statistics
where on the table. What I want to do is delete all statistics on all tables
and them create them on all indexes and colums. I have 10,000 tables so I am
looking for a script.
Paul Ibison wrote:
>sp_updatestats is what you're looking for.
>On some servers I regularly rebuild all indexes (which also updates the
>stats) and then issue DBCC FREEPROCCACHE to completely refresh the system.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200610/1|||could you point me to a script that automagically does all that for me?
Hilary Cotter wrote:[vbcol=seagreen]
>use UPDATE STATISTICS 'tablename', or drop statistics
>tablename.statisticsname Followed by a create statistics
>
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200610/1|||sp_updatestats.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||sp_updatestats will run UPDATE STATISTICS on all tables. It won't create
stats on tables.
The Indexes should all have stats which will get updated using DBCC
DBREINDEX.
If you want to check that the stats have been updated, run DBCC
SHOW_STATISTICS:
eg: DBCC SHOW_STATISTICS ('District','pk_district')
The first column is called 'Updated' and has the date and time of the
update.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Maybe I am not making my question clear enough. Does sp_updatestats delete
and recreate the statistics. And what does sp_updatestats create statistics
on.
I was not aware that update stats would create statistics on something but
that it would only update them. and on top of that it will only update them
if they are out of date.
Paul Ibison wrote:
>sp_updatestats.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200610/1|||sp_updatestats runs UPDATE STATISTICS on all user tables in the current
database - as far as I know this means that the statblob column is updated
in sysindexes. It doesn't create statistics on columns where there weren't
any previously existing. For indexes there'll already be statistics, and
there'll also be stats for those columns which have been explicitly created
(CREATE STATISTICS). If the key values for indexes have changed a lot or if
there is a significant change in the amount of data in the table then
updating the statistics is useful, and also recompiling the associated
stored procedures whose plans will be inaccurate.
HTH,
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

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

Dropping / Recreating Indexes

All -
You know when you generate the SQL script for a stored procedure, you get
that initial script at the top that first checks for the procedure in
sysobjects and drops it if it is found?
I want to do the same thing with indexes. I want to create a script that
will first delete a given index if its found and then create a new one.
How would you go about do this?
Thanks,
- Mattif exists (select * from dbo.sysindexes where name = 'IX_index_01' and id =object_id('dbo.tableA'))
drop index dbo.tableA.IX_index_01
GO
"Matthew Sajdera" <sajdera@.pcts.com> wrote in message
news:eeA8tWpkDHA.360@.TK2MSFTNGP12.phx.gbl...
> All -
> You know when you generate the SQL script for a stored procedure, you get
> that initial script at the top that first checks for the procedure in
> sysobjects and drops it if it is found?
> I want to do the same thing with indexes. I want to create a script that
> will first delete a given index if its found and then create a new one.
> How would you go about do this?
> Thanks,
> - Matt
>
>|||All -
Sorry about that. I found the DROP INDEX syntax.
Now it looks like the only problem is that if the index is a primary index,
you can't drop it. You have to drop the table, create the table, and then
re-create the index.
Does this sound right?
Thanks,
- Matt
"Matthew Sajdera" <sajdera@.pcts.com> wrote in message
news:eeA8tWpkDHA.360@.TK2MSFTNGP12.phx.gbl...
> All -
> You know when you generate the SQL script for a stored procedure, you get
> that initial script at the top that first checks for the procedure in
> sysobjects and drops it if it is found?
> I want to do the same thing with indexes. I want to create a script that
> will first delete a given index if its found and then create a new one.
> How would you go about do this?
> Thanks,
> - Matt
>
>|||Primary Key indexes are created as part of the Primary Key
Constraint. You cannot directly drop the index, but
instead you drop it by dropping the Primary Key
Constraint. So you don't have to drop the table. You must,
however, first drop any Foreign Key Constraints that
reference the PK.
HTH
Vern
>--Original Message--
>All -
>Sorry about that. I found the DROP INDEX syntax.
>Now it looks like the only problem is that if the index
is a primary index,
>you can't drop it. You have to drop the table, create
the table, and then
>re-create the index.
>Does this sound right?
>Thanks,
>- Matt
>
>"Matthew Sajdera" <sajdera@.pcts.com> wrote in message
>news:eeA8tWpkDHA.360@.TK2MSFTNGP12.phx.gbl...
>> All -
>> You know when you generate the SQL script for a stored
procedure, you get
>> that initial script at the top that first checks for
the procedure in
>> sysobjects and drops it if it is found?
>> I want to do the same thing with indexes. I want to
create a script that
>> will first delete a given index if its found and then
create a new one.
>> How would you go about do this?
>> Thanks,
>> - Matt
>>
>
>.
>|||Hi Matt
No, you certainly don't have to drop the whole table just to drop an index!
If an index supports a primary key constraint, you have to drop the
constraint, and that will automatically drop the index that supports the
constraint.
You can then readd the constraint.
sp_helpconstraint will show you the constraint names
to drop and re-add the constraint use:
ALTER TABLE ... DROP CONSTRAINT
ALTER TABLE ... ADD CONSTRAINT
Full details are in Books Online
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Matthew Sajdera" <sajdera@.pcts.com> wrote in message
news:e2aLycpkDHA.2432@.TK2MSFTNGP10.phx.gbl...
> All -
> Sorry about that. I found the DROP INDEX syntax.
> Now it looks like the only problem is that if the index is a primary
index,
> you can't drop it. You have to drop the table, create the table, and then
> re-create the index.
> Does this sound right?
> Thanks,
> - Matt
>
> "Matthew Sajdera" <sajdera@.pcts.com> wrote in message
> news:eeA8tWpkDHA.360@.TK2MSFTNGP12.phx.gbl...
> > All -
> >
> > You know when you generate the SQL script for a stored procedure, you
get
> > that initial script at the top that first checks for the procedure in
> > sysobjects and drops it if it is found?
> >
> > I want to do the same thing with indexes. I want to create a script
that
> > will first delete a given index if its found and then create a new one.
> >
> > How would you go about do this?
> >
> > Thanks,
> >
> > - Matt
> >
> >
> >
>|||What's the reason behind dropping and recreating the index? If the reason
is to rebuild it (remove fragmentation), then use DBCC DBREINDEX. This will
rebuild your index and you don't need to deal with dropping constraints.
Gail Erickson [MSFT]
SQL Server User Education
This posting is provided "AS IS" with no warranties, and confers no rights.
"Matthew Sajdera" <sajdera@.pcts.com> wrote in message
news:e2aLycpkDHA.2432@.TK2MSFTNGP10.phx.gbl...
> All -
> Sorry about that. I found the DROP INDEX syntax.
> Now it looks like the only problem is that if the index is a primary
index,
> you can't drop it. You have to drop the table, create the table, and then
> re-create the index.
> Does this sound right?
> Thanks,
> - Matt
>
> "Matthew Sajdera" <sajdera@.pcts.com> wrote in message
> news:eeA8tWpkDHA.360@.TK2MSFTNGP12.phx.gbl...
> > All -
> >
> > You know when you generate the SQL script for a stored procedure, you
get
> > that initial script at the top that first checks for the procedure in
> > sysobjects and drops it if it is found?
> >
> > I want to do the same thing with indexes. I want to create a script
that
> > will first delete a given index if its found and then create a new one.
> >
> > How would you go about do this?
> >
> > Thanks,
> >
> > - Matt
> >
> >
> >
>

Droping/Removing Identity from a Column

I want to remove Identity from a column within my table. I know that I can do it via the Enterprise Manager. But I need to remove it via a script. Does any one know how this can be done?
I've tried the following:
Alter Table FX_Operator_List drop Identity Operator_ID
go
In the above statment I want to drop "Identity" from column "Operator_ID"
in the table "FX_Operator_List".
I can't get it to work.
Sorry, you can't add or remove the identity property through ALTER TABLE.
You can do what enterprise manager does behind the scenes, or a slight
variation:
- add an INT column, move the data, drop the old column, rename the new
column
- create a new table, move the data over, drop the old table, rename the new
table
I'll leave it as an exercise to the reader to figure out which is which...
but both will accomplish the goal.
http://www.aspfaq.com/
(Reverse address to reply.)
"Larry Bird" <LarryBird@.discussions.microsoft.com> wrote in message
news:70EBD2DF-5924-4CBF-9EEC-3B895538A767@.microsoft.com...
> I want to remove Identity from a column within my table. I know that I
can do it via the Enterprise Manager. But I need to remove it via a script.
Does any one know how this can be done?
> I've tried the following:
> Alter Table FX_Operator_List drop Identity Operator_ID
> go
> In the above statment I want to drop "Identity" from column "Operator_ID"
> in the table "FX_Operator_List".
> I can't get it to work.
|||Behind the scenes, EM re-creates the table without the identity property.
You'll have to do the same.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Larry Bird" <LarryBird@.discussions.microsoft.com> wrote in message
news:70EBD2DF-5924-4CBF-9EEC-3B895538A767@.microsoft.com...
I want to remove Identity from a column within my table. I know that I can
do it via the Enterprise Manager. But I need to remove it via a script.
Does any one know how this can be done?
I've tried the following:
Alter Table FX_Operator_List drop Identity Operator_ID
go
In the above statment I want to drop "Identity" from column "Operator_ID"
in the table "FX_Operator_List".
I can't get it to work.
sql

Droping/Removing Identity from a Column

I want to remove Identity from a column within my table. I know that I can do it via the Enterprise Manager. But I need to remove it via a script. Does any one know how this can be done?
I've tried the following:
Alter Table FX_Operator_List drop Identity Operator_ID
go
In the above statment I want to drop "Identity" from column "Operator_ID"
in the table "FX_Operator_List".
I can't get it to work.Sorry, you can't add or remove the identity property through ALTER TABLE.
You can do what enterprise manager does behind the scenes, or a slight
variation:
- add an INT column, move the data, drop the old column, rename the new
column
- create a new table, move the data over, drop the old table, rename the new
table
I'll leave it as an exercise to the reader to figure out which is which...
but both will accomplish the goal.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Larry Bird" <LarryBird@.discussions.microsoft.com> wrote in message
news:70EBD2DF-5924-4CBF-9EEC-3B895538A767@.microsoft.com...
> I want to remove Identity from a column within my table. I know that I
can do it via the Enterprise Manager. But I need to remove it via a script.
Does any one know how this can be done?
> I've tried the following:
> Alter Table FX_Operator_List drop Identity Operator_ID
> go
> In the above statment I want to drop "Identity" from column "Operator_ID"
> in the table "FX_Operator_List".
> I can't get it to work.|||Behind the scenes, EM re-creates the table without the identity property.
You'll have to do the same.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Larry Bird" <LarryBird@.discussions.microsoft.com> wrote in message
news:70EBD2DF-5924-4CBF-9EEC-3B895538A767@.microsoft.com...
I want to remove Identity from a column within my table. I know that I can
do it via the Enterprise Manager. But I need to remove it via a script.
Does any one know how this can be done?
I've tried the following:
Alter Table FX_Operator_List drop Identity Operator_ID
go
In the above statment I want to drop "Identity" from column "Operator_ID"
in the table "FX_Operator_List".
I can't get it to work.

Droping/Removing Identity from a Column

I want to remove Identity from a column within my table. I know that I can
do it via the Enterprise Manager. But I need to remove it via a script. Do
es any one know how this can be done?
I've tried the following:
Alter Table FX_Operator_List drop Identity Operator_ID
go
In the above statment I want to drop "Identity" from column "Operator_ID"
in the table "FX_Operator_List".
I can't get it to work.Sorry, you can't add or remove the identity property through ALTER TABLE.
You can do what enterprise manager does behind the scenes, or a slight
variation:
- add an INT column, move the data, drop the old column, rename the new
column
- create a new table, move the data over, drop the old table, rename the new
table
I'll leave it as an exercise to the reader to figure out which is which...
but both will accomplish the goal.
http://www.aspfaq.com/
(Reverse address to reply.)
"Larry Bird" <LarryBird@.discussions.microsoft.com> wrote in message
news:70EBD2DF-5924-4CBF-9EEC-3B895538A767@.microsoft.com...
> I want to remove Identity from a column within my table. I know that I
can do it via the Enterprise Manager. But I need to remove it via a script.
Does any one know how this can be done?
> I've tried the following:
> Alter Table FX_Operator_List drop Identity Operator_ID
> go
> In the above statment I want to drop "Identity" from column "Operator_ID"
> in the table "FX_Operator_List".
> I can't get it to work.|||Behind the scenes, EM re-creates the table without the identity property.
You'll have to do the same.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Larry Bird" <LarryBird@.discussions.microsoft.com> wrote in message
news:70EBD2DF-5924-4CBF-9EEC-3B895538A767@.microsoft.com...
I want to remove Identity from a column within my table. I know that I can
do it via the Enterprise Manager. But I need to remove it via a script.
Does any one know how this can be done?
I've tried the following:
Alter Table FX_Operator_List drop Identity Operator_ID
go
In the above statment I want to drop "Identity" from column "Operator_ID"
in the table "FX_Operator_List".
I can't get it to work.

drop last bit

Hi,

I have an sql script, which updates some values by using BIT operations..

ex:

UPDATE table1

SET myValue = myValue & ~mask | (availability - mask)...

Problem is, this returns a value, when written to binary, it's one bit too long!

ex:

0 1 1 1 1 1

(= 62)

which should be:

0 1 1 1 1 0

(=31)

How can I "drop" this last BIT?

(Keep in mind that I store these values as LONGINT's)...

Been fighting with this one all day..

Try this...

Code Snippet

select cast(substring(rtrim(convert(char(19),<columnName>)),1,len(rtrim(convert(char(19),<columnName>)))-1) as bigint)

Drop indexes

I have a database with many PK and FK constraints. Is there any script I can use to drop all indexes and rebuild them taking care of PK and dependencies? I am unable to drop them by tablename order. This is on MSSQLServer-2000 SP3. Any help is appreciated.why drop and recreate when you can defrag or reindex? have you looked at DBCC SHOWCONTIG to make you sure need to do this.

btw, you only have to do the clustered ones. the nonclustered get rebuilt when you do this.

here is some code...

SELECT 'DBCC DBREINDEX(' + CAST(o.[name] as varchar(200)) + ',' + CAST(i.[name] as varchar) + ')
GO'
FROM sysindexes i
JOIN sysobjects o
ON i.id = o.id
WHERE o.xtype = 'U'
AND i.indid = 1|||is I have primary clustered indexes on 812 tables and do not know a lot of dependencies if I go by tablename order to drop and recreate indexes. Does DBCC REINDEX drops and recreates PK indexes too? We have run DBCC showcontig and saw fragmentation quite a bit.|||DBCC DBREINDEX does not drop and recreate to knowledge but I would confirm in BOL. It does however lock up tables.

DBCC INDEXDEFRAG is slower but does not lock up the tables so much and the users can work more easily while this goes on.|||DBCC DBREINDEX does not drop and recreate to knowledge but I would confirm in BOL.
It doesn't - in fact, when last I read about all this stuff it was presented as DBREINDEX's USP :D

Drop index error

Dear
I would like to using script to drop index and columns automatically. When I
run the script , it occur a error :
Cannot drop the index 'ADDRESS._WA_Sys_SHORTNAME_5A2A0B13', because it does
not exist in the system catalog.
I don't know what is the problem here and the "XXX_WA_Sys_XXXX" work for,
Can anyone point out the problem and give me a solution. Thanks
And my script is :
Declare Live2StdCur Scroll Cursor For
select Table_Name, Column_Name from Live.Information_Schema.columns Live
where not Exists (select * from Std.Information_Schema.columns Std where
Live.Table_Name = Std.Table_Name and Live.Column_Name = Std.Column_Name)
Order by Table_Name
For Read Only
Open Live2StdCur
Fetch First From Live2StdCur Into @.TableName, @.ColumnName
While @.@.Fetch_Status = 0
Begin
Set @.TableId = Object_id(@.TableName)
Set @.ColumnId = (select colid from syscolumns where id =
Object_id(@.TableName) and name = @.ColumnName )
-- Drop default value
Set @.constraint_name = (select name from sysobjects where parent_obj =
@.TableId and info = @.ColumnId)
EXEC ('ALTER TABLE ' +@.TableName + ' DROP CONSTRAINT ' + @.constraint_name)
-- Drop index
Declare IndexCur Scroll Cursor For
select name from sysindexes where indid in (select indid from sysindexkeys
where id = @.TableId and colid = @.ColumnId) and id = @.TableId
For Read Only
Open IndexCur
Fetch First From IndexCur Into @.index_name
While @.@.Fetch_Status = 0
Begin
EXEC ('Drop Index ' + @.TableName + '.' + @.index_name)
Fetch Next From IndexCur Into @.index_name
End
Close IndexCur
-- Drop Column
EXEC ('ALTER TABLE ' + @.TableName + ' DROP COLUMN ' + @.ColumnName)
-- Show information
print 'Table = ' + @.TableName + ', Column = ' + @.ColumnName
Fetch Next From Live2StdCur Into @.TableName, @.ColumnName
End
Close Live2StdCur
Deallocate IndexCur
Deallocate Live2StdCurTypically the indexes beginning with "_WA_Sys_" are statistics that SQL
Server generates. You ought to exclude them from your script by adding
and INDEXPROPERTY([id], [name], N'IsStatistics') = 0
to your inner cursor where you deal with the indexes.
HTH
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Gary wrote:

>Dear
>I would like to using script to drop index and columns automatically. When
I
>run the script , it occur a error :
>Cannot drop the index 'ADDRESS._WA_Sys_SHORTNAME_5A2A0B13', because it does
>not exist in the system catalog.
>I don't know what is the problem here and the "XXX_WA_Sys_XXXX" work for,
>Can anyone point out the problem and give me a solution. Thanks
>And my script is :
>Declare Live2StdCur Scroll Cursor For
>select Table_Name, Column_Name from Live.Information_Schema.columns Live
>where not Exists (select * from Std.Information_Schema.columns Std where
>Live.Table_Name = Std.Table_Name and Live.Column_Name = Std.Column_Name)
>Order by Table_Name
>For Read Only
>Open Live2StdCur
>Fetch First From Live2StdCur Into @.TableName, @.ColumnName
>While @.@.Fetch_Status = 0
>Begin
>Set @.TableId = Object_id(@.TableName)
>Set @.ColumnId = (select colid from syscolumns where id =
>Object_id(@.TableName) and name = @.ColumnName )
>-- Drop default value
>Set @.constraint_name = (select name from sysobjects where parent_obj =
>@.TableId and info = @.ColumnId)
>EXEC ('ALTER TABLE ' +@.TableName + ' DROP CONSTRAINT ' + @.constraint_name)
>-- Drop index
> Declare IndexCur Scroll Cursor For
> select name from sysindexes where indid in (select indid from sysindexkey
s
>where id = @.TableId and colid = @.ColumnId) and id = @.TableId
> For Read Only
> Open IndexCur
> Fetch First From IndexCur Into @.index_name
> While @.@.Fetch_Status = 0
> Begin
> EXEC ('Drop Index ' + @.TableName + '.' + @.index_name)
> Fetch Next From IndexCur Into @.index_name
> End
> Close IndexCur
>-- Drop Column
>EXEC ('ALTER TABLE ' + @.TableName + ' DROP COLUMN ' + @.ColumnName)
>-- Show information
>print 'Table = ' + @.TableName + ', Column = ' + @.ColumnName
>Fetch Next From Live2StdCur Into @.TableName, @.ColumnName
>End
>Close Live2StdCur
>Deallocate IndexCur
>Deallocate Live2StdCur
>
>
>|||Dear Mike
Thanks for your reply. I have solved the problem
but there is another problem here. It seem like the index type cannot drop
again
The index 'I_698DIMIDX' is dependent on column 'INVENTPROJID'.
Should I add any criteria again on the select statment
Gary
"Mike Hodgson" wrote:

> Typically the indexes beginning with "_WA_Sys_" are statistics that SQL
> Server generates. You ought to exclude them from your script by adding
> and INDEXPROPERTY([id], [name], N'IsStatistics') = 0
> to your inner cursor where you deal with the indexes.
> HTH
> --
> *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> *E* mailto:mike.hodgson@.mallesons.nospam.com |* W* [url]http://www.mallesons.com[/url
]
>
> Gary wrote:
>
>|||Sorry
The whole error message are:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'CONSTRAINT'.
Server: Msg 5074, Level 16, State 8, Line 1
The index 'I_698DIMIDX' is dependent on column 'INVENTPROJID'.
Server: Msg 5074, Level 16, State 1, Line 1
The index 'I_698PROJIDIDX' is dependent on column 'INVENTPROJID'.
Server: Msg 5074, Level 16, State 1, Line 1
The index 'I_698DIMIDX' is dependent on column 'INVENTPROJID'.
Server: Msg 5074, Level 16, State 1, Line 1
The index 'I_698PROJIDIDX' is dependent on column 'INVENTPROJID'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN INVENTPROJID failed because one or more objects
access this column.
"Gary" wrote:
> Dear Mike
> Thanks for your reply. I have solved the problem
> but there is another problem here. It seem like the index type cannot drop
> again
> The index 'I_698DIMIDX' is dependent on column 'INVENTPROJID'.
> Should I add any criteria again on the select statment
> Gary
> "Mike Hodgson" wrote:
>|||Gary,
Cursor loops (especially nested loops) can be difficult to debug. What
I often do, which I find quite helpful especially when building up
complex dynamic strings to EXEC at runtime, is change the "EXEC (...)"
statements to "PRINT (...)" so I can see exactly what T-SQL commands the
SQL server is trying to execute. Then you can take the results of that
(with all the print statements) and, as that should be a valid T-SQL
script, execute the statements either one at a time (in order) or in
small batches to see where it's going wrong. It often becomes blatantly
obvious where the mistake is when you do this.
One thing I notice is you are declaring the inner cursor (IndexCur)
inside the outer WHILE loop but you're only deallocating it outside the
outer loop. The OPEN & CLOSE are fine and open & close the resultset
appropriately, but how you've got the DECLARE & DEALLOCATE I would think
would result in the same cursor being declared multiple times but the
resources used by the cursor would not get released each time. SQL
Server may be able to handle this odd case (not sure), but in most
programming languages that would result in a memory/resource leak.
I think the problem is in the fact that you're using variables in the
declaration of your inner cursor (IndexCur) but since you're not
deallocating the cursor before you declare it again (i.e. at the end of
the inner loop), the different variable values each time through the
outer loop will not be taken into account when the cursor is declared
again. This would mean that for each iteration of the outer loop, the
inner cursor would have the same declaration and so you'd be working on
the same resultset for IndexCur each time...I think. BOL describes this
on its "DECLARE CURSOR" page:
Variables may be used as part of the /select_statement/ that
declares a cursor. Cursor variable values do not change after a
cursor is declared. In SQL Server version 6.5 and earlier, variable
values are refreshed every time a cursor is reopened.
I may be off-base with this thought but you should be able to tell if
this is the case or not pretty quickly by changing your "EXEC (...)"
statements to "PRINT (...)" and looking at the resultant T-SQL statements.
It's always a good idea to have your DECLARE/DEALLOCATE and OPEN/CLOSE
statements at the same scope as each other so they're always a matching
pair in terms of scope.
Also, there's not much point in making the cursors SCROLL cursors since
the only operation you're doing on them is FETCH NEXT (the FETCH FIRST
statements in this context do the same as a FETCH NEXT).
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Gary wrote:
>Sorry
>The whole error message are:
>Server: Msg 170, Level 15, State 1, Line 1
>Line 1: Incorrect syntax near 'CONSTRAINT'.
>Server: Msg 5074, Level 16, State 8, Line 1
>The index 'I_698DIMIDX' is dependent on column 'INVENTPROJID'.
>Server: Msg 5074, Level 16, State 1, Line 1
>The index 'I_698PROJIDIDX' is dependent on column 'INVENTPROJID'.
>Server: Msg 5074, Level 16, State 1, Line 1
>The index 'I_698DIMIDX' is dependent on column 'INVENTPROJID'.
>Server: Msg 5074, Level 16, State 1, Line 1
>The index 'I_698PROJIDIDX' is dependent on column 'INVENTPROJID'.
>Server: Msg 4922, Level 16, State 1, Line 1
>ALTER TABLE DROP COLUMN INVENTPROJID failed because one or more objects
>access this column.
>
>"Gary" wrote:
>
>

Drop identitycolumn

hi.

i want to drop identity column in a table but not though design view, so plz tell the script for that.

Unfortunately, you cannot 'just' remove the IDENTITY property of a column. You have to completely remove the column.

Code Snippet

ALTER TABLE MyTable

DROP COLUMN MyColumn

If you wish to keep the values in the column, and just remove the IDENTITY property, you must first add another column, copy the existing values to the new column, and then DROP the existing IDENTITY column.

|||

This resource might be helpful:

http://www.bennadel.com/index.cfm?dax=blog:24.view

DROP IDENTITY from tables

Hello,
I have two questions.
My goal is to create a script which drops identities for multiple tables in
a database. So for example if: alter table alter column X int NO IDENTITY
was valid...which is not I would be all set. Dropping and Recreating the
column could work as long as the column gets added in the same order from
where it was deleted, but I was hoping for something sleaker (see code below).
In process of testing some scenarios for the above I uncovered the following
"weirdness"
I created a test table with an identity field and I execute the following
select:
select * from syscolumns c inner join sysobjects o on c.id = o.id
where o.name = 'test'
The result set shows one row for each field. The data on the identity field
row seems ok up to column "usertype" but all remaining data values are
shifted one position out. So value for "prec" shows under "scale".. and so
on for 32 fields
I bet you can recreate this issue as well. Create a table with one identity
field and run the select sql below.
Question 1: Is this is a bug?
Question 2: Is the code below appropriate to drop identities or are there
any issues with updating the syscolumns table this way.
sp_configure 'allow updates', 1;
RECONFIGURE WITH OVERRIDE;
update syscolumns
set colstat = 0,
autoval = NULL
where id = (select id from sysobjects where name = 'test')
and colstat = 1
sp_configure 'allow updates', 0;
RECONFIGURE;
Thank You in advance,
John
John,
Updating the system table is usually not a good practice. Can you just
create a new table and load the data from the existing table (once for each
table)? If you do decide to update the system tables I'd recommend you
backup the database first.
HTH
Jerry
"John K" <JohnK@.discussions.microsoft.com> wrote in message
news:8AC208F0-EB75-424B-A2D8-9D94A01885D5@.microsoft.com...
> Hello,
> I have two questions.
> My goal is to create a script which drops identities for multiple tables
> in
> a database. So for example if: alter table alter column X int NO IDENTITY
> was valid...which is not I would be all set. Dropping and Recreating the
> column could work as long as the column gets added in the same order from
> where it was deleted, but I was hoping for something sleaker (see code
> below).
> In process of testing some scenarios for the above I uncovered the
> following
> "weirdness"
> I created a test table with an identity field and I execute the following
> select:
> select * from syscolumns c inner join sysobjects o on c.id = o.id
> where o.name = 'test'
> The result set shows one row for each field. The data on the identity
> field
> row seems ok up to column "usertype" but all remaining data values are
> shifted one position out. So value for "prec" shows under "scale".. and
> so
> on for 32 fields
> I bet you can recreate this issue as well. Create a table with one
> identity
> field and run the select sql below.
> Question 1: Is this is a bug?
> --
> Question 2: Is the code below appropriate to drop identities or are there
> any issues with updating the syscolumns table this way.
> sp_configure 'allow updates', 1;
> RECONFIGURE WITH OVERRIDE;
> update syscolumns
> set colstat = 0,
> autoval = NULL
> where id = (select id from sysobjects where name = 'test')
> and colstat = 1
> sp_configure 'allow updates', 0;
> RECONFIGURE;
> --
> Thank You in advance,
> John
|||"John K" <JohnK@.discussions.microsoft.com> wrote in message
news:8AC208F0-EB75-424B-A2D8-9D94A01885D5@.microsoft.com...
> Hello,
> I have two questions.
> My goal is to create a script which drops identities for multiple tables
> in
> a database. So for example if: alter table alter column X int NO IDENTITY
> was valid...which is not I would be all set. Dropping and Recreating the
> column could work as long as the column gets added in the same order from
> where it was deleted, but I was hoping for something sleaker (see code
> below).
> In process of testing some scenarios for the above I uncovered the
> following
> "weirdness"
> I created a test table with an identity field and I execute the following
> select:
> select * from syscolumns c inner join sysobjects o on c.id = o.id
> where o.name = 'test'
> The result set shows one row for each field. The data on the identity
> field
> row seems ok up to column "usertype" but all remaining data values are
> shifted one position out. So value for "prec" shows under "scale".. and
> so
> on for 32 fields
> I bet you can recreate this issue as well. Create a table with one
> identity
> field and run the select sql below.
> Question 1: Is this is a bug?
> --
> Question 2: Is the code below appropriate to drop identities or are there
> any issues with updating the syscolumns table this way.
> sp_configure 'allow updates', 1;
> RECONFIGURE WITH OVERRIDE;
> update syscolumns
> set colstat = 0,
> autoval = NULL
> where id = (select id from sysobjects where name = 'test')
> and colstat = 1
> sp_configure 'allow updates', 0;
> RECONFIGURE;
> --
> Thank You in advance,
> John
Updating system tables directly is the easiest route to corrupt and
unrecoverable data.
Add a new column. Populate it. Drop the old column. Forget about modifying
system tables.
David Portas
SQL Server MVP
|||John K wrote:
> Hello,
> I have two questions.
> SNIP
Create a new table with the same ddl, sans identity attribute. Insert
the data from old to new. Create indexes. Drop the old table. Rename the
new table. Run sp_recompile on each procedure that accesses the table.
If you have any DRI involved, it makes the process more difficult.
Messing with the system tables is a surefire way to cause the database
to be marked suspect or cause any number of other operational / support
issues.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Ok, no system table changes.
I don't care to move the data, but I do have hundreds of tables and I do
need the new column to be at the same physical order the old one existed. So
how do I script this operation to create a new column of int and drop the old
identity one in the same spot (column order)?
John
"David Gugick" wrote:

> John K wrote:
> Create a new table with the same ddl, sans identity attribute. Insert
> the data from old to new. Create indexes. Drop the old table. Rename the
> new table. Run sp_recompile on each procedure that accesses the table.
> If you have any DRI involved, it makes the process more difficult.
> Messing with the system tables is a surefire way to cause the database
> to be marked suspect or cause any number of other operational / support
> issues.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
|||John K wrote:
> Ok, no system table changes.
> I don't care to move the data, but I do have hundreds of tables and I
> do need the new column to be at the same physical order the old one
> existed. So how do I script this operation to create a new column of
> int and drop the old identity one in the same spot (column order)?
You need to create a new table as I mentioned in the previous post. You
can use the INFORMATION_SCHEMA.COLUMNS view to query the table and sort
by ORDINAL_POSITION to get the correct order. You can do this in your
favorite development tool (my preference) or do this from a stored
procedure using dynamic sql (a valid alternative, but the coding and
debugging is less elegant).
Indexes are more involved, but that information is also available from
the INFORMATION_SCHEMA views. Keys make it more involved as well. Also,
pay attention to the location of tables / clustered indexes and
non-clustered indexes / keys so you don't end up creating these objects
in a different location from where they were originally located.
If you want to see how SQL Enterprise does this, create a test table
with an identity column and change the identity attribute. Rather than
applying the change, have SQL EM script out the T-SQL.
Why is the physical order important? Physical positioning should not be
of any importance. You can always query the columns in the order you'd
like to see them returned.
David Gugick
Quest Software
www.imceda.com
www.quest.com

DROP IDENTITY from tables

Hello,
I have two questions.
My goal is to create a script which drops identities for multiple tables in
a database. So for example if: alter table alter column X int NO IDENTITY
was valid...which is not I would be all set. Dropping and Recreating the
column could work as long as the column gets added in the same order from
where it was deleted, but I was hoping for something sleaker (see code below
).
In process of testing some scenarios for the above I uncovered the following
"weirdness"
I created a test table with an identity field and I execute the following
select:
select * from syscolumns c inner join sysobjects o on c.id = o.id
where o.name = 'test'
The result set shows one row for each field. The data on the identity field
row seems ok up to column "usertype" but all remaining data values are
shifted one position out. So value for "prec" shows under "scale".. and so
on for 32 fields
I bet you can recreate this issue as well. Create a table with one identity
field and run the select sql below.
Question 1: Is this is a bug?
Question 2: Is the code below appropriate to drop identities or are there
any issues with updating the syscolumns table this way.
sp_configure 'allow updates', 1;
RECONFIGURE WITH OVERRIDE;
update syscolumns
set colstat = 0,
autoval = NULL
where id = (select id from sysobjects where name = 'test')
and colstat = 1
sp_configure 'allow updates', 0;
RECONFIGURE;
Thank You in advance,
JohnJohn,
Updating the system table is usually not a good practice. Can you just
create a new table and load the data from the existing table (once for each
table)? If you do decide to update the system tables I'd recommend you
backup the database first.
HTH
Jerry
"John K" <JohnK@.discussions.microsoft.com> wrote in message
news:8AC208F0-EB75-424B-A2D8-9D94A01885D5@.microsoft.com...
> Hello,
> I have two questions.
> My goal is to create a script which drops identities for multiple tables
> in
> a database. So for example if: alter table alter column X int NO IDENTITY
> was valid...which is not I would be all set. Dropping and Recreating the
> column could work as long as the column gets added in the same order from
> where it was deleted, but I was hoping for something sleaker (see code
> below).
> In process of testing some scenarios for the above I uncovered the
> following
> "weirdness"
> I created a test table with an identity field and I execute the following
> select:
> select * from syscolumns c inner join sysobjects o on c.id = o.id
> where o.name = 'test'
> The result set shows one row for each field. The data on the identity
> field
> row seems ok up to column "usertype" but all remaining data values are
> shifted one position out. So value for "prec" shows under "scale".. and
> so
> on for 32 fields
> I bet you can recreate this issue as well. Create a table with one
> identity
> field and run the select sql below.
> Question 1: Is this is a bug?
> --
> Question 2: Is the code below appropriate to drop identities or are there
> any issues with updating the syscolumns table this way.
> sp_configure 'allow updates', 1;
> RECONFIGURE WITH OVERRIDE;
> update syscolumns
> set colstat = 0,
> autoval = NULL
> where id = (select id from sysobjects where name = 'test')
> and colstat = 1
> sp_configure 'allow updates', 0;
> RECONFIGURE;
> --
> Thank You in advance,
> John|||"John K" <JohnK@.discussions.microsoft.com> wrote in message
news:8AC208F0-EB75-424B-A2D8-9D94A01885D5@.microsoft.com...
> Hello,
> I have two questions.
> My goal is to create a script which drops identities for multiple tables
> in
> a database. So for example if: alter table alter column X int NO IDENTITY
> was valid...which is not I would be all set. Dropping and Recreating the
> column could work as long as the column gets added in the same order from
> where it was deleted, but I was hoping for something sleaker (see code
> below).
> In process of testing some scenarios for the above I uncovered the
> following
> "weirdness"
> I created a test table with an identity field and I execute the following
> select:
> select * from syscolumns c inner join sysobjects o on c.id = o.id
> where o.name = 'test'
> The result set shows one row for each field. The data on the identity
> field
> row seems ok up to column "usertype" but all remaining data values are
> shifted one position out. So value for "prec" shows under "scale".. and
> so
> on for 32 fields
> I bet you can recreate this issue as well. Create a table with one
> identity
> field and run the select sql below.
> Question 1: Is this is a bug?
> --
> Question 2: Is the code below appropriate to drop identities or are there
> any issues with updating the syscolumns table this way.
> sp_configure 'allow updates', 1;
> RECONFIGURE WITH OVERRIDE;
> update syscolumns
> set colstat = 0,
> autoval = NULL
> where id = (select id from sysobjects where name = 'test')
> and colstat = 1
> sp_configure 'allow updates', 0;
> RECONFIGURE;
> --
> Thank You in advance,
> John
Updating system tables directly is the easiest route to corrupt and
unrecoverable data.
Add a new column. Populate it. Drop the old column. Forget about modifying
system tables.
David Portas
SQL Server MVP
--|||John K wrote:
> Hello,
> I have two questions.
> SNIP
Create a new table with the same ddl, sans identity attribute. Insert
the data from old to new. Create indexes. Drop the old table. Rename the
new table. Run sp_recompile on each procedure that accesses the table.
If you have any DRI involved, it makes the process more difficult.
Messing with the system tables is a surefire way to cause the database
to be marked suspect or cause any number of other operational / support
issues.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Ok, no system table changes.
I don't care to move the data, but I do have hundreds of tables and I do
need the new column to be at the same physical order the old one existed. S
o
how do I script this operation to create a new column of int and drop the ol
d
identity one in the same spot (column order)?
John
"David Gugick" wrote:

> John K wrote:
> Create a new table with the same ddl, sans identity attribute. Insert
> the data from old to new. Create indexes. Drop the old table. Rename the
> new table. Run sp_recompile on each procedure that accesses the table.
> If you have any DRI involved, it makes the process more difficult.
> Messing with the system tables is a surefire way to cause the database
> to be marked suspect or cause any number of other operational / support
> issues.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||John K wrote:
> Ok, no system table changes.
> I don't care to move the data, but I do have hundreds of tables and I
> do need the new column to be at the same physical order the old one
> existed. So how do I script this operation to create a new column of
> int and drop the old identity one in the same spot (column order)?
You need to create a new table as I mentioned in the previous post. You
can use the INFORMATION_SCHEMA.COLUMNS view to query the table and sort
by ORDINAL_POSITION to get the correct order. You can do this in your
favorite development tool (my preference) or do this from a stored
procedure using dynamic sql (a valid alternative, but the coding and
debugging is less elegant).
Indexes are more involved, but that information is also available from
the INFORMATION_SCHEMA views. Keys make it more involved as well. Also,
pay attention to the location of tables / clustered indexes and
non-clustered indexes / keys so you don't end up creating these objects
in a different location from where they were originally located.
If you want to see how SQL Enterprise does this, create a test table
with an identity column and change the identity attribute. Rather than
applying the change, have SQL EM script out the T-SQL.
Why is the physical order important? Physical positioning should not be
of any importance. You can always query the columns in the order you'd
like to see them returned.
David Gugick
Quest Software
www.imceda.com
www.quest.com

DROP IDENTITY from tables

Hello,
I have two questions.
My goal is to create a script which drops identities for multiple tables in
a database. So for example if: alter table alter column X int NO IDENTITY
was valid...which is not I would be all set. Dropping and Recreating the
column could work as long as the column gets added in the same order from
where it was deleted, but I was hoping for something sleaker (see code below).
In process of testing some scenarios for the above I uncovered the following
"weirdness"
I created a test table with an identity field and I execute the following
select:
select * from syscolumns c inner join sysobjects o on c.id = o.id
where o.name = 'test'
The result set shows one row for each field. The data on the identity field
row seems ok up to column "usertype" but all remaining data values are
shifted one position out. So value for "prec" shows under "scale".. and so
on for 32 fields
I bet you can recreate this issue as well. Create a table with one identity
field and run the select sql below.
Question 1: Is this is a bug?
--
Question 2: Is the code below appropriate to drop identities or are there
any issues with updating the syscolumns table this way.
sp_configure 'allow updates', 1;
RECONFIGURE WITH OVERRIDE;
update syscolumns
set colstat = 0,
autoval = NULL
where id = (select id from sysobjects where name = 'test')
and colstat = 1
sp_configure 'allow updates', 0;
RECONFIGURE;
--
Thank You in advance,
JohnJohn,
Updating the system table is usually not a good practice. Can you just
create a new table and load the data from the existing table (once for each
table)? If you do decide to update the system tables I'd recommend you
backup the database first.
HTH
Jerry
"John K" <JohnK@.discussions.microsoft.com> wrote in message
news:8AC208F0-EB75-424B-A2D8-9D94A01885D5@.microsoft.com...
> Hello,
> I have two questions.
> My goal is to create a script which drops identities for multiple tables
> in
> a database. So for example if: alter table alter column X int NO IDENTITY
> was valid...which is not I would be all set. Dropping and Recreating the
> column could work as long as the column gets added in the same order from
> where it was deleted, but I was hoping for something sleaker (see code
> below).
> In process of testing some scenarios for the above I uncovered the
> following
> "weirdness"
> I created a test table with an identity field and I execute the following
> select:
> select * from syscolumns c inner join sysobjects o on c.id = o.id
> where o.name = 'test'
> The result set shows one row for each field. The data on the identity
> field
> row seems ok up to column "usertype" but all remaining data values are
> shifted one position out. So value for "prec" shows under "scale".. and
> so
> on for 32 fields
> I bet you can recreate this issue as well. Create a table with one
> identity
> field and run the select sql below.
> Question 1: Is this is a bug?
> --
> Question 2: Is the code below appropriate to drop identities or are there
> any issues with updating the syscolumns table this way.
> sp_configure 'allow updates', 1;
> RECONFIGURE WITH OVERRIDE;
> update syscolumns
> set colstat = 0,
> autoval = NULL
> where id = (select id from sysobjects where name = 'test')
> and colstat = 1
> sp_configure 'allow updates', 0;
> RECONFIGURE;
> --
> Thank You in advance,
> John|||"John K" <JohnK@.discussions.microsoft.com> wrote in message
news:8AC208F0-EB75-424B-A2D8-9D94A01885D5@.microsoft.com...
> Hello,
> I have two questions.
> My goal is to create a script which drops identities for multiple tables
> in
> a database. So for example if: alter table alter column X int NO IDENTITY
> was valid...which is not I would be all set. Dropping and Recreating the
> column could work as long as the column gets added in the same order from
> where it was deleted, but I was hoping for something sleaker (see code
> below).
> In process of testing some scenarios for the above I uncovered the
> following
> "weirdness"
> I created a test table with an identity field and I execute the following
> select:
> select * from syscolumns c inner join sysobjects o on c.id = o.id
> where o.name = 'test'
> The result set shows one row for each field. The data on the identity
> field
> row seems ok up to column "usertype" but all remaining data values are
> shifted one position out. So value for "prec" shows under "scale".. and
> so
> on for 32 fields
> I bet you can recreate this issue as well. Create a table with one
> identity
> field and run the select sql below.
> Question 1: Is this is a bug?
> --
> Question 2: Is the code below appropriate to drop identities or are there
> any issues with updating the syscolumns table this way.
> sp_configure 'allow updates', 1;
> RECONFIGURE WITH OVERRIDE;
> update syscolumns
> set colstat = 0,
> autoval = NULL
> where id = (select id from sysobjects where name = 'test')
> and colstat = 1
> sp_configure 'allow updates', 0;
> RECONFIGURE;
> --
> Thank You in advance,
> John
Updating system tables directly is the easiest route to corrupt and
unrecoverable data.
Add a new column. Populate it. Drop the old column. Forget about modifying
system tables.
--
David Portas
SQL Server MVP
--|||John K wrote:
> Hello,
> I have two questions.
> SNIP
Create a new table with the same ddl, sans identity attribute. Insert
the data from old to new. Create indexes. Drop the old table. Rename the
new table. Run sp_recompile on each procedure that accesses the table.
If you have any DRI involved, it makes the process more difficult.
Messing with the system tables is a surefire way to cause the database
to be marked suspect or cause any number of other operational / support
issues.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Ok, no system table changes.
I don't care to move the data, but I do have hundreds of tables and I do
need the new column to be at the same physical order the old one existed. So
how do I script this operation to create a new column of int and drop the old
identity one in the same spot (column order)?
--
John
"David Gugick" wrote:
> John K wrote:
> > Hello,
> >
> > I have two questions.
> >
> > SNIP
> Create a new table with the same ddl, sans identity attribute. Insert
> the data from old to new. Create indexes. Drop the old table. Rename the
> new table. Run sp_recompile on each procedure that accesses the table.
> If you have any DRI involved, it makes the process more difficult.
> Messing with the system tables is a surefire way to cause the database
> to be marked suspect or cause any number of other operational / support
> issues.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||John K wrote:
> Ok, no system table changes.
> I don't care to move the data, but I do have hundreds of tables and I
> do need the new column to be at the same physical order the old one
> existed. So how do I script this operation to create a new column of
> int and drop the old identity one in the same spot (column order)?
You need to create a new table as I mentioned in the previous post. You
can use the INFORMATION_SCHEMA.COLUMNS view to query the table and sort
by ORDINAL_POSITION to get the correct order. You can do this in your
favorite development tool (my preference) or do this from a stored
procedure using dynamic sql (a valid alternative, but the coding and
debugging is less elegant).
Indexes are more involved, but that information is also available from
the INFORMATION_SCHEMA views. Keys make it more involved as well. Also,
pay attention to the location of tables / clustered indexes and
non-clustered indexes / keys so you don't end up creating these objects
in a different location from where they were originally located.
If you want to see how SQL Enterprise does this, create a test table
with an identity column and change the identity attribute. Rather than
applying the change, have SQL EM script out the T-SQL.
Why is the physical order important? Physical positioning should not be
of any importance. You can always query the columns in the order you'd
like to see them returned.
David Gugick
Quest Software
www.imceda.com
www.quest.com

Drop extended property "MS_Description" of ALL tables and ALL columns

Hi,

Is there an easy way (a sql script) to drop the "MS_Description" of all tables and all columns in my database?

Regards,
Alejandroo

These queries will produce a script that you can run to drop these properties. You can automate with a cursor, or you might want to add a GO to the end of each EXEC statement.

--tables

select 'EXEC sp_dropextendedproperty

@.name = ''MS_Description''

,@.level0type = ''schema''

,@.level0name = ' + object_schema_name(extended_properties.major_id) + '

,@.level1type = ''table''

,@.level1name = ' + object_name(extended_properties.major_id)

from sys.extended_properties

where extended_properties.class_desc = 'OBJECT_OR_COLUMN'

and extended_properties.minor_id = 0

and extended_properties.name = 'MS_Description'

--columns

select 'EXEC sp_dropextendedproperty

@.name = ''MS_Description''

,@.level0type = ''schema''

,@.level0name = ' + object_schema_name(extended_properties.major_id) + '

,@.level1type = ''table''

,@.level1name = ' + object_name(extended_properties.major_id) + '

,@.level2type = ''column''

,@.level2name = ' + columns.name

from sys.extended_properties

join sys.columns

on columns.object_id = extended_properties.major_id

and columns.column_id = extended_properties.minor_id

where extended_properties.class_desc = 'OBJECT_OR_COLUMN'

and extended_properties.minor_id > 0

and extended_properties.name = 'MS_Description'

|||Thanks a lot Louis, this works perfectly!