Deleting rows

What tool are you using?

>--Original Message--
>I have a table wherein previous entries on some rows were
deleted but the
>fields doesn't go away ex.
>tbl_name (one column table only)
>row1 name 1
>row2 (the entry is deleted but this is still showing a
blank space)
>row3 (the entry is deleted but this is still showing a
blank space)
>row4 (the entry is deleted but this is still showing a
blank space)
>row5 name 2
>How do i delete rows 2-4 in tbl_name so that it will only
show two entries
>row1 and row5 which should move into position 2 basically
deleting rows 2-4
>which contains no data and wont allow me to enter data in
them?
>thanks....
>.
>
When you delete the rows, it sounds to me like you are really only updating
the value of the data to a blank rather than actually removing the row.
The procedure in your application is probably doing an update like:
UPDATE tblName
SET columnName = ''
WHERE columnName = '<some criteria>'
The procedure in your application should be doing a DELETE like:
DELETE tblName
WHERE columnName = '<some criteria>'
To get rid of the currently empty rows, you could run something like:
DELETE tblName
WHERE LENGTH(columnName) = 0
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:08ff01c47afa$4c7e3a10$a301280a@.phx.gbl...[vbcol=seagreen]
> What tool are you using?
> deleted but the
> blank space)
> blank space)
> blank space)
> show two entries
> deleting rows 2-4
> them?
sql

Deleting rows

What tool are you using?

>--Original Message--
>I have a table wherein previous entries on some rows were
deleted but the
>fields doesn't go away ex.
>tbl_name (one column table only)
>row1 name 1
>row2 (the entry is deleted but this is still showing a
blank space)
>row3 (the entry is deleted but this is still showing a
blank space)
>row4 (the entry is deleted but this is still showing a
blank space)
>row5 name 2
>How do i delete rows 2-4 in tbl_name so that it will only
show two entries
>row1 and row5 which should move into position 2 basically
deleting rows 2-4
>which contains no data and wont allow me to enter data in
them?
>thanks....
>.
>When you delete the rows, it sounds to me like you are really only updating
the value of the data to a blank rather than actually removing the row.
The procedure in your application is probably doing an update like:
UPDATE tblName
SET columnName = ''
WHERE columnName = '<some criteria>'
The procedure in your application should be doing a DELETE like:
DELETE tblName
WHERE columnName = '<some criteria>'
To get rid of the currently empty rows, you could run something like:
DELETE tblName
WHERE LENGTH(columnName) = 0
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:08ff01c47afa$4c7e3a10$a301280a@.phx.gbl...[vbcol=seagreen]
> What tool are you using?
>
> deleted but the
> blank space)
> blank space)
> blank space)
> show two entries
> deleting rows 2-4
> them?

DELETE Rouge Statistic from sys.sysindexes

Morning,

I have a stale statistic that I need to remove from the sysindexes table.

It is causing our DB upgrade tool to fail.

In the past, on SQL Server 2000, I could just go into the sysindexes table and delete it.

In SQL Server 2005 of course, no ad hoc catalog changes error prohibits me from doing this.

How can I delete this rouge stat?

Thanks ALL!

What about using DROP STATISTICS?

AMB

|||

Thanks for the info AMB.

SO ... if I have a statistic named '_WA_Sys_name_009508B4'

How do I find what table it is referencing in order to use that table name in the DROP STATISTICS command?

Thanks for your help so far!

J Kusch

|||

Try:

select

object_name([object_id])as table_name,

[name] as stats_name

from

sys.stats

where

[name] like'\_WA\_Sys\_%'escape'\'

AMB

|||

PERFECT!

Thanks a bunch for all the help!

DELETE Rouge Statistic from sys.sysindexes

Morning,

I have a stale statistic that I need to remove from the sysindexes table.

It is causing our DB upgrade tool to fail.

In the past, on SQL Server 2000, I could just go into the sysindexes table and delete it.

In SQL Server 2005 of course, no ad hoc catalog changes error prohibits me from doing this.

How can I delete this rouge stat?

Thanks ALL!

What about using DROP STATISTICS?

AMB

|||

Thanks for the info AMB.

SO ... if I have a statistic named '_WA_Sys_name_009508B4'

How do I find what table it is referencing in order to use that table name in the DROP STATISTICS command?

Thanks for your help so far!

J Kusch

|||

Try:

select

object_name([object_id])as table_name,

[name] as stats_name

from

sys.stats

where

[name] like'\_WA\_Sys\_%'escape'\'

AMB

|||

PERFECT!

Thanks a bunch for all the help!

delete records in tables

Is there any built-in tool that can delete tabel
records/contents in sql server? I can run delete from
tablename script, but there are two many tables. is there
an easy way?Look at this...
Declare @.sql varchar(8000)
set @.sql=''
select @.sql=@.sql + ' Truncate Table ' + name from sysobjects where
xtype='U'
EXEC(@.SQL) -- Truncates all tables
set @.sql=''
Select @.sql=@.sql + ' DROP TABLE ' + name from sysobjects where xtype='U'
EXEC(@.SQL) -- Drops all tables
HTH
"Matt" <spam@.spam.com> escreveu na mensagem
news:108001c3a920$524706e0$a101280a@.phx.gbl...
> Is there any built-in tool that can delete tabel
> records/contents in sql server? I can run delete from
> tablename script, but there are two many tables. is there
> an easy way?
>|||If you run the following SQL it will create a DELETE script for every table
that can then be ran.
SELECT 'DELETE FROM ' + TABLE_SCHEMA+'.'+TABLE_NAME + char(10) + 'GO'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
--
HTH
Ryan Waight, MCDBA, MCSE
"Matt" <spam@.spam.com> wrote in message
news:108001c3a920$524706e0$a101280a@.phx.gbl...
> Is there any built-in tool that can delete tabel
> records/contents in sql server? I can run delete from
> tablename script, but there are two many tables. is there
> an easy way?
>|||Hi,
Truncate table command will not succeed if there is FK relation ship. The
parent table will fail with below error,
Server: Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'table name' because it is being referenced by a
FOREIGN KEY constraint.
In this case you have to use delete command. (delete the child table using
Truncate command and use delete command for Parent table)
Thanks
Hari
MCDBA
"Wandenkolk T. Neto" <wandenkolkneto@.hotmail.com> wrote in message
news:#u4uRLSqDHA.688@.TK2MSFTNGP10.phx.gbl...
> Look at this...
> Declare @.sql varchar(8000)
> set @.sql=''
> select @.sql=@.sql + ' Truncate Table ' + name from sysobjects where
> xtype='U'
> EXEC(@.SQL) -- Truncates all tables
> set @.sql=''
> Select @.sql=@.sql + ' DROP TABLE ' + name from sysobjects where
xtype='U'
> EXEC(@.SQL) -- Drops all tables
>
> HTH
> "Matt" <spam@.spam.com> escreveu na mensagem
> news:108001c3a920$524706e0$a101280a@.phx.gbl...
> > Is there any built-in tool that can delete tabel
> > records/contents in sql server? I can run delete from
> > tablename script, but there are two many tables. is there
> > an easy way?
> >
>