Showing posts with label script. Show all posts
Showing posts with label script. Show all posts

Tuesday, March 27, 2012

delete all statistics in db

Hi,
anybody have a script or an idea on how to delete all stats in a db?
while on the topic why do some stats (column I think) prevent column from
being upgraded via alter table statement? I would have expected stats to be
dropped as soon as column modified.
Thanks.
Try the following post from Dan Guzman:
http://groups.google.com/group/micro...5ec3c61?hl=en&
The requirement is listed in books online under the alter
table topic in the section alter column - the limitation is
from statistics generated by an explicit create statistics
statement. I think you also can hit issues if you have
hypothetical index left behind from the Index tuning wizard.
From books online:
The altered column cannot be:
Used in statistics generated by the CREATE STATISTICS
statement. First remove the statistics using the DROP
STATISTICS statement. Statistics automatically generated by
the query optimizer are automatically dropped by ALTER
COLUMN.
-Sue
On Tue, 13 Sep 2005 15:03:01 -0700, "sysbox27"
<sysbox27@.discussions.microsoft.com> wrote:

>Hi,
>anybody have a script or an idea on how to delete all stats in a db?
>while on the topic why do some stats (column I think) prevent column from
>being upgraded via alter table statement? I would have expected stats to be
>dropped as soon as column modified.
>Thanks.
|||Thanks very much Sue.
"Sue Hoegemeier" wrote:

> Try the following post from Dan Guzman:
> http://groups.google.com/group/micro...5ec3c61?hl=en&
> The requirement is listed in books online under the alter
> table topic in the section alter column - the limitation is
> from statistics generated by an explicit create statistics
> statement. I think you also can hit issues if you have
> hypothetical index left behind from the Index tuning wizard.
> From books online:
> The altered column cannot be:
> Used in statistics generated by the CREATE STATISTICS
> statement. First remove the statistics using the DROP
> STATISTICS statement. Statistics automatically generated by
> the query optimizer are automatically dropped by ALTER
> COLUMN.
> -Sue
> On Tue, 13 Sep 2005 15:03:01 -0700, "sysbox27"
> <sysbox27@.discussions.microsoft.com> wrote:
>
>

delete all statistics in db

Hi,
anybody have a script or an idea on how to delete all stats in a db?
while on the topic why do some stats (column I think) prevent column from
being upgraded via alter table statement? I would have expected stats to be
dropped as soon as column modified.
Thanks.Try the following post from Dan Guzman:
http://groups.google.com/group/microsoft.public.sqlserver.server/msg/8dd9fd47a5ec3c61?hl=en&
The requirement is listed in books online under the alter
table topic in the section alter column - the limitation is
from statistics generated by an explicit create statistics
statement. I think you also can hit issues if you have
hypothetical index left behind from the Index tuning wizard.
From books online:
The altered column cannot be:
Used in statistics generated by the CREATE STATISTICS
statement. First remove the statistics using the DROP
STATISTICS statement. Statistics automatically generated by
the query optimizer are automatically dropped by ALTER
COLUMN.
-Sue
On Tue, 13 Sep 2005 15:03:01 -0700, "sysbox27"
<sysbox27@.discussions.microsoft.com> wrote:
>Hi,
>anybody have a script or an idea on how to delete all stats in a db?
>while on the topic why do some stats (column I think) prevent column from
>being upgraded via alter table statement? I would have expected stats to be
>dropped as soon as column modified.
>Thanks.|||Thanks very much Sue.
"Sue Hoegemeier" wrote:
> Try the following post from Dan Guzman:
> http://groups.google.com/group/microsoft.public.sqlserver.server/msg/8dd9fd47a5ec3c61?hl=en&
> The requirement is listed in books online under the alter
> table topic in the section alter column - the limitation is
> from statistics generated by an explicit create statistics
> statement. I think you also can hit issues if you have
> hypothetical index left behind from the Index tuning wizard.
> From books online:
> The altered column cannot be:
> Used in statistics generated by the CREATE STATISTICS
> statement. First remove the statistics using the DROP
> STATISTICS statement. Statistics automatically generated by
> the query optimizer are automatically dropped by ALTER
> COLUMN.
> -Sue
> On Tue, 13 Sep 2005 15:03:01 -0700, "sysbox27"
> <sysbox27@.discussions.microsoft.com> wrote:
> >Hi,
> >anybody have a script or an idea on how to delete all stats in a db?
> >while on the topic why do some stats (column I think) prevent column from
> >being upgraded via alter table statement? I would have expected stats to be
> >dropped as soon as column modified.
> >Thanks.
>

delete all statistics in db

Hi,
anybody have a script or an idea on how to delete all stats in a db?
while on the topic why do some stats (column I think) prevent column from
being upgraded via alter table statement? I would have expected stats to be
dropped as soon as column modified.
Thanks.Try the following post from Dan Guzman:
http://groups.google.com/group/micr... />
c61?hl=en&
The requirement is listed in books online under the alter
table topic in the section alter column - the limitation is
from statistics generated by an explicit create statistics
statement. I think you also can hit issues if you have
hypothetical index left behind from the Index tuning wizard.
From books online:
The altered column cannot be:
Used in statistics generated by the CREATE STATISTICS
statement. First remove the statistics using the DROP
STATISTICS statement. Statistics automatically generated by
the query optimizer are automatically dropped by ALTER
COLUMN.
-Sue
On Tue, 13 Sep 2005 15:03:01 -0700, "sysbox27"
<sysbox27@.discussions.microsoft.com> wrote:

>Hi,
>anybody have a script or an idea on how to delete all stats in a db?
>while on the topic why do some stats (column I think) prevent column from
>being upgraded via alter table statement? I would have expected stats to be
>dropped as soon as column modified.
>Thanks.|||Thanks very much Sue.
"Sue Hoegemeier" wrote:

> Try the following post from Dan Guzman:
> http://groups.google.com/group/micr...>
c3c61?hl=en&
> The requirement is listed in books online under the alter
> table topic in the section alter column - the limitation is
> from statistics generated by an explicit create statistics
> statement. I think you also can hit issues if you have
> hypothetical index left behind from the Index tuning wizard.
> From books online:
> The altered column cannot be:
> Used in statistics generated by the CREATE STATISTICS
> statement. First remove the statistics using the DROP
> STATISTICS statement. Statistics automatically generated by
> the query optimizer are automatically dropped by ALTER
> COLUMN.
> -Sue
> On Tue, 13 Sep 2005 15:03:01 -0700, "sysbox27"
> <sysbox27@.discussions.microsoft.com> wrote:
>
>

Sunday, March 25, 2012

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

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...
>> 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
>|||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...
> > 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
> >>
> >
> >
>
>

delete all data from database

Does anyone have or know of a way/script where I can delete all data from a
database following integrity rules?
Thanks
Rob
What about dropping the database and recreating it?
AMB
"Rob Blij" wrote:

> Does anyone have or know of a way/script where I can delete all data from a
> database following integrity rules?
> Thanks
> Rob
>
>
|||Without referential integrity
[code]
use MyDatabase
go
exec sp_MSforeachtable "alter table ? nocheck constraint all"
exec sp_MSforeachtable "delete from ?"
exec sp_MSforeachtable "alter table ? check constraint all"
[/code]
Cristian Lefter, SQL Server MVP
"Rob Blij" <robblij@.community.nospam> wrote in message
news:OKXB6dqaFHA.1044@.TK2MSFTNGP10.phx.gbl...
> Does anyone have or know of a way/script where I can delete all data from
> a database following integrity rules?
> Thanks
> Rob
>
|||Thanks cristian works like a charm
would it only delete from my user tables?
"Cristian Lefter" <nospam_CristianLefter@.hotmail.com> wrote in message
news:%23KVKwuqaFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Without referential integrity
> [code]
> use MyDatabase
> go
> exec sp_MSforeachtable "alter table ? nocheck constraint all"
> exec sp_MSforeachtable "delete from ?"
> exec sp_MSforeachtable "alter table ? check constraint all"
> [/code]
> Cristian Lefter, SQL Server MVP
> "Rob Blij" <robblij@.community.nospam> wrote in message
> news:OKXB6dqaFHA.1044@.TK2MSFTNGP10.phx.gbl...
>
|||You could certainly query the catalogue to generate the RI; however, you
should never delete the data without knowing the design of the "logical data
model" (LDB).
You could use the Database Digram tool to generate a graphic or a
third-party tool that could generate a model, but this would only be a
"physical data model" (PDM).
From this you could "infer" an LDM, but without a real one, there would be
no gaurantees.
If you are lucky, that is, the database designer used Declaritive RI and
other contraints exclusively. If you ran the sp_MSforeachtable script, then
only the child table data would succeed without error. Then, you could run
again to get the next level. When you could run the script without error,
you'd be done.
Sincerely,
Anthony Thomas

"Rob Blij" <robblij@.community.nospam> wrote in message
news:OKXB6dqaFHA.1044@.TK2MSFTNGP10.phx.gbl...
Does anyone have or know of a way/script where I can delete all data from a
database following integrity rules?
Thanks
Rob
sql

delete all data from database

Does anyone have or know of a way/script where I can delete all data from a
database following integrity rules?
Thanks
RobWhat about dropping the database and recreating it?
AMB
"Rob Blij" wrote:
> Does anyone have or know of a way/script where I can delete all data from a
> database following integrity rules?
> Thanks
> Rob
>
>|||Without referential integrity
[code]
use MyDatabase
go
exec sp_MSforeachtable "alter table ? nocheck constraint all"
exec sp_MSforeachtable "delete from ?"
exec sp_MSforeachtable "alter table ? check constraint all"
[/code]
Cristian Lefter, SQL Server MVP
"Rob Blij" <robblij@.community.nospam> wrote in message
news:OKXB6dqaFHA.1044@.TK2MSFTNGP10.phx.gbl...
> Does anyone have or know of a way/script where I can delete all data from
> a database following integrity rules?
> Thanks
> Rob
>|||Thanks cristian works like a charm
would it only delete from my user tables?
"Cristian Lefter" <nospam_CristianLefter@.hotmail.com> wrote in message
news:%23KVKwuqaFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Without referential integrity
> [code]
> use MyDatabase
> go
> exec sp_MSforeachtable "alter table ? nocheck constraint all"
> exec sp_MSforeachtable "delete from ?"
> exec sp_MSforeachtable "alter table ? check constraint all"
> [/code]
> Cristian Lefter, SQL Server MVP
> "Rob Blij" <robblij@.community.nospam> wrote in message
> news:OKXB6dqaFHA.1044@.TK2MSFTNGP10.phx.gbl...
>> Does anyone have or know of a way/script where I can delete all data from
>> a database following integrity rules?
>> Thanks
>> Rob
>|||You could certainly query the catalogue to generate the RI; however, you
should never delete the data without knowing the design of the "logical data
model" (LDB).
You could use the Database Digram tool to generate a graphic or a
third-party tool that could generate a model, but this would only be a
"physical data model" (PDM).
From this you could "infer" an LDM, but without a real one, there would be
no gaurantees.
If you are lucky, that is, the database designer used Declaritive RI and
other contraints exclusively. If you ran the sp_MSforeachtable script, then
only the child table data would succeed without error. Then, you could run
again to get the next level. When you could run the script without error,
you'd be done.
Sincerely,
Anthony Thomas
"Rob Blij" <robblij@.community.nospam> wrote in message
news:OKXB6dqaFHA.1044@.TK2MSFTNGP10.phx.gbl...
Does anyone have or know of a way/script where I can delete all data from a
database following integrity rules?
Thanks
Rob

delete all data from database

Does anyone have or know of a way/script where I can delete all data from a
database following integrity rules?
Thanks
RobWhat about dropping the database and recreating it?
AMB
"Rob Blij" wrote:

> Does anyone have or know of a way/script where I can delete all data from
a
> database following integrity rules?
> Thanks
> Rob
>
>|||Without referential integrity
[code]
use MyDatabase
go
exec sp_MSforeachtable "alter table ? nocheck constraint all"
exec sp_MSforeachtable "delete from ?"
exec sp_MSforeachtable "alter table ? check constraint all"
[/code]
Cristian Lefter, SQL Server MVP
"Rob Blij" <robblij@.community.nospam> wrote in message
news:OKXB6dqaFHA.1044@.TK2MSFTNGP10.phx.gbl...
> Does anyone have or know of a way/script where I can delete all data from
> a database following integrity rules?
> Thanks
> Rob
>|||Thanks cristian works like a charm
would it only delete from my user tables?
"Cristian Lefter" <nospam_CristianLefter@.hotmail.com> wrote in message
news:%23KVKwuqaFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Without referential integrity
> [code]
> use MyDatabase
> go
> exec sp_MSforeachtable "alter table ? nocheck constraint all"
> exec sp_MSforeachtable "delete from ?"
> exec sp_MSforeachtable "alter table ? check constraint all"
> [/code]
> Cristian Lefter, SQL Server MVP
> "Rob Blij" <robblij@.community.nospam> wrote in message
> news:OKXB6dqaFHA.1044@.TK2MSFTNGP10.phx.gbl...
>|||You could certainly query the catalogue to generate the RI; however, you
should never delete the data without knowing the design of the "logical data
model" (LDB).
You could use the Database Digram tool to generate a graphic or a
third-party tool that could generate a model, but this would only be a
"physical data model" (PDM).
From this you could "infer" an LDM, but without a real one, there would be
no gaurantees.
If you are lucky, that is, the database designer used Declaritive RI and
other contraints exclusively. If you ran the sp_MSforeachtable script, then
only the child table data would succeed without error. Then, you could run
again to get the next level. When you could run the script without error,
you'd be done.
Sincerely,
Anthony Thomas
"Rob Blij" <robblij@.community.nospam> wrote in message
news:OKXB6dqaFHA.1044@.TK2MSFTNGP10.phx.gbl...
Does anyone have or know of a way/script where I can delete all data from a
database following integrity rules?
Thanks
Rob

Wednesday, March 21, 2012

del dupes

record# cat product
1 fish halibut
2 fish halibut
I need a script to find all records with duplicates similar to the scenario
above and delete the duplicates, in this case record#2. Thank you.Try:
;with x
as
(
select
*
, row_number () over (partition by cat, product order by cat product)
as row_num
from
MyTable
)
delete x
where
row_num > 1
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:31A254C8-43FE-4FCE-9DB8-0B06FF18B6DB@.microsoft.com...
record# cat product
1 fish halibut
2 fish halibut
I need a script to find all records with duplicates similar to the scenario
above and delete the duplicates, in this case record#2. Thank you.|||> I need a script to find all records with duplicates similar to the
> scenario
> above and delete the duplicates, in this case record#2. Thank you.
Same question was posted and answered in .programming yesterday. Have a
look.

del dupes

record# cat product
1 fish halibut
2 fish halibut
I need a script to find all records with duplicates similar to the scenario
above and delete the duplicates, in this case record#2. Thank you.
Try:
;with x
as
(
select
*
, row_number () over (partition by cat, product order by cat product)
as row_num
from
MyTable
)
delete x
where
row_num > 1
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:31A254C8-43FE-4FCE-9DB8-0B06FF18B6DB@.microsoft.com...
record# cat product
1 fish halibut
2 fish halibut
I need a script to find all records with duplicates similar to the scenario
above and delete the duplicates, in this case record#2. Thank you.
|||> I need a script to find all records with duplicates similar to the
> scenario
> above and delete the duplicates, in this case record#2. Thank you.
Same question was posted and answered in .programming yesterday. Have a
look.

del dupes

record# cat product
1 fish halibut
2 fish halibut
I need a script to find all records with duplicates similar to the scenario
above and delete the duplicates, in this case record#2. Thank you.Try:
;with x
as
(
select
*
, row_number () over (partition by cat, product order by cat product)
as row_num
from
MyTable
)
delete x
where
row_num > 1
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:31A254C8-43FE-4FCE-9DB8-0B06FF18B6DB@.microsoft.com...
record# cat product
1 fish halibut
2 fish halibut
I need a script to find all records with duplicates similar to the scenario
above and delete the duplicates, in this case record#2. Thank you.|||> I need a script to find all records with duplicates similar to the
> scenario
> above and delete the duplicates, in this case record#2. Thank you.
Same question was posted and answered in .programming yesterday. Have a
look.

Sunday, March 11, 2012

Defrag Script

Hi all,
I'm writing my own defrag script. I've set it up so under certain conditions
it will run an IndexDefrag and under others a Reindex. I wanted to post the
conditions I've set and see if anyone has any feedback that can improve them.
I have two temporary tables 1 contains the tables and indexes, and one the
ShowContig information.
The IndexDefrag test is:
UPDATE #TableIndexList
SET ReqAction = 'I'
FROM #TableIndexList AS a
INNER JOIN #FragStats AS b
ON a.ObjectID = b.ObjectID
WHERE b.Pages BETWEEN 16 AND 4096
AND ((b.LogicalFragmentation BETWEEN 20 AND 40)
OR b.AveragePageDensity < 75)
The reindex criteria is:
UPDATE #TableIndexList
SET ReqAction = 'R'
FROM #TableIndexList AS a
INNER JOIN #FragStats AS b
ON a.ObjectID = b.ObjectID
WHERE (b.Pages > 16
AND (b.LogicalFragmentation > 40
OR b.AveragePageDensity > 95))
OR (b.Pages > 4096
AND (b.LogicalFragmentation > 20
OR b.AveragePageDensity < 75))
As advised any feedback would be greatly appreciated...Have you compared the before and after results. Sometimes due to the way you
have constructed your table you will find residual fragmentation after
running such scripts will be almost the same.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"BenUK" <BenUK@.discussions.microsoft.com> wrote in message
news:AD82F542-DD91-4973-B0C1-8DAB523A7EF8@.microsoft.com...
> Hi all,
> I'm writing my own defrag script. I've set it up so under certain
> conditions
> it will run an IndexDefrag and under others a Reindex. I wanted to post
> the
> conditions I've set and see if anyone has any feedback that can improve
> them.
> I have two temporary tables 1 contains the tables and indexes, and one the
> ShowContig information.
> The IndexDefrag test is:
> UPDATE #TableIndexList
> SET ReqAction = 'I'
> FROM #TableIndexList AS a
> INNER JOIN #FragStats AS b
> ON a.ObjectID = b.ObjectID
> WHERE b.Pages BETWEEN 16 AND 4096
> AND ((b.LogicalFragmentation BETWEEN 20 AND 40)
> OR b.AveragePageDensity < 75)
> The reindex criteria is:
> UPDATE #TableIndexList
> SET ReqAction = 'R'
> FROM #TableIndexList AS a
> INNER JOIN #FragStats AS b
> ON a.ObjectID = b.ObjectID
> WHERE (b.Pages > 16
> AND (b.LogicalFragmentation > 40
> OR b.AveragePageDensity > 95))
> OR (b.Pages > 4096
> AND (b.LogicalFragmentation > 20
> OR b.AveragePageDensity < 75))
> As advised any feedback would be greatly appreciated...|||Thats interesting.. ..I'm identifying heaps for manual defrags, I'm excluding
blobs, is it a scenario where the table is busy and the IndexDefrag is
skipping the pages?
"Hilary Cotter" wrote:
> Have you compared the before and after results. Sometimes due to the way you
> have constructed your table you will find residual fragmentation after
> running such scripts will be almost the same.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> news:AD82F542-DD91-4973-B0C1-8DAB523A7EF8@.microsoft.com...
> > Hi all,
> >
> > I'm writing my own defrag script. I've set it up so under certain
> > conditions
> > it will run an IndexDefrag and under others a Reindex. I wanted to post
> > the
> > conditions I've set and see if anyone has any feedback that can improve
> > them.
> > I have two temporary tables 1 contains the tables and indexes, and one the
> > ShowContig information.
> >
> > The IndexDefrag test is:
> > UPDATE #TableIndexList
> > SET ReqAction = 'I'
> > FROM #TableIndexList AS a
> > INNER JOIN #FragStats AS b
> > ON a.ObjectID = b.ObjectID
> > WHERE b.Pages BETWEEN 16 AND 4096
> > AND ((b.LogicalFragmentation BETWEEN 20 AND 40)
> > OR b.AveragePageDensity < 75)
> >
> > The reindex criteria is:
> > UPDATE #TableIndexList
> > SET ReqAction = 'R'
> > FROM #TableIndexList AS a
> > INNER JOIN #FragStats AS b
> > ON a.ObjectID = b.ObjectID
> > WHERE (b.Pages > 16
> > AND (b.LogicalFragmentation > 40
> > OR b.AveragePageDensity > 95))
> > OR (b.Pages > 4096
> > AND (b.LogicalFragmentation > 20
> > OR b.AveragePageDensity < 75))
> >
> > As advised any feedback would be greatly appreciated...
>
>|||or maybe when there's not enough disk space to run DBCC Reindex on a large
table
"Hilary Cotter" wrote:
> Have you compared the before and after results. Sometimes due to the way you
> have constructed your table you will find residual fragmentation after
> running such scripts will be almost the same.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> news:AD82F542-DD91-4973-B0C1-8DAB523A7EF8@.microsoft.com...
> > Hi all,
> >
> > I'm writing my own defrag script. I've set it up so under certain
> > conditions
> > it will run an IndexDefrag and under others a Reindex. I wanted to post
> > the
> > conditions I've set and see if anyone has any feedback that can improve
> > them.
> > I have two temporary tables 1 contains the tables and indexes, and one the
> > ShowContig information.
> >
> > The IndexDefrag test is:
> > UPDATE #TableIndexList
> > SET ReqAction = 'I'
> > FROM #TableIndexList AS a
> > INNER JOIN #FragStats AS b
> > ON a.ObjectID = b.ObjectID
> > WHERE b.Pages BETWEEN 16 AND 4096
> > AND ((b.LogicalFragmentation BETWEEN 20 AND 40)
> > OR b.AveragePageDensity < 75)
> >
> > The reindex criteria is:
> > UPDATE #TableIndexList
> > SET ReqAction = 'R'
> > FROM #TableIndexList AS a
> > INNER JOIN #FragStats AS b
> > ON a.ObjectID = b.ObjectID
> > WHERE (b.Pages > 16
> > AND (b.LogicalFragmentation > 40
> > OR b.AveragePageDensity > 95))
> > OR (b.Pages > 4096
> > AND (b.LogicalFragmentation > 20
> > OR b.AveragePageDensity < 75))
> >
> > As advised any feedback would be greatly appreciated...
>
>

Defrag Script

Hi all,
I'm writing my own defrag script. I've set it up so under certain conditions
it will run an IndexDefrag and under others a Reindex. I wanted to post the
conditions I've set and see if anyone has any feedback that can improve them
.
I have two temporary tables 1 contains the tables and indexes, and one the
ShowContig information.
The IndexDefrag test is:
UPDATE #TableIndexList
SET ReqAction = 'I'
FROM #TableIndexList AS a
INNER JOIN #FragStats AS b
ON a.ObjectID = b.ObjectID
WHERE b.Pages BETWEEN 16 AND 4096
AND ((b.LogicalFragmentation BETWEEN 20 AND 40)
OR b.AveragePageDensity < 75)
The reindex criteria is:
UPDATE #TableIndexList
SET ReqAction = 'R'
FROM #TableIndexList AS a
INNER JOIN #FragStats AS b
ON a.ObjectID = b.ObjectID
WHERE (b.Pages > 16
AND (b.LogicalFragmentation > 40
OR b.AveragePageDensity > 95))
OR (b.Pages > 4096
AND (b.LogicalFragmentation > 20
OR b.AveragePageDensity < 75))
As advised any feedback would be greatly appreciated...Have you compared the before and after results. Sometimes due to the way you
have constructed your table you will find residual fragmentation after
running such scripts will be almost the same.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"BenUK" <BenUK@.discussions.microsoft.com> wrote in message
news:AD82F542-DD91-4973-B0C1-8DAB523A7EF8@.microsoft.com...
> Hi all,
> I'm writing my own defrag script. I've set it up so under certain
> conditions
> it will run an IndexDefrag and under others a Reindex. I wanted to post
> the
> conditions I've set and see if anyone has any feedback that can improve
> them.
> I have two temporary tables 1 contains the tables and indexes, and one the
> ShowContig information.
> The IndexDefrag test is:
> UPDATE #TableIndexList
> SET ReqAction = 'I'
> FROM #TableIndexList AS a
> INNER JOIN #FragStats AS b
> ON a.ObjectID = b.ObjectID
> WHERE b.Pages BETWEEN 16 AND 4096
> AND ((b.LogicalFragmentation BETWEEN 20 AND 40)
> OR b.AveragePageDensity < 75)
> The reindex criteria is:
> UPDATE #TableIndexList
> SET ReqAction = 'R'
> FROM #TableIndexList AS a
> INNER JOIN #FragStats AS b
> ON a.ObjectID = b.ObjectID
> WHERE (b.Pages > 16
> AND (b.LogicalFragmentation > 40
> OR b.AveragePageDensity > 95))
> OR (b.Pages > 4096
> AND (b.LogicalFragmentation > 20
> OR b.AveragePageDensity < 75))
> As advised any feedback would be greatly appreciated...|||Thats interesting.. ..I'm identifying heaps for manual defrags, I'm excludin
g
blobs, is it a scenario where the table is busy and the IndexDefrag is
skipping the pages?
"Hilary Cotter" wrote:

> Have you compared the before and after results. Sometimes due to the way y
ou
> have constructed your table you will find residual fragmentation after
> running such scripts will be almost the same.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> news:AD82F542-DD91-4973-B0C1-8DAB523A7EF8@.microsoft.com...
>
>|||or maybe when there's not enough disk space to run DBCC Reindex on a large
table
"Hilary Cotter" wrote:

> Have you compared the before and after results. Sometimes due to the way y
ou
> have constructed your table you will find residual fragmentation after
> running such scripts will be almost the same.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> news:AD82F542-DD91-4973-B0C1-8DAB523A7EF8@.microsoft.com...
>
>

Wednesday, March 7, 2012

Define a hierarchy in sql script for SQL SERVER 2005

Good day to all!

I have a problem to resolve.

Which is the script in sql in order to define one hierarchy of products categories?

I have need to define the script for the creation of the table and store procedures (INSERT,DELETE,UPDATE,GET,GETALL).

Thanks for the eventual answers !

Search Google for Joe Celko's articles about hierarchies in SQL Server.

You ma yalso be interested in following links:

Joe Celko's book about hierarchies
Article about trees in SQL

|||

Xml is a suitable way to store hierarchies.

So you could define category hierarchies in xml and load it into database.

Hope this helps.

Saturday, February 25, 2012

Deferred Name Resolution gone wild.

I have a script that has a spelling error in the insert statement:
insert #tempTable(id, statux) values (1, 'this is a test')
This statement is deep in the script behind If and Case..when statements
and is never ever executed. There is a validation, which prevents this
code from executing, that hasn't been hit in a very long time.
That said, I recently ran this script on a sql server 2000 machine, and
the script failed with an error saying that 'statux' was an invalid
column. The odd thing is that the failure occured when the code
execution got close to the offending code statement. It's like SQL
Server decided to recompile the piece of code where the insert statement
is located.
I ran the same script on a different sql server 2000 and sql server 2005
machines and the script did not fail.
Of course, I fixed the misspelling, but I am curious about the deeper
issue of how and when Sql Server enforces Deferred Name Resolution (if
that is what causes the issue).
Is there a setting that controls this issue?
RegardsHello Frank,
I understand that you have some concerns about deferred name resolution.
When a stored procedure is created, the statements in the procedure are
parsed for syntactical accuracy. If a syntactical error is encountered in
the procedure definition, an error is returned and the stored procedure is
not created. If the statements are syntactically correct, the text of the
stored procedure is stored in the syscomments system table.
When a stored procedure is executed for the first time, the query processor
reads the text of the stored procedure from the syscomments system table of
the procedure and checks that the names of the objects used by the
procedure are present. This process is called deferred name resolution
because table objects referenced by the stored procedure need not exist
when the stored procedure is created, but only when it is executed. You may
want to refer to the following article for details:
http://msdn2.microsoft.com/en-us/library/aa214346(SQL.80).aspx
It seems that when the SQL is first executed the column is valid and the
compliation completed. However, when the exectuion plan is run again on
this statement, the error appears because the column is actually changed.
Please let's know if you have any further comments or questions. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||> That said, I recently ran this script on a sql server 2000 machine, and
> the script failed with an error saying that 'statux' was an invalid
> column.
To add to Perter's response, the column name will be validated only if the
table exists when the proc is created.
> I ran the same script on a different sql server 2000 and sql server 2005
> machines and the script did not fail.
My guess is that the temp table existed on only the one server. The script
below illustrates this.
CREATE TABLE #tempTable
(
id int NOT NULL,
status varchar(30) NOT NULL
)
GO
--this create will fail
CREATE PROC dbo.Test1
AS
CREATE TABLE #tempTable
(
id int NOT NULL,
status varchar(30) NOT NULL
)
INSERT #tempTable(id, statux)
VALUES (1, 'this is a test')
GO
DROP TABLE #tempTable
GO
--this create will succeed
CREATE PROC dbo.Test1
AS
CREATE TABLE #tempTable
(
id int NOT NULL,
status varchar(30) NOT NULL
)
INSERT #tempTable(id, statux)
VALUES (1, 'this is a test')
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:OAwRUA2ZHHA.984@.TK2MSFTNGP04.phx.gbl...
>I have a script that has a spelling error in the insert statement:
> insert #tempTable(id, statux) values (1, 'this is a test')
> This statement is deep in the script behind If and Case..when statements
> and is never ever executed. There is a validation, which prevents this
> code from executing, that hasn't been hit in a very long time.
> That said, I recently ran this script on a sql server 2000 machine, and
> the script failed with an error saying that 'statux' was an invalid
> column. The odd thing is that the failure occured when the code execution
> got close to the offending code statement. It's like SQL Server decided
> to recompile the piece of code where the insert statement is located.
> I ran the same script on a different sql server 2000 and sql server 2005
> machines and the script did not fail.
> Of course, I fixed the misspelling, but I am curious about the deeper
> issue of how and when Sql Server enforces Deferred Name Resolution (if
> that is what causes the issue).
> Is there a setting that controls this issue?
> Regards

Deferred Name Resolution gone wild.

I have a script that has a spelling error in the insert statement:
insert #tempTable(id, statux) values (1, 'this is a test')
This statement is deep in the script behind If and Case..when statements
and is never ever executed. There is a validation, which prevents this
code from executing, that hasn't been hit in a very long time.
That said, I recently ran this script on a sql server 2000 machine, and
the script failed with an error saying that 'statux' was an invalid
column. The odd thing is that the failure occured when the code
execution got close to the offending code statement. It's like SQL
Server decided to recompile the piece of code where the insert statement
is located.
I ran the same script on a different sql server 2000 and sql server 2005
machines and the script did not fail.
Of course, I fixed the misspelling, but I am curious about the deeper
issue of how and when Sql Server enforces Deferred Name Resolution (if
that is what causes the issue).
Is there a setting that controls this issue?
Regards
Hello Frank,
I understand that you have some concerns about deferred name resolution.
When a stored procedure is created, the statements in the procedure are
parsed for syntactical accuracy. If a syntactical error is encountered in
the procedure definition, an error is returned and the stored procedure is
not created. If the statements are syntactically correct, the text of the
stored procedure is stored in the syscomments system table.
When a stored procedure is executed for the first time, the query processor
reads the text of the stored procedure from the syscomments system table of
the procedure and checks that the names of the objects used by the
procedure are present. This process is called deferred name resolution
because table objects referenced by the stored procedure need not exist
when the stored procedure is created, but only when it is executed. You may
want to refer to the following article for details:
http://msdn2.microsoft.com/en-us/library/aa214346(SQL.80).aspx
It seems that when the SQL is first executed the column is valid and the
compliation completed. However, when the exectuion plan is run again on
this statement, the error appears because the column is actually changed.
Please let's know if you have any further comments or questions. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||> That said, I recently ran this script on a sql server 2000 machine, and
> the script failed with an error saying that 'statux' was an invalid
> column.
To add to Perter's response, the column name will be validated only if the
table exists when the proc is created.

> I ran the same script on a different sql server 2000 and sql server 2005
> machines and the script did not fail.
My guess is that the temp table existed on only the one server. The script
below illustrates this.
CREATE TABLE #tempTable
(
id int NOT NULL,
status varchar(30) NOT NULL
)
GO
--this create will fail
CREATE PROC dbo.Test1
AS
CREATE TABLE #tempTable
(
id int NOT NULL,
status varchar(30) NOT NULL
)
INSERT #tempTable(id, statux)
VALUES (1, 'this is a test')
GO
DROP TABLE #tempTable
GO
--this create will succeed
CREATE PROC dbo.Test1
AS
CREATE TABLE #tempTable
(
id int NOT NULL,
status varchar(30) NOT NULL
)
INSERT #tempTable(id, statux)
VALUES (1, 'this is a test')
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:OAwRUA2ZHHA.984@.TK2MSFTNGP04.phx.gbl...
>I have a script that has a spelling error in the insert statement:
> insert #tempTable(id, statux) values (1, 'this is a test')
> This statement is deep in the script behind If and Case..when statements
> and is never ever executed. There is a validation, which prevents this
> code from executing, that hasn't been hit in a very long time.
> That said, I recently ran this script on a sql server 2000 machine, and
> the script failed with an error saying that 'statux' was an invalid
> column. The odd thing is that the failure occured when the code execution
> got close to the offending code statement. It's like SQL Server decided
> to recompile the piece of code where the insert statement is located.
> I ran the same script on a different sql server 2000 and sql server 2005
> machines and the script did not fail.
> Of course, I fixed the misspelling, but I am curious about the deeper
> issue of how and when Sql Server enforces Deferred Name Resolution (if
> that is what causes the issue).
> Is there a setting that controls this issue?
> Regards

Deferred Name Resolution gone wild.

I have a script that has a spelling error in the insert statement:
insert #tempTable(id, statux) values (1, 'this is a test')
This statement is deep in the script behind If and Case..when statements
and is never ever executed. There is a validation, which prevents this
code from executing, that hasn't been hit in a very long time.
That said, I recently ran this script on a sql server 2000 machine, and
the script failed with an error saying that 'statux' was an invalid
column. The odd thing is that the failure occured when the code
execution got close to the offending code statement. It's like SQL
Server decided to recompile the piece of code where the insert statement
is located.
I ran the same script on a different sql server 2000 and sql server 2005
machines and the script did not fail.
Of course, I fixed the misspelling, but I am curious about the deeper
issue of how and when Sql Server enforces Deferred Name Resolution (if
that is what causes the issue).
Is there a setting that controls this issue?
RegardsHello Frank,
I understand that you have some concerns about deferred name resolution.
When a stored procedure is created, the statements in the procedure are
parsed for syntactical accuracy. If a syntactical error is encountered in
the procedure definition, an error is returned and the stored procedure is
not created. If the statements are syntactically correct, the text of the
stored procedure is stored in the syscomments system table.
When a stored procedure is executed for the first time, the query processor
reads the text of the stored procedure from the syscomments system table of
the procedure and checks that the names of the objects used by the
procedure are present. This process is called deferred name resolution
because table objects referenced by the stored procedure need not exist
when the stored procedure is created, but only when it is executed. You may
want to refer to the following article for details:
http://msdn2.microsoft.com/en-us/library/aa214346(SQL.80).aspx
It seems that when the SQL is first executed the column is valid and the
compliation completed. However, when the exectuion plan is run again on
this statement, the error appears because the column is actually changed.
Please let's know if you have any further comments or questions. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||> That said, I recently ran this script on a sql server 2000 machine, and
> the script failed with an error saying that 'statux' was an invalid
> column.
To add to Perter's response, the column name will be validated only if the
table exists when the proc is created.

> I ran the same script on a different sql server 2000 and sql server 2005
> machines and the script did not fail.
My guess is that the temp table existed on only the one server. The script
below illustrates this.
CREATE TABLE #tempTable
(
id int NOT NULL,
status varchar(30) NOT NULL
)
GO
--this create will fail
CREATE PROC dbo.Test1
AS
CREATE TABLE #tempTable
(
id int NOT NULL,
status varchar(30) NOT NULL
)
INSERT #tempTable(id, statux)
VALUES (1, 'this is a test')
GO
DROP TABLE #tempTable
GO
--this create will succeed
CREATE PROC dbo.Test1
AS
CREATE TABLE #tempTable
(
id int NOT NULL,
status varchar(30) NOT NULL
)
INSERT #tempTable(id, statux)
VALUES (1, 'this is a test')
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:OAwRUA2ZHHA.984@.TK2MSFTNGP04.phx.gbl...
>I have a script that has a spelling error in the insert statement:
> insert #tempTable(id, statux) values (1, 'this is a test')
> This statement is deep in the script behind If and Case..when statements
> and is never ever executed. There is a validation, which prevents this
> code from executing, that hasn't been hit in a very long time.
> That said, I recently ran this script on a sql server 2000 machine, and
> the script failed with an error saying that 'statux' was an invalid
> column. The odd thing is that the failure occured when the code execution
> got close to the offending code statement. It's like SQL Server decided
> to recompile the piece of code where the insert statement is located.
> I ran the same script on a different sql server 2000 and sql server 2005
> machines and the script did not fail.
> Of course, I fixed the misspelling, but I am curious about the deeper
> issue of how and when Sql Server enforces Deferred Name Resolution (if
> that is what causes the issue).
> Is there a setting that controls this issue?
> Regards

Friday, February 24, 2012

Default values

Is there a way to check to see if a default value exists for an existing
column in a table using SQL script? If so, how?http://www.aspfaq.com/2177
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Robert" <netsec4u@.hotmail.com> wrote in message
news:OHQx#K9eEHA.2804@.TK2MSFTNGP11.phx.gbl...
> Is there a way to check to see if a default value exists for an existing
> column in a table using SQL script? If so, how?
>|||SELECT column_default
FROM information_schema.columns
WHERE table_name = 'TABLE_NAME'
AND column_name = 'COLUMN_NAME'
--
David Portas
SQL Server MVP
--

Default values

Is there a way to check to see if a default value exists for an existing
column in a table using SQL script? If so, how?
http://www.aspfaq.com/2177
http://www.aspfaq.com/
(Reverse address to reply.)
"Robert" <netsec4u@.hotmail.com> wrote in message
news:OHQx#K9eEHA.2804@.TK2MSFTNGP11.phx.gbl...
> Is there a way to check to see if a default value exists for an existing
> column in a table using SQL script? If so, how?
>
|||SELECT column_default
FROM information_schema.columns
WHERE table_name = 'TABLE_NAME'
AND column_name = 'COLUMN_NAME'
David Portas
SQL Server MVP