Delete duplicate entries from tables in my database using Query Analyzer

Hello,

How can I delete duplicate entries from tables in my database using Query Analyzer, as there are many duplicate entries in my tables, I want to delete them.

Thanks in advance,
Uday.Does this table contains any unique key or any other key field?|||Hi,

There is seperate id for each entries but duplicate entries have the same id number.

Thanks in advance,
Uday.|||One solution could be adding identity column to this and then deleting the non relevent data.|||You can move all the duplicate ones into a separate temp table using GROUP BY HAVING COUNT(*)>1

Then you delete them using the same clause can use a SELECT DISTINCT to copy them back from the temp table.

Of course if your table is small you can just copy the lot and do a SELECT DISTINCT back!

Delete data from sys.columns in SQL Server 2005

Hello,
My understanding is that modifying data in system tables in SQL Server 2005
isn't allowed. Currently, I have an issue where a full dbcc checkdb is
failing due to records existing in sys.columns, but not in sys.objects.
I believe these orphaned rows are a result of a delete statement being ran
against the SQL Server 2000 sysobjects table prior to a SQL Server 2005
upgrade. This delete removed all objects from sysobjects which were owned by
a specific user id as the user was to be removed from the system. This
delete apparently left records behind for 2 of these user tables in
syscolumns.
I have gone through the results of the dbcc checkdb and every inconsistency
is in reference to these 2 object IDs existing in sys.columns, but not
sys.objects. In SQL Server 2000 I would just remove these records from
syscolumns and I believe I'd be good. Since this is causing the full dbcc
checkdb to fail, I'd like to see what options are available for removing
these records. Any help would be greatly appreciated.
Also, while a full dbcc checkdb fails due to this catalog inconsistency, a
dbcc checkdb with physical_only comes back clean. I assume this is due to a
physical_only not performing a catalog check. Would this inconsistency cause
a database restore to fail?
Thanks,
Greg
Check BOL for the following options for DBCC CHECKDB:
REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
Also, have you actually TRIED to delete the offending rows yourself?
Although MS says not supported . . . you never know! Attempt on a TEST
server first! :-)
TheSQLGuru
President
Indicium Resources, Inc.
"gj111" <gj111@.discussions.microsoft.com> wrote in message
news:D7531D91-C796-45F4-B81D-1C3836B2CB64@.microsoft.com...
> Hello,
> My understanding is that modifying data in system tables in SQL Server
> 2005
> isn't allowed. Currently, I have an issue where a full dbcc checkdb is
> failing due to records existing in sys.columns, but not in sys.objects.
> I believe these orphaned rows are a result of a delete statement being ran
> against the SQL Server 2000 sysobjects table prior to a SQL Server 2005
> upgrade. This delete removed all objects from sysobjects which were owned
> by
> a specific user id as the user was to be removed from the system. This
> delete apparently left records behind for 2 of these user tables in
> syscolumns.
> I have gone through the results of the dbcc checkdb and every
> inconsistency
> is in reference to these 2 object IDs existing in sys.columns, but not
> sys.objects. In SQL Server 2000 I would just remove these records from
> syscolumns and I believe I'd be good. Since this is causing the full dbcc
> checkdb to fail, I'd like to see what options are available for removing
> these records. Any help would be greatly appreciated.
> Also, while a full dbcc checkdb fails due to this catalog inconsistency, a
> dbcc checkdb with physical_only comes back clean. I assume this is due to
> a
> physical_only not performing a catalog check. Would this inconsistency
> cause
> a database restore to fail?
> Thanks,
> Greg
|||Thanks for the response. I have tried setting 'allow updates' to 1 and
deleting these rows from sys.columns, but it errors out with "Ad hoc updates
to system catalogs are not allowed. I'd prefer not to try any of the checkdb
repair options as I'm not sure whether this removes just the specific rows
that I'd like to delete or whole pages with other data that I don't want to
remove.
If it's not possible to delete these rows, it looks like the only option to
correct the inconsistency within these system tables is to export all of the
user objects/data into a new database. I can understand the reasoning behind
Microsoft taking away the ability to mess around with system tables. But,
considering that this database is just over 900GB, exporting seems to be an
exceedingly painful solution compared to just deleting all rows from a table
where object_id = object_id. I guess the other option is to just live with
the dbcc checkdb errors every week and keep track of the output to make sure
that there aren't any additional errors. Hopefully there's another way.
Thanks,
Greg
"TheSQLGuru" wrote:

> Check BOL for the following options for DBCC CHECKDB:
> REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
> Also, have you actually TRIED to delete the offending rows yourself?
> Although MS says not supported . . . you never know! Attempt on a TEST
> server first! :-)
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "gj111" <gj111@.discussions.microsoft.com> wrote in message
> news:D7531D91-C796-45F4-B81D-1C3836B2CB64@.microsoft.com...
>
>
|||On 23 Mar, 23:54, gj111 <g...@.discussions.microsoft.com> wrote:
> If it's not possible to delete these rows, it looks like the only option to
> correct the inconsistency within these system tables is to export all of the
> user objects/data into a new database. I can understand the reasoning behind
> Microsoft taking away the ability to mess around with system tables. But,
> considering that this database is just over 900GB, exporting seems to be an
> exceedingly painful solution compared to just deleting all rows from a table
> where object_id = object_id. I guess the other option is to just live with
> the dbcc checkdb errors every week and keep track of the output to make sure
> that there aren't any additional errors. Hopefully there's another way.
>
Exporting the data would indeed be a painful solution but maybe it is
the only way, I really don't know. Obviously this demonstrates why it
was an incredibly bad idea to do what you did in the first place. Why
would you ever resort to deleting data directly from sysobjects? Doing
that with critical data (especially with 900GB of critical data!)
seems like a pretty irresponsible action on the part of someone.
I mention this in case you are still looking for the right solution
for whatever it is you were trying to achieve - as well as to satisfy
my own surprise and curiosity.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||Thanks for the feedback Tibor. I think you're probably right.
"Tibor Karaszi" wrote:

> Allow updates is indeed a dummy config in 2005. It is only there for the command not to error, it
> will not allow updates of the system tables. I would suggest using a backup on which you test DBCC
> with repair option. I'm not sure whether DBCC repair will attempt repair system table
> inconsistencies. I understand it can be a hassle considering the database size. You could open a
> case with MS Support, but to be honest, I wouldn't expect them to have much more to suggest.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "gj111" <gj111@.discussions.microsoft.com> wrote in message
> news:FBA5FECF-4B16-43B6-8021-7063EB6329F1@.microsoft.com...
>
|||Well, thanks for the comments David, although not very helpful. I wasn't
actually the person who originally deleted these records, but I am left to
figure this out after the checkdb came back with errors.
From what I understand, this was done to try and drop 20,000+ objects that
were owned by a particular user id as this user was to be dropped.
"David Portas" wrote:

> On 23 Mar, 23:54, gj111 <g...@.discussions.microsoft.com> wrote:
> Exporting the data would indeed be a painful solution but maybe it is
> the only way, I really don't know. Obviously this demonstrates why it
> was an incredibly bad idea to do what you did in the first place. Why
> would you ever resort to deleting data directly from sysobjects? Doing
> that with critical data (especially with 900GB of critical data!)
> seems like a pretty irresponsible action on the part of someone.
> I mention this in case you are still looking for the right solution
> for whatever it is you were trying to achieve - as well as to satisfy
> my own surprise and curiosity.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
|||On 24 Mar, 14:38, gj111 <g...@.discussions.microsoft.com> wrote:
> From what I understand, this was done to try and drop 20,000+ objects that
> were owned by a particular user id as this user was to be dropped.
>
Then it was perhaps due to ignorance and inexperience rather than pure
recklessness. Surely the quick, easy and safe way would have been to
script some DROP statements from the catalogue to do the same job. Or
to use sp_changeobjectowner.
As you are the person who has to do the mopping up, you might wonder
what other horrors you are going to discover. Good luck!
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||I would recommend taking the original suggestion of contacting Microsoft
support. They do have ways of modifying the system tables but unless you
really know what you're doing, modifying deleting these rows may make the
problem worse. If nothing else, there are a lot of disk pages allocated to
those 20,000 tables that are no longer addressable and will need to be
cleaned up.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"gj111" <gj111@.discussions.microsoft.com> wrote in message
news:94385EB4-D924-44C3-B7DC-36B609294E51@.microsoft.com...[vbcol=seagreen]
> Well, thanks for the comments David, although not very helpful. I wasn't
> actually the person who originally deleted these records, but I am left to
> figure this out after the checkdb came back with errors.
> From what I understand, this was done to try and drop 20,000+ objects that
> were owned by a particular user id as this user was to be dropped.
> "David Portas" wrote:
|||I discussed this with Greg offline and the solution I offered (which is
unfortunately undocumented) seems to have done the trick without any
size-of-data operations. It only worked because the corruption is totally
benign in this case - so its not something that I can publicize (although
you're welcome to email me through the blog link below)
Best way to cope with this once its happened is to call Product Support and
have them help you (or email me if you don't need an instantaneous
response). Best way to avoid it is to educate your DBAs not to mess with the
system tables...
Thanks
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"gj111" <gj111@.discussions.microsoft.com> wrote in message
news:94385EB4-D924-44C3-B7DC-36B609294E51@.microsoft.com...[vbcol=seagreen]
> Well, thanks for the comments David, although not very helpful. I wasn't
> actually the person who originally deleted these records, but I am left to
> figure this out after the checkdb came back with errors.
> From what I understand, this was done to try and drop 20,000+ objects that
> were owned by a particular user id as this user was to be dropped.
> "David Portas" wrote:
|||Thanks Roger. I'll definitely get with support. I'm also in Orlando this
week, maybe I'll get lucky and get a chance to pick someone's brain.
Thanks,
Greg
"Roger Wolter[MSFT]" wrote:

> I would recommend taking the original suggestion of contacting Microsoft
> support. They do have ways of modifying the system tables but unless you
> really know what you're doing, modifying deleting these rows may make the
> problem worse. If nothing else, there are a lot of disk pages allocated to
> those 20,000 tables that are no longer addressable and will need to be
> cleaned up.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "gj111" <gj111@.discussions.microsoft.com> wrote in message
> news:94385EB4-D924-44C3-B7DC-36B609294E51@.microsoft.com...
>

Delete data from sys.columns in SQL Server 2005

Hello,
My understanding is that modifying data in system tables in SQL Server 2005
isn't allowed. Currently, I have an issue where a full dbcc checkdb is
failing due to records existing in sys.columns, but not in sys.objects.
I believe these orphaned rows are a result of a delete statement being ran
against the SQL Server 2000 sysobjects table prior to a SQL Server 2005
upgrade. This delete removed all objects from sysobjects which were owned by
a specific user id as the user was to be removed from the system. This
delete apparently left records behind for 2 of these user tables in
syscolumns.
I have gone through the results of the dbcc checkdb and every inconsistency
is in reference to these 2 object IDs existing in sys.columns, but not
sys.objects. In SQL Server 2000 I would just remove these records from
syscolumns and I believe I'd be good. Since this is causing the full dbcc
checkdb to fail, I'd like to see what options are available for removing
these records. Any help would be greatly appreciated.
Also, while a full dbcc checkdb fails due to this catalog inconsistency, a
dbcc checkdb with physical_only comes back clean. I assume this is due to a
physical_only not performing a catalog check. Would this inconsistency cause
a database restore to fail?
Thanks,
GregCheck BOL for the following options for DBCC CHECKDB:
REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
Also, have you actually TRIED to delete the offending rows yourself?
Although MS says not supported . . . you never know! Attempt on a TEST
server first! :-)
--
TheSQLGuru
President
Indicium Resources, Inc.
"gj111" <gj111@.discussions.microsoft.com> wrote in message
news:D7531D91-C796-45F4-B81D-1C3836B2CB64@.microsoft.com...
> Hello,
> My understanding is that modifying data in system tables in SQL Server
> 2005
> isn't allowed. Currently, I have an issue where a full dbcc checkdb is
> failing due to records existing in sys.columns, but not in sys.objects.
> I believe these orphaned rows are a result of a delete statement being ran
> against the SQL Server 2000 sysobjects table prior to a SQL Server 2005
> upgrade. This delete removed all objects from sysobjects which were owned
> by
> a specific user id as the user was to be removed from the system. This
> delete apparently left records behind for 2 of these user tables in
> syscolumns.
> I have gone through the results of the dbcc checkdb and every
> inconsistency
> is in reference to these 2 object IDs existing in sys.columns, but not
> sys.objects. In SQL Server 2000 I would just remove these records from
> syscolumns and I believe I'd be good. Since this is causing the full dbcc
> checkdb to fail, I'd like to see what options are available for removing
> these records. Any help would be greatly appreciated.
> Also, while a full dbcc checkdb fails due to this catalog inconsistency, a
> dbcc checkdb with physical_only comes back clean. I assume this is due to
> a
> physical_only not performing a catalog check. Would this inconsistency
> cause
> a database restore to fail?
> Thanks,
> Greg|||Thanks for the response. I have tried setting 'allow updates' to 1 and
deleting these rows from sys.columns, but it errors out with "Ad hoc updates
to system catalogs are not allowed. I'd prefer not to try any of the checkdb
repair options as I'm not sure whether this removes just the specific rows
that I'd like to delete or whole pages with other data that I don't want to
remove.
If it's not possible to delete these rows, it looks like the only option to
correct the inconsistency within these system tables is to export all of the
user objects/data into a new database. I can understand the reasoning behind
Microsoft taking away the ability to mess around with system tables. But,
considering that this database is just over 900GB, exporting seems to be an
exceedingly painful solution compared to just deleting all rows from a table
where object_id = object_id. I guess the other option is to just live with
the dbcc checkdb errors every week and keep track of the output to make sure
that there aren't any additional errors. Hopefully there's another way.
Thanks,
Greg
"TheSQLGuru" wrote:
> Check BOL for the following options for DBCC CHECKDB:
> REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
> Also, have you actually TRIED to delete the offending rows yourself?
> Although MS says not supported . . . you never know! Attempt on a TEST
> server first! :-)
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "gj111" <gj111@.discussions.microsoft.com> wrote in message
> news:D7531D91-C796-45F4-B81D-1C3836B2CB64@.microsoft.com...
> > Hello,
> >
> > My understanding is that modifying data in system tables in SQL Server
> > 2005
> > isn't allowed. Currently, I have an issue where a full dbcc checkdb is
> > failing due to records existing in sys.columns, but not in sys.objects.
> >
> > I believe these orphaned rows are a result of a delete statement being ran
> > against the SQL Server 2000 sysobjects table prior to a SQL Server 2005
> > upgrade. This delete removed all objects from sysobjects which were owned
> > by
> > a specific user id as the user was to be removed from the system. This
> > delete apparently left records behind for 2 of these user tables in
> > syscolumns.
> >
> > I have gone through the results of the dbcc checkdb and every
> > inconsistency
> > is in reference to these 2 object IDs existing in sys.columns, but not
> > sys.objects. In SQL Server 2000 I would just remove these records from
> > syscolumns and I believe I'd be good. Since this is causing the full dbcc
> > checkdb to fail, I'd like to see what options are available for removing
> > these records. Any help would be greatly appreciated.
> >
> > Also, while a full dbcc checkdb fails due to this catalog inconsistency, a
> > dbcc checkdb with physical_only comes back clean. I assume this is due to
> > a
> > physical_only not performing a catalog check. Would this inconsistency
> > cause
> > a database restore to fail?
> >
> > Thanks,
> > Greg
>
>|||Allow updates is indeed a dummy config in 2005. It is only there for the command not to error, it
will not allow updates of the system tables. I would suggest using a backup on which you test DBCC
with repair option. I'm not sure whether DBCC repair will attempt repair system table
inconsistencies. I understand it can be a hassle considering the database size. You could open a
case with MS Support, but to be honest, I wouldn't expect them to have much more to suggest.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"gj111" <gj111@.discussions.microsoft.com> wrote in message
news:FBA5FECF-4B16-43B6-8021-7063EB6329F1@.microsoft.com...
> Thanks for the response. I have tried setting 'allow updates' to 1 and
> deleting these rows from sys.columns, but it errors out with "Ad hoc updates
> to system catalogs are not allowed. I'd prefer not to try any of the checkdb
> repair options as I'm not sure whether this removes just the specific rows
> that I'd like to delete or whole pages with other data that I don't want to
> remove.
> If it's not possible to delete these rows, it looks like the only option to
> correct the inconsistency within these system tables is to export all of the
> user objects/data into a new database. I can understand the reasoning behind
> Microsoft taking away the ability to mess around with system tables. But,
> considering that this database is just over 900GB, exporting seems to be an
> exceedingly painful solution compared to just deleting all rows from a table
> where object_id = object_id. I guess the other option is to just live with
> the dbcc checkdb errors every week and keep track of the output to make sure
> that there aren't any additional errors. Hopefully there's another way.
> Thanks,
> Greg
> "TheSQLGuru" wrote:
>> Check BOL for the following options for DBCC CHECKDB:
>> REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
>> Also, have you actually TRIED to delete the offending rows yourself?
>> Although MS says not supported . . . you never know! Attempt on a TEST
>> server first! :-)
>> --
>> TheSQLGuru
>> President
>> Indicium Resources, Inc.
>> "gj111" <gj111@.discussions.microsoft.com> wrote in message
>> news:D7531D91-C796-45F4-B81D-1C3836B2CB64@.microsoft.com...
>> > Hello,
>> >
>> > My understanding is that modifying data in system tables in SQL Server
>> > 2005
>> > isn't allowed. Currently, I have an issue where a full dbcc checkdb is
>> > failing due to records existing in sys.columns, but not in sys.objects.
>> >
>> > I believe these orphaned rows are a result of a delete statement being ran
>> > against the SQL Server 2000 sysobjects table prior to a SQL Server 2005
>> > upgrade. This delete removed all objects from sysobjects which were owned
>> > by
>> > a specific user id as the user was to be removed from the system. This
>> > delete apparently left records behind for 2 of these user tables in
>> > syscolumns.
>> >
>> > I have gone through the results of the dbcc checkdb and every
>> > inconsistency
>> > is in reference to these 2 object IDs existing in sys.columns, but not
>> > sys.objects. In SQL Server 2000 I would just remove these records from
>> > syscolumns and I believe I'd be good. Since this is causing the full dbcc
>> > checkdb to fail, I'd like to see what options are available for removing
>> > these records. Any help would be greatly appreciated.
>> >
>> > Also, while a full dbcc checkdb fails due to this catalog inconsistency, a
>> > dbcc checkdb with physical_only comes back clean. I assume this is due to
>> > a
>> > physical_only not performing a catalog check. Would this inconsistency
>> > cause
>> > a database restore to fail?
>> >
>> > Thanks,
>> > Greg
>>|||On 23 Mar, 23:54, gj111 <g...@.discussions.microsoft.com> wrote:
> If it's not possible to delete these rows, it looks like the only option to
> correct the inconsistency within these system tables is to export all of the
> user objects/data into a new database. I can understand the reasoning behind
> Microsoft taking away the ability to mess around with system tables. But,
> considering that this database is just over 900GB, exporting seems to be an
> exceedingly painful solution compared to just deleting all rows from a table
> where object_id = object_id. I guess the other option is to just live with
> the dbcc checkdb errors every week and keep track of the output to make sure
> that there aren't any additional errors. Hopefully there's another way.
>
Exporting the data would indeed be a painful solution but maybe it is
the only way, I really don't know. Obviously this demonstrates why it
was an incredibly bad idea to do what you did in the first place. Why
would you ever resort to deleting data directly from sysobjects? Doing
that with critical data (especially with 900GB of critical data!)
seems like a pretty irresponsible action on the part of someone.
I mention this in case you are still looking for the right solution
for whatever it is you were trying to achieve - as well as to satisfy
my own surprise and curiosity.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks for the feedback Tibor. I think you're probably right.
"Tibor Karaszi" wrote:
> Allow updates is indeed a dummy config in 2005. It is only there for the command not to error, it
> will not allow updates of the system tables. I would suggest using a backup on which you test DBCC
> with repair option. I'm not sure whether DBCC repair will attempt repair system table
> inconsistencies. I understand it can be a hassle considering the database size. You could open a
> case with MS Support, but to be honest, I wouldn't expect them to have much more to suggest.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "gj111" <gj111@.discussions.microsoft.com> wrote in message
> news:FBA5FECF-4B16-43B6-8021-7063EB6329F1@.microsoft.com...
> > Thanks for the response. I have tried setting 'allow updates' to 1 and
> > deleting these rows from sys.columns, but it errors out with "Ad hoc updates
> > to system catalogs are not allowed. I'd prefer not to try any of the checkdb
> > repair options as I'm not sure whether this removes just the specific rows
> > that I'd like to delete or whole pages with other data that I don't want to
> > remove.
> >
> > If it's not possible to delete these rows, it looks like the only option to
> > correct the inconsistency within these system tables is to export all of the
> > user objects/data into a new database. I can understand the reasoning behind
> > Microsoft taking away the ability to mess around with system tables. But,
> > considering that this database is just over 900GB, exporting seems to be an
> > exceedingly painful solution compared to just deleting all rows from a table
> > where object_id = object_id. I guess the other option is to just live with
> > the dbcc checkdb errors every week and keep track of the output to make sure
> > that there aren't any additional errors. Hopefully there's another way.
> >
> > Thanks,
> > Greg
> >
> > "TheSQLGuru" wrote:
> >
> >> Check BOL for the following options for DBCC CHECKDB:
> >> REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
> >>
> >> Also, have you actually TRIED to delete the offending rows yourself?
> >> Although MS says not supported . . . you never know! Attempt on a TEST
> >> server first! :-)
> >>
> >> --
> >> TheSQLGuru
> >> President
> >> Indicium Resources, Inc.
> >>
> >> "gj111" <gj111@.discussions.microsoft.com> wrote in message
> >> news:D7531D91-C796-45F4-B81D-1C3836B2CB64@.microsoft.com...
> >> > Hello,
> >> >
> >> > My understanding is that modifying data in system tables in SQL Server
> >> > 2005
> >> > isn't allowed. Currently, I have an issue where a full dbcc checkdb is
> >> > failing due to records existing in sys.columns, but not in sys.objects.
> >> >
> >> > I believe these orphaned rows are a result of a delete statement being ran
> >> > against the SQL Server 2000 sysobjects table prior to a SQL Server 2005
> >> > upgrade. This delete removed all objects from sysobjects which were owned
> >> > by
> >> > a specific user id as the user was to be removed from the system. This
> >> > delete apparently left records behind for 2 of these user tables in
> >> > syscolumns.
> >> >
> >> > I have gone through the results of the dbcc checkdb and every
> >> > inconsistency
> >> > is in reference to these 2 object IDs existing in sys.columns, but not
> >> > sys.objects. In SQL Server 2000 I would just remove these records from
> >> > syscolumns and I believe I'd be good. Since this is causing the full dbcc
> >> > checkdb to fail, I'd like to see what options are available for removing
> >> > these records. Any help would be greatly appreciated.
> >> >
> >> > Also, while a full dbcc checkdb fails due to this catalog inconsistency, a
> >> > dbcc checkdb with physical_only comes back clean. I assume this is due to
> >> > a
> >> > physical_only not performing a catalog check. Would this inconsistency
> >> > cause
> >> > a database restore to fail?
> >> >
> >> > Thanks,
> >> > Greg
> >>
> >>
> >>
>|||Well, thanks for the comments David, although not very helpful. I wasn't
actually the person who originally deleted these records, but I am left to
figure this out after the checkdb came back with errors.
From what I understand, this was done to try and drop 20,000+ objects that
were owned by a particular user id as this user was to be dropped.
"David Portas" wrote:
> On 23 Mar, 23:54, gj111 <g...@.discussions.microsoft.com> wrote:
> >
> > If it's not possible to delete these rows, it looks like the only option to
> > correct the inconsistency within these system tables is to export all of the
> > user objects/data into a new database. I can understand the reasoning behind
> > Microsoft taking away the ability to mess around with system tables. But,
> > considering that this database is just over 900GB, exporting seems to be an
> > exceedingly painful solution compared to just deleting all rows from a table
> > where object_id = object_id. I guess the other option is to just live with
> > the dbcc checkdb errors every week and keep track of the output to make sure
> > that there aren't any additional errors. Hopefully there's another way.
> >
> Exporting the data would indeed be a painful solution but maybe it is
> the only way, I really don't know. Obviously this demonstrates why it
> was an incredibly bad idea to do what you did in the first place. Why
> would you ever resort to deleting data directly from sysobjects? Doing
> that with critical data (especially with 900GB of critical data!)
> seems like a pretty irresponsible action on the part of someone.
> I mention this in case you are still looking for the right solution
> for whatever it is you were trying to achieve - as well as to satisfy
> my own surprise and curiosity.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||On 24 Mar, 14:38, gj111 <g...@.discussions.microsoft.com> wrote:
> From what I understand, this was done to try and drop 20,000+ objects that
> were owned by a particular user id as this user was to be dropped.
>
Then it was perhaps due to ignorance and inexperience rather than pure
recklessness. Surely the quick, easy and safe way would have been to
script some DROP statements from the catalogue to do the same job. Or
to use sp_changeobjectowner.
As you are the person who has to do the mopping up, you might wonder
what other horrors you are going to discover. Good luck!
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||> From what I understand, this was done to try and drop 20,000+ objects that
> were owned by a particular user id as this user was to be dropped.
You might already be familiar with this technique, but for the benefit of other readers:
These type of operations are pretty straight forward to automate by generating the SQL statements
and copy the results into a query window and execute it:
SELECT 'DROP TABLE dbo.[' + name + ']'
FROM sysobjects
WHERE uid = USER_ID('dbo')
AND type = 'U'
Above is for 2000. Similar can be done for 2005 using schema and catalog views.
Or, one can use a cursor and built the DROP command in a variable which you then execute.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"gj111" <gj111@.discussions.microsoft.com> wrote in message
news:94385EB4-D924-44C3-B7DC-36B609294E51@.microsoft.com...
> Well, thanks for the comments David, although not very helpful. I wasn't
> actually the person who originally deleted these records, but I am left to
> figure this out after the checkdb came back with errors.
> From what I understand, this was done to try and drop 20,000+ objects that
> were owned by a particular user id as this user was to be dropped.
> "David Portas" wrote:
>> On 23 Mar, 23:54, gj111 <g...@.discussions.microsoft.com> wrote:
>> >
>> > If it's not possible to delete these rows, it looks like the only option to
>> > correct the inconsistency within these system tables is to export all of the
>> > user objects/data into a new database. I can understand the reasoning behind
>> > Microsoft taking away the ability to mess around with system tables. But,
>> > considering that this database is just over 900GB, exporting seems to be an
>> > exceedingly painful solution compared to just deleting all rows from a table
>> > where object_id = object_id. I guess the other option is to just live with
>> > the dbcc checkdb errors every week and keep track of the output to make sure
>> > that there aren't any additional errors. Hopefully there's another way.
>> >
>> Exporting the data would indeed be a painful solution but maybe it is
>> the only way, I really don't know. Obviously this demonstrates why it
>> was an incredibly bad idea to do what you did in the first place. Why
>> would you ever resort to deleting data directly from sysobjects? Doing
>> that with critical data (especially with 900GB of critical data!)
>> seems like a pretty irresponsible action on the part of someone.
>> I mention this in case you are still looking for the right solution
>> for whatever it is you were trying to achieve - as well as to satisfy
>> my own surprise and curiosity.
>> --
>> David Portas, SQL Server MVP
>> Whenever possible please post enough code to reproduce your problem.
>> Including CREATE TABLE and INSERT statements usually helps.
>> State what version of SQL Server you are using and specify the content
>> of any error messages.
>> SQL Server Books Online:
>> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
>> --
>>|||I would recommend taking the original suggestion of contacting Microsoft
support. They do have ways of modifying the system tables but unless you
really know what you're doing, modifying deleting these rows may make the
problem worse. If nothing else, there are a lot of disk pages allocated to
those 20,000 tables that are no longer addressable and will need to be
cleaned up.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"gj111" <gj111@.discussions.microsoft.com> wrote in message
news:94385EB4-D924-44C3-B7DC-36B609294E51@.microsoft.com...
> Well, thanks for the comments David, although not very helpful. I wasn't
> actually the person who originally deleted these records, but I am left to
> figure this out after the checkdb came back with errors.
> From what I understand, this was done to try and drop 20,000+ objects that
> were owned by a particular user id as this user was to be dropped.
> "David Portas" wrote:
>> On 23 Mar, 23:54, gj111 <g...@.discussions.microsoft.com> wrote:
>> >
>> > If it's not possible to delete these rows, it looks like the only
>> > option to
>> > correct the inconsistency within these system tables is to export all
>> > of the
>> > user objects/data into a new database. I can understand the reasoning
>> > behind
>> > Microsoft taking away the ability to mess around with system tables.
>> > But,
>> > considering that this database is just over 900GB, exporting seems to
>> > be an
>> > exceedingly painful solution compared to just deleting all rows from a
>> > table
>> > where object_id = object_id. I guess the other option is to just live
>> > with
>> > the dbcc checkdb errors every week and keep track of the output to make
>> > sure
>> > that there aren't any additional errors. Hopefully there's another
>> > way.
>> >
>> Exporting the data would indeed be a painful solution but maybe it is
>> the only way, I really don't know. Obviously this demonstrates why it
>> was an incredibly bad idea to do what you did in the first place. Why
>> would you ever resort to deleting data directly from sysobjects? Doing
>> that with critical data (especially with 900GB of critical data!)
>> seems like a pretty irresponsible action on the part of someone.
>> I mention this in case you are still looking for the right solution
>> for whatever it is you were trying to achieve - as well as to satisfy
>> my own surprise and curiosity.
>> --
>> David Portas, SQL Server MVP
>> Whenever possible please post enough code to reproduce your problem.
>> Including CREATE TABLE and INSERT statements usually helps.
>> State what version of SQL Server you are using and specify the content
>> of any error messages.
>> SQL Server Books Online:
>> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
>> --
>>|||I discussed this with Greg offline and the solution I offered (which is
unfortunately undocumented) seems to have done the trick without any
size-of-data operations. It only worked because the corruption is totally
benign in this case - so its not something that I can publicize (although
you're welcome to email me through the blog link below)
Best way to cope with this once its happened is to call Product Support and
have them help you (or email me if you don't need an instantaneous
response). Best way to avoid it is to educate your DBAs not to mess with the
system tables...
Thanks
--
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"gj111" <gj111@.discussions.microsoft.com> wrote in message
news:94385EB4-D924-44C3-B7DC-36B609294E51@.microsoft.com...
> Well, thanks for the comments David, although not very helpful. I wasn't
> actually the person who originally deleted these records, but I am left to
> figure this out after the checkdb came back with errors.
> From what I understand, this was done to try and drop 20,000+ objects that
> were owned by a particular user id as this user was to be dropped.
> "David Portas" wrote:
>> On 23 Mar, 23:54, gj111 <g...@.discussions.microsoft.com> wrote:
>> >
>> > If it's not possible to delete these rows, it looks like the only
>> > option to
>> > correct the inconsistency within these system tables is to export all
>> > of the
>> > user objects/data into a new database. I can understand the reasoning
>> > behind
>> > Microsoft taking away the ability to mess around with system tables.
>> > But,
>> > considering that this database is just over 900GB, exporting seems to
>> > be an
>> > exceedingly painful solution compared to just deleting all rows from a
>> > table
>> > where object_id = object_id. I guess the other option is to just live
>> > with
>> > the dbcc checkdb errors every week and keep track of the output to make
>> > sure
>> > that there aren't any additional errors. Hopefully there's another
>> > way.
>> >
>> Exporting the data would indeed be a painful solution but maybe it is
>> the only way, I really don't know. Obviously this demonstrates why it
>> was an incredibly bad idea to do what you did in the first place. Why
>> would you ever resort to deleting data directly from sysobjects? Doing
>> that with critical data (especially with 900GB of critical data!)
>> seems like a pretty irresponsible action on the part of someone.
>> I mention this in case you are still looking for the right solution
>> for whatever it is you were trying to achieve - as well as to satisfy
>> my own surprise and curiosity.
>> --
>> David Portas, SQL Server MVP
>> Whenever possible please post enough code to reproduce your problem.
>> Including CREATE TABLE and INSERT statements usually helps.
>> State what version of SQL Server you are using and specify the content
>> of any error messages.
>> SQL Server Books Online:
>> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
>> --
>>|||Thanks Roger. I'll definitely get with support. I'm also in Orlando this
week, maybe I'll get lucky and get a chance to pick someone's brain.
Thanks,
Greg
"Roger Wolter[MSFT]" wrote:
> I would recommend taking the original suggestion of contacting Microsoft
> support. They do have ways of modifying the system tables but unless you
> really know what you're doing, modifying deleting these rows may make the
> problem worse. If nothing else, there are a lot of disk pages allocated to
> those 20,000 tables that are no longer addressable and will need to be
> cleaned up.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "gj111" <gj111@.discussions.microsoft.com> wrote in message
> news:94385EB4-D924-44C3-B7DC-36B609294E51@.microsoft.com...
> > Well, thanks for the comments David, although not very helpful. I wasn't
> > actually the person who originally deleted these records, but I am left to
> > figure this out after the checkdb came back with errors.
> >
> > From what I understand, this was done to try and drop 20,000+ objects that
> > were owned by a particular user id as this user was to be dropped.
> >
> > "David Portas" wrote:
> >
> >> On 23 Mar, 23:54, gj111 <g...@.discussions.microsoft.com> wrote:
> >> >
> >> > If it's not possible to delete these rows, it looks like the only
> >> > option to
> >> > correct the inconsistency within these system tables is to export all
> >> > of the
> >> > user objects/data into a new database. I can understand the reasoning
> >> > behind
> >> > Microsoft taking away the ability to mess around with system tables.
> >> > But,
> >> > considering that this database is just over 900GB, exporting seems to
> >> > be an
> >> > exceedingly painful solution compared to just deleting all rows from a
> >> > table
> >> > where object_id = object_id. I guess the other option is to just live
> >> > with
> >> > the dbcc checkdb errors every week and keep track of the output to make
> >> > sure
> >> > that there aren't any additional errors. Hopefully there's another
> >> > way.
> >> >
> >>
> >> Exporting the data would indeed be a painful solution but maybe it is
> >> the only way, I really don't know. Obviously this demonstrates why it
> >> was an incredibly bad idea to do what you did in the first place. Why
> >> would you ever resort to deleting data directly from sysobjects? Doing
> >> that with critical data (especially with 900GB of critical data!)
> >> seems like a pretty irresponsible action on the part of someone.
> >>
> >> I mention this in case you are still looking for the right solution
> >> for whatever it is you were trying to achieve - as well as to satisfy
> >> my own surprise and curiosity.
> >>
> >> --
> >> David Portas, SQL Server MVP
> >>
> >> Whenever possible please post enough code to reproduce your problem.
> >> Including CREATE TABLE and INSERT statements usually helps.
> >> State what version of SQL Server you are using and specify the content
> >> of any error messages.
> >>
> >> SQL Server Books Online:
> >> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> >> --
> >>
> >>
>sql

Delete data from sys.columns in SQL Server 2005

Hello,
My understanding is that modifying data in system tables in SQL Server 2005
isn't allowed. Currently, I have an issue where a full dbcc checkdb is
failing due to records existing in sys.columns, but not in sys.objects.
I believe these orphaned rows are a result of a delete statement being ran
against the SQL Server 2000 sysobjects table prior to a SQL Server 2005
upgrade. This delete removed all objects from sysobjects which were owned b
y
a specific user id as the user was to be removed from the system. This
delete apparently left records behind for 2 of these user tables in
syscolumns.
I have gone through the results of the dbcc checkdb and every inconsistency
is in reference to these 2 object IDs existing in sys.columns, but not
sys.objects. In SQL Server 2000 I would just remove these records from
syscolumns and I believe I'd be good. Since this is causing the full dbcc
checkdb to fail, I'd like to see what options are available for removing
these records. Any help would be greatly appreciated.
Also, while a full dbcc checkdb fails due to this catalog inconsistency, a
dbcc checkdb with physical_only comes back clean. I assume this is due to a
physical_only not performing a catalog check. Would this inconsistency caus
e
a database restore to fail?
Thanks,
GregCheck BOL for the following options for DBCC CHECKDB:
REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
Also, have you actually TRIED to delete the offending rows yourself?
Although MS says not supported . . . you never know! Attempt on a TEST
server first! :-)
TheSQLGuru
President
Indicium Resources, Inc.
"gj111" <gj111@.discussions.microsoft.com> wrote in message
news:D7531D91-C796-45F4-B81D-1C3836B2CB64@.microsoft.com...
> Hello,
> My understanding is that modifying data in system tables in SQL Server
> 2005
> isn't allowed. Currently, I have an issue where a full dbcc checkdb is
> failing due to records existing in sys.columns, but not in sys.objects.
> I believe these orphaned rows are a result of a delete statement being ran
> against the SQL Server 2000 sysobjects table prior to a SQL Server 2005
> upgrade. This delete removed all objects from sysobjects which were owned
> by
> a specific user id as the user was to be removed from the system. This
> delete apparently left records behind for 2 of these user tables in
> syscolumns.
> I have gone through the results of the dbcc checkdb and every
> inconsistency
> is in reference to these 2 object IDs existing in sys.columns, but not
> sys.objects. In SQL Server 2000 I would just remove these records from
> syscolumns and I believe I'd be good. Since this is causing the full dbcc
> checkdb to fail, I'd like to see what options are available for removing
> these records. Any help would be greatly appreciated.
> Also, while a full dbcc checkdb fails due to this catalog inconsistency, a
> dbcc checkdb with physical_only comes back clean. I assume this is due to
> a
> physical_only not performing a catalog check. Would this inconsistency
> cause
> a database restore to fail?
> Thanks,
> Greg|||Thanks for the response. I have tried setting 'allow updates' to 1 and
deleting these rows from sys.columns, but it errors out with "Ad hoc updates
to system catalogs are not allowed. I'd prefer not to try any of the checkd
b
repair options as I'm not sure whether this removes just the specific rows
that I'd like to delete or whole pages with other data that I don't want to
remove.
If it's not possible to delete these rows, it looks like the only option to
correct the inconsistency within these system tables is to export all of the
user objects/data into a new database. I can understand the reasoning behin
d
Microsoft taking away the ability to mess around with system tables. But,
considering that this database is just over 900GB, exporting seems to be an
exceedingly painful solution compared to just deleting all rows from a table
where object_id = object_id. I guess the other option is to just live with
the dbcc checkdb errors every week and keep track of the output to make sure
that there aren't any additional errors. Hopefully there's another way.
Thanks,
Greg
"TheSQLGuru" wrote:

> Check BOL for the following options for DBCC CHECKDB:
> REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
> Also, have you actually TRIED to delete the offending rows yourself?
> Although MS says not supported . . . you never know! Attempt on a TEST
> server first! :-)
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "gj111" <gj111@.discussions.microsoft.com> wrote in message
> news:D7531D91-C796-45F4-B81D-1C3836B2CB64@.microsoft.com...
>
>|||Allow updates is indeed a dummy config in 2005. It is only there for the com
mand not to error, it
will not allow updates of the system tables. I would suggest using a backup
on which you test DBCC
with repair option. I'm not sure whether DBCC repair will attempt repair sys
tem table
inconsistencies. I understand it can be a hassle considering the database si
ze. You could open a
case with MS Support, but to be honest, I wouldn't expect them to have much
more to suggest.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"gj111" <gj111@.discussions.microsoft.com> wrote in message
news:FBA5FECF-4B16-43B6-8021-7063EB6329F1@.microsoft.com...[vbcol=seagreen]
> Thanks for the response. I have tried setting 'allow updates' to 1 and
> deleting these rows from sys.columns, but it errors out with "Ad hoc updat
es
> to system catalogs are not allowed. I'd prefer not to try any of the chec
kdb
> repair options as I'm not sure whether this removes just the specific rows
> that I'd like to delete or whole pages with other data that I don't want t
o
> remove.
> If it's not possible to delete these rows, it looks like the only option t
o
> correct the inconsistency within these system tables is to export all of t
he
> user objects/data into a new database. I can understand the reasoning beh
ind
> Microsoft taking away the ability to mess around with system tables. But,
> considering that this database is just over 900GB, exporting seems to be a
n
> exceedingly painful solution compared to just deleting all rows from a tab
le
> where object_id = object_id. I guess the other option is to just live wit
h
> the dbcc checkdb errors every week and keep track of the output to make su
re
> that there aren't any additional errors. Hopefully there's another way.
> Thanks,
> Greg
> "TheSQLGuru" wrote:
>|||On 23 Mar, 23:54, gj111 <g...@.discussions.microsoft.com> wrote:
> If it's not possible to delete these rows, it looks like the only option t
o
> correct the inconsistency within these system tables is to export all of t
he
> user objects/data into a new database. I can understand the reasoning beh
ind
> Microsoft taking away the ability to mess around with system tables. But,
> considering that this database is just over 900GB, exporting seems to be a
n
> exceedingly painful solution compared to just deleting all rows from a tab
le
> where object_id = object_id. I guess the other option is to just live wit
h
> the dbcc checkdb errors every week and keep track of the output to make su
re
> that there aren't any additional errors. Hopefully there's another way.
>
Exporting the data would indeed be a painful solution but maybe it is
the only way, I really don't know. Obviously this demonstrates why it
was an incredibly bad idea to do what you did in the first place. Why
would you ever resort to deleting data directly from sysobjects? Doing
that with critical data (especially with 900GB of critical data!)
seems like a pretty irresponsible action on the part of someone.
I mention this in case you are still looking for the right solution
for whatever it is you were trying to achieve - as well as to satisfy
my own surprise and curiosity.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks for the feedback Tibor. I think you're probably right.
"Tibor Karaszi" wrote:

> Allow updates is indeed a dummy config in 2005. It is only there for the c
ommand not to error, it
> will not allow updates of the system tables. I would suggest using a backu
p on which you test DBCC
> with repair option. I'm not sure whether DBCC repair will attempt repair s
ystem table
> inconsistencies. I understand it can be a hassle considering the database
size. You could open a
> case with MS Support, but to be honest, I wouldn't expect them to have muc
h more to suggest.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "gj111" <gj111@.discussions.microsoft.com> wrote in message
> news:FBA5FECF-4B16-43B6-8021-7063EB6329F1@.microsoft.com...
>|||Well, thanks for the comments David, although not very helpful. I wasn't
actually the person who originally deleted these records, but I am left to
figure this out after the checkdb came back with errors.
From what I understand, this was done to try and drop 20,000+ objects that
were owned by a particular user id as this user was to be dropped.
"David Portas" wrote:

> On 23 Mar, 23:54, gj111 <g...@.discussions.microsoft.com> wrote:
> Exporting the data would indeed be a painful solution but maybe it is
> the only way, I really don't know. Obviously this demonstrates why it
> was an incredibly bad idea to do what you did in the first place. Why
> would you ever resort to deleting data directly from sysobjects? Doing
> that with critical data (especially with 900GB of critical data!)
> seems like a pretty irresponsible action on the part of someone.
> I mention this in case you are still looking for the right solution
> for whatever it is you were trying to achieve - as well as to satisfy
> my own surprise and curiosity.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||> From what I understand, this was done to try and drop 20,000+ objects that
> were owned by a particular user id as this user was to be dropped.
You might already be familiar with this technique, but for the benefit of ot
her readers:
These type of operations are pretty straight forward to automate by generati
ng the SQL statements
and copy the results into a query window and execute it:
SELECT 'DROP TABLE dbo.[' + name + ']'
FROM sysobjects
WHERE uid = USER_ID('dbo')
AND type = 'U'
Above is for 2000. Similar can be done for 2005 using schema and catalog vie
ws.
Or, one can use a cursor and built the DROP command in a variable which you
then execute.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"gj111" <gj111@.discussions.microsoft.com> wrote in message
news:94385EB4-D924-44C3-B7DC-36B609294E51@.microsoft.com...[vbcol=seagreen]
> Well, thanks for the comments David, although not very helpful. I wasn't
> actually the person who originally deleted these records, but I am left to
> figure this out after the checkdb came back with errors.
> From what I understand, this was done to try and drop 20,000+ objects that
> were owned by a particular user id as this user was to be dropped.
> "David Portas" wrote:
>|||On 24 Mar, 14:38, gj111 <g...@.discussions.microsoft.com> wrote:
> From what I understand, this was done to try and drop 20,000+ objects that
> were owned by a particular user id as this user was to be dropped.
>
Then it was perhaps due to ignorance and inexperience rather than pure
recklessness. Surely the quick, easy and safe way would have been to
script some DROP statements from the catalogue to do the same job. Or
to use sp_changeobjectowner.
As you are the person who has to do the mopping up, you might wonder
what other horrors you are going to discover. Good luck!
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I would recommend taking the original suggestion of contacting Microsoft
support. They do have ways of modifying the system tables but unless you
really know what you're doing, modifying deleting these rows may make the
problem worse. If nothing else, there are a lot of disk pages allocated to
those 20,000 tables that are no longer addressable and will need to be
cleaned up.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"gj111" <gj111@.discussions.microsoft.com> wrote in message
news:94385EB4-D924-44C3-B7DC-36B609294E51@.microsoft.com...[vbcol=seagreen]
> Well, thanks for the comments David, although not very helpful. I wasn't
> actually the person who originally deleted these records, but I am left to
> figure this out after the checkdb came back with errors.
> From what I understand, this was done to try and drop 20,000+ objects that
> were owned by a particular user id as this user was to be dropped.
> "David Portas" wrote:
>

delete data from all tables.

Is there an easy way to delete all data from all nonsystem tables in a given sql server 2000 database?

Right now i have about 50 tables that i want to clear data out of and want to know if there is an easier way than

Delete from <mytable>

For each table i want to clear data out of.Simply script the database then delete the database and create the database with the script.|||is this script doable say in 3 to 4 lines. if so can you give me an example of how a delete all tables script might look like. Otherwise i think i'll just purge it the slow way for now.|||If you work with links from PK to FK, set the option 'Cascade DELETE' on each of them, then delete only parent tables... can still be boring!

But as suggested before, the best solution is to generate a script from your DB using DTS wizard, checking without data, and regenerate your DB by executing this script. It should take 5 minutes to do so.|||Use [Your Database]
Select 'delete from ' + name from Sysobjects
where Type='u' order by name

This will generate the statement:
Delete from A
Delete from B
Delete from C
Delete from D

Copy the Output to the Query Analyzer and press F5.It will start deleting the data from the user table in one go.

Also You might need to change the order of the delete for the Child and the Parent table(if the Relationship exists).Make sure that the Delete from Child is the statement before Delete from the Parent.

You can also replace the 'Delete from ' statement with the 'Truncate Table ' statement as it will be faster.

Hope it Helps.|||As per sqlserver2k's post, we use the same sort of thing:

select 'delete from ' + o.name + ';'
from sysobjects o,
sysusers u
where o.type = 'U'
and o.uid = u.uid
and upper(u.name) = 'xxx';

where 'xxx' is the owner of the object you're wanting to delete from.

Copy and paste the output back into the execution pane of whatever sql tool you're using and execute (f5).
You may need to run the execute several times if there are constraints, unless you've got the time to order the output so that it deletes in the right sequence for them (constraints, that is).

Also, take off the semi colon if you're using query analyzer :)|||TAKE CARE!

The method shown before (Select 'delete from ' + name from Sysobjects where Type='u' order by name) returns also dtproperties table, wich you might not want to delete. If so, add 'status>0' where clause, status coming from Sysobjects table.

You'll get:

Select 'delete from ' + name from Sysobjects where Type='u' and status>0 order by name|||tnx sqlserver2k and megan. That's the answer that i was seeking.|||No worries :)

BTW, Climber is right about dtproperties table. If the tables you want to delete from are owned by dbo, be a little careful and double-check the output from the select 'delete from' + query so that you're only executing delete statements on those tables you want to delete from.|||/*
If you work with links from PK to FK, set the option 'Cascade DELETE' on each of them, then delete only parent tables... can still be boring!
*/

declare @.sqlstring char(200)

DECLARE Tables_Cursor CURSOR FOR

SELECT 'truncate table '+ rtrim(name) FROM sysobjects where xtype='U' order by name

OPEN Tables_Cursor

FETCH NEXT FROM Tables_cursor INTO @.sqlstring

WHILE @.@.FETCH_STATUS = 0

BEGIN

exec(@.sqlstring)

FETCH NEXT FROM Tables_cursor INTO @.sqlstring

END

CLOSE tables_cursor

DEALLOCATE tables_cursor|||TAKE CARE|||Select 'truncate table ' + name,* from Sysobjects
where Type='u' order by name

This is much faster than deleting data
Prakash

delete data

Hi,

I have two tables.

CREATE TABLE [one] (
[roleno] [int] NOT NULL ,
[schno] [int] NULL ,
CONSTRAINT [PK_one] PRIMARY KEY CLUSTERED
(
[roleno]
) ON [PRIMARY] ,
CONSTRAINT [FK_one_two] FOREIGN KEY
(
[schno]
) REFERENCES [two] (
[schno]
)
) ON [PRIMARY]
GO

CREATE TABLE [two] (
[roleno] [int] NULL ,
[schno] [int] NOT NULL ,
CONSTRAINT [PK_two] PRIMARY KEY CLUSTERED
(
[schno]
) ON [PRIMARY] ,
CONSTRAINT [FK_two_one] FOREIGN KEY
(
[roleno]
) REFERENCES [one] (
[roleno]
)
) ON [PRIMARY]
GO

(I fact i created Primary & Foreign keys after inserting data in both of these tables.)

I want to delete data from these two tables.
How do i do that...Any Ideas?Originally posted by naveen_mehta
Hi,

I have two tables.

CREATE TABLE [one] (
[roleno] [int] NOT NULL ,
[schno] [int] NULL ,
CONSTRAINT [PK_one] PRIMARY KEY CLUSTERED
(
[roleno]
) ON [PRIMARY] ,
CONSTRAINT [FK_one_two] FOREIGN KEY
(
[schno]
) REFERENCES [two] (
[schno]
)
) ON [PRIMARY]
GO

CREATE TABLE [two] (
[roleno] [int] NULL ,
[schno] [int] NOT NULL ,
CONSTRAINT [PK_two] PRIMARY KEY CLUSTERED
(
[schno]
) ON [PRIMARY] ,
CONSTRAINT [FK_two_one] FOREIGN KEY
(
[roleno]
) REFERENCES [one] (
[roleno]
)
) ON [PRIMARY]
GO

(I fact i created Primary & Foreign keys after inserting data in both of these tables.)

I want to delete data from these two tables.
How do i do that...Any Ideas?

ALTER TABLE ONE NOCHECK CONSTRAINT FK_one_two
ALTER TABLE TWO NOCHECK CONSTRAINT FK_two_one
DELETE ONE
DELETE TWO
ALTER TABLE ONE CHECK CONSTRAINT FK_one_two
ALTER TABLE TWO CHECK CONSTRAINT FK_two_one|||That really works...Thanks a ton...|||You could similarly use the alter statements while inserting data if you do not want to check for constraints

Delete cursor (must change it !)

Hi all,

The db that I took over is full of !@.##$@., unnormalized tables, cursors, you name it and it has it :(.

There is this cursor that opens a temp table, fetches the key and then deletes from the production table using that key for every row in the temp table.

I want to change it to something like

delete from A
where exists (select 1
from B
where B.ID1 = A.ID1 and
B.ID2 = A.ID2)

Now, I'm thinking that this query would secuentially scan A and compare the key to what B has and that is a waste of time. Is there a way to do it the other way around ? Scan the rows on table B and then delete them from table A ?

I haven't really played with sql in some time, maybe the answer is trivial but I can't see it right now.

Thanks in advance

Luis TorresWhen you submit the query, SQL Server will generate a plan. That plan will often automagically turn the query "inside out" as you've described if the optimizer determines that is more efficient.

-PatP|||My guess is that this will do quite well

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable00(ID1 int, ID2 int, Col3 char(1), PRIMARY KEY (ID1, ID2))
CREATE TABLE myTable99(ID1 int, ID2 int, Col3 char(1), PRIMARY KEY (ID1, ID2))
GO

INSERT INTO myTable00(ID1, ID2, Col3)
SELECT 1 , 1, 'a' UNION ALL
SELECT 33 , 1, 'a' UNION ALL
SELECT 555, 1, 'a' UNION ALL
SELECT 777, 1, 'a'

DECLARE @.x int
SELECT @.x = 1
WHILE @.x < 1000
BEGIN
INSERT INTO myTable99(ID1, ID2, Col3)
SELECT @.x, 1, 'a'
SELECT @.x = @.x + 1
END
GO

SET SHOWPLAN_TEXT ON
GO

SET NOCOUNT OFF
GO

DELETE FROM l
FROM myTable99 l
INNER JOIN myTable00 r
ON l.ID1 = r.ID1
AND l.ID2 = r.ID2
GO

SET SHOWPLAN_TEXT OFF
GO

DROP TABLE myTable00
DROP TABLE myTable99
GO

And produces

StmtText
--------------------------------------------------------
|--Clustered Index Delete(OBJECT:([Northwind].[dbo].[myTable99].[PK__myTable99__39A43435]))
|--Table Spool
|--Top(ROWCOUNT est 0)
|--Nested Loops(Inner Join, OUTER REFERENCES:([r].[ID2], [r].[ID1]))
|--Clustered Index Scan(OBJECT:([Northwind].[dbo].[myTable00].[PK__myTable00__37BBEBC3] AS [r]))
|--Clustered Index Seek(OBJECT:([Northwind].[dbo].[myTable99].[PK__myTable99__39A43435] AS [l]), SEEK:([l].[ID1]=[r].[ID1] AND [l].[ID2]=[r].[ID2]) ORDERED FORWARD)

(6 row(s) affected)|||Thank you Pat and Brett for your answer :), The inner join makes a lot of sense now that I see it :)

Luis Torres

delete contraint

HI all,
Hope I have the correct terminology.
I have related tables, with cascading deletes off. In otherwords, you cannot
delete a parent row if there are related child row.
Now when this happens a correct error is produced.
Is there any way to have a single return statement stipulation which child
table was involved in the cascading delete( or rarther the parent delete
failing)
Reasoning behind this is that because I have many child tables related with
a single parent, it would be a lot easier to know which table cause the
delete to fail, go to that table and determin if row in that particular
table can be deleted.
Currently I have to go to every related child table, and figure out if it
caused the delete to fail, and if ti did, then take the needed action.
I'm really looking for principals here, or for someone to steer me in the
right direction, perhaps some reading material etc.
Thanks
RobertAre you saying that you want to know which referencing table is it that proh
ibits the DELETE
operation? Check out the error message from the DELETE operation. If you hav
e several referencing
tables that has a conflict, you will only get on of them, though:
CREATE TABLE rd(c1 int primary key)
INSERT INTO rd (c1) VALUES(1)
GO
CREATE TABLE rs1(c1 int REFERENCES rd(c1))
CREATE TABLE rs2(c1 int REFERENCES rd(c1))
INSERT INTO rs2 (c1) VALUES(1)
CREATE TABLE rs3(c1 int REFERENCES rd(c1))
INSERT INTO rs3 (c1) VALUES(1)
GO
DELETE FROM rd WHERE c1 = 1
Server: Msg 547, Level 16, State 1, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK__rs2__c1__
4D0CD9BB". The conflict
occurred in database "tempdb", table "dbo.rs2", column 'c1'.
The statement has been terminated.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Robert Bravery" <me@.u.com> wrote in message news:egQxAm8MGHA.2828@.TK2MSFTNGP12.phx.gbl...[
color=darkred]
> HI all,
> Hope I have the correct terminology.
> I have related tables, with cascading deletes off. In otherwords, you cann
ot
> delete a parent row if there are related child row.
> Now when this happens a correct error is produced.
> Is there any way to have a single return statement stipulation which child
> table was involved in the cascading delete( or rarther the parent delete
> failing)
> Reasoning behind this is that because I have many child tables related wit
h
> a single parent, it would be a lot easier to know which table cause the
> delete to fail, go to that table and determin if row in that particular
> table can be deleted.
> Currently I have to go to every related child table, and figure out if it
> caused the delete to fail, and if ti did, then take the needed action.
> I'm really looking for principals here, or for someone to steer me in the
> right direction, perhaps some reading material etc.
> Thanks
> Robert
>[/color]

Delete Cascade on cyclic relationship

If I have 3 tables with cyclic relationship like this. Table A has a fk
referencing Table B, table C has a fk referencing table B and table A has a
fk referencing table C.
|--<--fk--|
| |
v |
A--fk-->B--fk-->C
Question is how do I handle "Delete Cascade". How do I write triggers to
handle this?
Thanks,
Tom DOn Sun, 23 Jan 2005 22:35:52 -0800, tom d wrote:

>If I have 3 tables with cyclic relationship like this. Table A has a fk
>referencing Table B, table C has a fk referencing table B and table A has a
>fk referencing table C.
> |--<--fk--|
> | |
> v |
> A--fk-->B--fk-->C
>
>Question is how do I handle "Delete Cascade". How do I write triggers to
>handle this?
>Thanks,
>Tom D
Hi Tom,
I just replieed to your question about the self-referencing table. The
trick for this cyclic relationship is essentially the same. If you are
sure that the nest level won't pass 32, just make a trigger for each of
the tables that will delete from the referencing table all rows
referencing a deleted row, make sure you enable nested and recursive
triggers and don't forget to start each trigger with
IF @.@.ROWCOUNT=0
RETURN
If you think the 32 level nesting limit might cause problems, use the
alternative approach outlined in my other message. This time, you'll have
to use three temp tables and run through a three-step cycle to add rows to
each of these temp tables in turn.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Delete any records in table 1 that are not in table 2 (tables joined on 2 columns)

I have two tables. The primary key for both tables is comprised of 2 columns.

How do I delete any records in table 1 that are not in table 2.

e.g.

if this was the data in the 2 tables

Table 1

col1 col2 col 3

0 0 0

0 1 0

Table 2

col1 col2 col3

0 0 0

I would like to delete the records containg the values 0, 1, 0 from table 1 because the is no record in table 2 with the values 0, 1 as the primary key.

Hi,

You can do this as follow (by suggesting that COL1 and COL2 are the key columns):

DELETE FROM table1
FROM table1
LEFT OUTER JOIN table2
ON table1.COL1 = table2.col1
AND table1.COL2 = table2.col2
WHERE table2.COL1 IS NULL

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||Thanks a million. It seems strange to me the way the From caluse is included twice but it works!|||

the following query will do..

Delete from table1 Where Not Exists(Select 1 From table2 Where table1.Col1=Table2.Col1 and Table1.Col2=Table2.Col2)

Delete and update

Hi All,

I have a Access front-end,the tables are from SQL server 2000 and
linked via ODBC using DSN.
I have a Main table and several related tables.The Main table has a
One -to- Many relationship with the related tables.
When I try to delete a record from the front-end,I get a error msg "
the table is locked,you and another user are attempting to delete/
update the same record".I am the single user and I am at a loss to
understand what the problem is.

I have to explain that I never had a problem when i was using Access
as the backend too.Is it because of linking?

Any suggestions?

RoyAre you are running the sql statement from Access?
Is the DSN a User DSN or System DSN?

--

Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=http://www.itjobfeed.com">UK IT Jobs</a>

"Roy" <praish1998@.yahoo.comwrote in message
news:1173148692.840874.72780@.c51g2000cwc.googlegro ups.com...

Quote:

Originally Posted by

Hi All,
>
I have a Access front-end,the tables are from SQL server 2000 and
linked via ODBC using DSN.
I have a Main table and several related tables.The Main table has a
One -to- Many relationship with the related tables.
When I try to delete a record from the front-end,I get a error msg "
the table is locked,you and another user are attempting to delete/
update the same record".I am the single user and I am at a loss to
understand what the problem is.
>
I have to explain that I never had a problem when i was using Access
as the backend too.Is it because of linking?
>
Any suggestions?
>
Roy
>

|||Jack Vamvas wrote:

Quote:

Originally Posted by

Are you are running the sql statement from Access?
Is the DSN a User DSN or System DSN?

What SQL statements does Profiler show as being attempted?
How are the relationships enforced (which ON DELETE option is in use)?

Delete All Tables from MS Access Database

HI

I want to delete all tables from an MS Access database.

i cannot use the designer . i have to do it thru an sql statement

a bunch of statements will also do . .

any body has a solution ?

P.s: All replies will be appreacited

Hi,

this is the SQL Server forum, perhaps you might try a Access forum, which might give you a wider variety of answers and experiences.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.desql

delete all tables

Hi folk,

whats the SQL syntax of deleting all user tables of a specific database on a Microsoft SQL server?

thanks in advance,

mulata

use yourTable;
go
EXEC sp_MSforeachtable @.command1 = "DROP TABLE ?"

--"sp_MSforeachtable" is one of the undocumented SQL Server system stored procedures.

|||

hm sorry I don"t understand your solution.

Is there a way that you do that in a "one line SQL statement" ,

something like: "drop table (select * from usertables) "

thanks

|||I agree with limon's solution, which is really straight and easy to use. It will go through each user table in current database and perform same action on each of them.?Since?DROP TABLE command can only be followed bytablename, I don't think you can drop all tables within one single statement except using such stored procedure.

Delete ALL rows in All Tables in a Single Database

Is there any easy way to do this?
(1) Script the objects, drop the database, re-create the database, run your
objects script.
(2) Drop all foreign key constraints, and then EXEC sp_msForEachTable
'TRUNCATE TABLE ?'
See yesterday's thread: "removing all data from all users table"
(It's often useful to scan the newsgroup quickly before posting your
question; you might avoid having to wait for an answer.)
http://www.aspfaq.com/
(Reverse address to reply.)
"Sarah" <skingswell@.donotreply.com> wrote in message
news:eYWQoNkSEHA.3872@.TK2MSFTNGP10.phx.gbl...
> Is there any easy way to do this?
>

Delete ALL rows in All Tables in a Single Database

Is there any easy way to do this?(1) Script the objects, drop the database, re-create the database, run your
objects script.
(2) Drop all foreign key constraints, and then EXEC sp_msForEachTable
'TRUNCATE TABLE ?'
See yesterday's thread: "removing all data from all users table"
(It's often useful to scan the newsgroup quickly before posting your
question; you might avoid having to wait for an answer.)
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Sarah" <skingswell@.donotreply.com> wrote in message
news:eYWQoNkSEHA.3872@.TK2MSFTNGP10.phx.gbl...
> Is there any easy way to do this?
>

Delete ALL rows in All Tables in a Single Database

Is there any easy way to do this?(1) Script the objects, drop the database, re-create the database, run your
objects script.
(2) Drop all foreign key constraints, and then EXEC sp_msForEachTable
'TRUNCATE TABLE ?'
See yesterday's thread: "removing all data from all users table"
(It's often useful to scan the newsgroup quickly before posting your
question; you might avoid having to wait for an answer.)
http://www.aspfaq.com/
(Reverse address to reply.)
"Sarah" <skingswell@.donotreply.com> wrote in message
news:eYWQoNkSEHA.3872@.TK2MSFTNGP10.phx.gbl...
> Is there any easy way to do this?
>

Delete All Rows

Is there a way to programatically delete all rows from a number of tables?
I am developing an application and am doing a lot of testing with dummy
data. As a result, I am regularly deleting the contents of tables. If I
could automate this down to a simple piece of code that would be great.
ThanksKeith,
This is from Uri's post sometime back.
DECLARE @.TruncateStatement nvarchar(4000)
DECLARE TruncateStatements CURSOR LOCAL FAST_FORWARD
FOR
SELECT
N'TRUNCATE TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
OPEN TruncateStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM TruncateStatements INTO @.TruncateStatement
IF @.@.FETCH_STATUS <> 0 BREAK
RAISERROR (@.TruncateStatement, 0, 1) WITH NOWAIT
EXEC(@.TruncateStatement)
END
CLOSE TruncateStatements
DEALLOCATE TruncateStatements
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
http://groups.msn.com/SQLBang
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Keith" <@..> wrote in message
news:%23lNF8J5TEHA.3512@.TK2MSFTNGP12.phx.gbl...
> Is there a way to programatically delete all rows from a number of tables?
> I am developing an application and am doing a lot of testing with dummy
> data. As a result, I am regularly deleting the contents of tables. If I
> could automate this down to a simple piece of code that would be great.
> Thanks
>|||Create a cursor which reads off of INFORMATION_SCHEMA.TABLES or sysobjects and for each table, fire TRUNCATE
TABLE or DELETE statement. Just be aware of foreign keys. If you have such, I suggest you start with "bottom
most" tables and do TRUNCATE for all tables that no other refers to and for those that are referenced, do
DELETE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Keith" <@..> wrote in message news:%23lNF8J5TEHA.3512@.TK2MSFTNGP12.phx.gbl...
> Is there a way to programatically delete all rows from a number of tables?
> I am developing an application and am doing a lot of testing with dummy
> data. As a result, I am regularly deleting the contents of tables. If I
> could automate this down to a simple piece of code that would be great.
> Thanks
>|||If this is just for use in a development environment then it's maybe
acceptable to use the undocumented funcion sp_msforeachtable:
EXEC SP_MSFOREACHTABLE '-- TRUNCATE TABLE ?'
or
EXEC SP_MSFOREACHTABLE '-- DELETE FROM ?'
(remove the -- comment from these statements to run them, but only when
you're sure you want to do this!)
TRUNCATE TABLE will fail on tables with Foreign Key dependencies so you may
need to use the DELETE version.
--
David Portas
SQL Server MVP
--|||Just be aware that if you use delete, the log will grow significantly. Be
sure to backup the log (perhaps even between each delete ) if the tables are
large, to keep the log from growing growing growing..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Keith" <@..> wrote in message
news:%23lNF8J5TEHA.3512@.TK2MSFTNGP12.phx.gbl...
> Is there a way to programatically delete all rows from a number of tables?
> I am developing an application and am doing a lot of testing with dummy
> data. As a result, I am regularly deleting the contents of tables. If I
> could automate this down to a simple piece of code that would be great.
> Thanks
>

Delete all data from user tables with a DELETE statement?

Hello,
I need an sql script that will do a 'DELETE FROM <table name>' against
are USER tables in a db.
It is for a small database that is getting a lot of test data.
How would I go about writing this?
Thanks,
TmuldWhy not take a FULL backup of the database when it is empty and then restore
that each time you wish to start over?
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Tmuldoon" <tmuldoon@.spliced.com> wrote in message
news:1187131121.604441.257780@.z24g2000prh.googlegroups.com...
> Hello,
> I need an sql script that will do a 'DELETE FROM <table name>' against
> are USER tables in a db.
> It is for a small database that is getting a lot of test data.
> How would I go about writing this?
> Thanks,
> Tmuld
>|||Hi
Take a look at Dan's script
DECLARE @.TruncateStatement nvarchar(4000)
DECLARE TruncateStatements CURSOR LOCAL FAST_FORWARD
FOR
SELECT
N'TRUNCATE TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE
_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
OPEN TruncateStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM TruncateStatements INTO @.TruncateStatement
IF @.@.FETCH_STATUS <> 0 BREAK
RAISERROR (@.TruncateStatement, 0, 1) WITH NOWAIT
EXEC(@.TruncateStatement)
END
CLOSE TruncateStatements
DEALLOCATE TruncateStatements
"Tmuldoon" <tmuldoon@.spliced.com> wrote in message
news:1187131121.604441.257780@.z24g2000prh.googlegroups.com...
> Hello,
> I need an sql script that will do a 'DELETE FROM <table name>' against
> are USER tables in a db.
> It is for a small database that is getting a lot of test data.
> How would I go about writing this?
> Thanks,
> Tmuld
>|||Something else you may want to try is to add the data source to a dotNet
project using the project options in NET 2005 (if you have it) and add a
setup project to the database you add to the project. The setup project can
be uninstalled and reinstalled and this will refresh the data each time
around.
--
Regards,
Jamie
"Tmuldoon" wrote:

> Hello,
> I need an sql script that will do a 'DELETE FROM <table name>' against
> are USER tables in a db.
> It is for a small database that is getting a lot of test data.
> How would I go about writing this?
> Thanks,
> Tmuld
>|||generating scripts for execution from system objects is a GREAT way to do
stuff like this. 2 things here tho:
1) You can't use truncate on tables with FKs.
2) You can avoid the cursor by creating the output and pasting it for
execution thusly:
select 'delete from ' + name + '
go'
from sys.objects --cheating here - I hate typing out the infoschema stuff
:-))
where type = 'u'
execute that, then copy the output into the query window and execute it.
TheSQLGuru
President
Indicium Resources, Inc.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23zuqAdw3HHA.2312@.TK2MSFTNGP06.phx.gbl...
> Hi
> Take a look at Dan's script
> DECLARE @.TruncateStatement nvarchar(4000)
> DECLARE TruncateStatements CURSOR LOCAL FAST_FORWARD
> FOR
> SELECT
> N'TRUNCATE TABLE ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)
> FROM
> INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = 'BASE TABLE' AND
> OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE
_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
> OPEN TruncateStatements
> WHILE 1 = 1
> BEGIN
> FETCH NEXT FROM TruncateStatements INTO @.TruncateStatement
> IF @.@.FETCH_STATUS <> 0 BREAK
> RAISERROR (@.TruncateStatement, 0, 1) WITH NOWAIT
> EXEC(@.TruncateStatement)
> END
> CLOSE TruncateStatements
> DEALLOCATE TruncateStatements
> "Tmuldoon" <tmuldoon@.spliced.com> wrote in message
> news:1187131121.604441.257780@.z24g2000prh.googlegroups.com...
>|||That would have been the smarted idea when I started...
Will do!
Thanks,
Tmuld|||It might be quicker to generate a script to create the FK constraints, drop
the FKs, truncate the tables and put the FKs back on.
The truncate table script can be created using the sysobjects table also
1;it
is quicker than using INFO SCHEMA ]
The 'delete from' statement would log everything taking much more time.
Thank you for sharing your knowledge
"TheSQLGuru" wrote:

> generating scripts for execution from system objects is a GREAT way to do
> stuff like this. 2 things here tho:
> 1) You can't use truncate on tables with FKs.
> 2) You can avoid the cursor by creating the output and pasting it for
> execution thusly:
> select 'delete from ' + name + '
> go'
> from sys.objects --cheating here - I hate typing out the infoschema stuff
> :-))
> where type = 'u'
> execute that, then copy the output into the query window and execute it.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23zuqAdw3HHA.2312@.TK2MSFTNGP06.phx.gbl...
>
>

Delete all data from user tables with a DELETE statement?

Hello,
I need an sql script that will do a 'DELETE FROM <table name>' against
are USER tables in a db.
It is for a small database that is getting a lot of test data.
How would I go about writing this?
Thanks,
Tmuld
Why not take a FULL backup of the database when it is empty and then restore
that each time you wish to start over?
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Tmuldoon" <tmuldoon@.spliced.com> wrote in message
news:1187131121.604441.257780@.z24g2000prh.googlegr oups.com...
> Hello,
> I need an sql script that will do a 'DELETE FROM <table name>' against
> are USER tables in a db.
> It is for a small database that is getting a lot of test data.
> How would I go about writing this?
> Thanks,
> Tmuld
>
|||Hi
Take a look at Dan's script
DECLARE @.TruncateStatement nvarchar(4000)
DECLARE TruncateStatements CURSOR LOCAL FAST_FORWARD
FOR
SELECT
N'TRUNCATE TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
OPEN TruncateStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM TruncateStatements INTO @.TruncateStatement
IF @.@.FETCH_STATUS <> 0 BREAK
RAISERROR (@.TruncateStatement, 0, 1) WITH NOWAIT
EXEC(@.TruncateStatement)
END
CLOSE TruncateStatements
DEALLOCATE TruncateStatements
"Tmuldoon" <tmuldoon@.spliced.com> wrote in message
news:1187131121.604441.257780@.z24g2000prh.googlegr oups.com...
> Hello,
> I need an sql script that will do a 'DELETE FROM <table name>' against
> are USER tables in a db.
> It is for a small database that is getting a lot of test data.
> How would I go about writing this?
> Thanks,
> Tmuld
>
|||Something else you may want to try is to add the data source to a dotNet
project using the project options in NET 2005 (if you have it) and add a
setup project to the database you add to the project. The setup project can
be uninstalled and reinstalled and this will refresh the data each time
around.
Regards,
Jamie
"Tmuldoon" wrote:

> Hello,
> I need an sql script that will do a 'DELETE FROM <table name>' against
> are USER tables in a db.
> It is for a small database that is getting a lot of test data.
> How would I go about writing this?
> Thanks,
> Tmuld
>
|||generating scripts for execution from system objects is a GREAT way to do
stuff like this. 2 things here tho:
1) You can't use truncate on tables with FKs.
2) You can avoid the cursor by creating the output and pasting it for
execution thusly:
select 'delete from ' + name + '
go'
from sys.objects --cheating here - I hate typing out the infoschema stuff
:-))
where type = 'u'
execute that, then copy the output into the query window and execute it.
TheSQLGuru
President
Indicium Resources, Inc.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23zuqAdw3HHA.2312@.TK2MSFTNGP06.phx.gbl...
> Hi
> Take a look at Dan's script
> DECLARE @.TruncateStatement nvarchar(4000)
> DECLARE TruncateStatements CURSOR LOCAL FAST_FORWARD
> FOR
> SELECT
> N'TRUNCATE TABLE ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)
> FROM
> INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = 'BASE TABLE' AND
> OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
> OPEN TruncateStatements
> WHILE 1 = 1
> BEGIN
> FETCH NEXT FROM TruncateStatements INTO @.TruncateStatement
> IF @.@.FETCH_STATUS <> 0 BREAK
> RAISERROR (@.TruncateStatement, 0, 1) WITH NOWAIT
> EXEC(@.TruncateStatement)
> END
> CLOSE TruncateStatements
> DEALLOCATE TruncateStatements
> "Tmuldoon" <tmuldoon@.spliced.com> wrote in message
> news:1187131121.604441.257780@.z24g2000prh.googlegr oups.com...
>
|||That would have been the smarted idea when I started...
Will do!
Thanks,
Tmuld
|||It might be quicker to generate a script to create the FK constraints, drop
the FKs, truncate the tables and put the FKs back on.
The truncate table script can be created using the sysobjects table also [it
is quicker than using INFO SCHEMA ]
The 'delete from' statement would log everything taking much more time.
Thank you for sharing your knowledge
"TheSQLGuru" wrote:

> generating scripts for execution from system objects is a GREAT way to do
> stuff like this. 2 things here tho:
> 1) You can't use truncate on tables with FKs.
> 2) You can avoid the cursor by creating the output and pasting it for
> execution thusly:
> select 'delete from ' + name + '
> go'
> from sys.objects --cheating here - I hate typing out the infoschema stuff
> :-))
> where type = 'u'
> execute that, then copy the output into the query window and execute it.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23zuqAdw3HHA.2312@.TK2MSFTNGP06.phx.gbl...
>
>
sql