renamed table [need to quickly update 200 SPs]

Hi
I have had to rename one of my tables (sp_Rename []) I now need to update
all my stored procedures, I want to some how use syscomments (maybe) and
loop through my SPs using REPLACE command to change the references to the
old table name
Any help / advise appreciated
LukeHi,
One solution is, you can generate a script with all the SPs available.
Open some text editor and use Replace All option giving your old and new
File Name.
Run the script again in Query Analyser
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Luke Ward" wrote:

> Hi
> I have had to rename one of my tables (sp_Rename []) I now need to update
> all my stored procedures, I want to some how use syscomments (maybe) and
> loop through my SPs using REPLACE command to change the references to the
> old table name
> Any help / advise appreciated
> Luke
>
>|||Don't fiddle with system tables ever! I would hope you have some
sort of code mgmt (ie VSS) in place where your master copies of
all procs are kept. That's where your changes should be made, tested
against a copy of your production data, and then applied to the
production database when you're satisfied with the results.
I think your best bet is to manually change the table name within
the procs. If you know a scripting language (like Perl), you could
write a script to blow through the procs and make the change and then
compile them back into the database.. That's what I would do.
"Luke Ward" <lukeward@.campbelluk.com> wrote in message
news:e6GC%23xsWFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Hi
> I have had to rename one of my tables (sp_Rename []) I now need to update
> all my stored procedures, I want to some how use syscomments (maybe) and
> loop through my SPs using REPLACE command to change the references to the
> old table name
> Any help / advise appreciated
> Luke
>

Rename tables from query analyzer

I have SQL 2k and I would like to know if I can rename a table from SQL
query analyzer.
Thanks
DimitrisEXEC sp_rename 'customers', 'custs'
HTH. Ryan
"Dimitris Nikolakakis" <dn@.hol.gr> wrote in message
news:OnIXVmMeGHA.3900@.TK2MSFTNGP05.phx.gbl...
>I have SQL 2k and I would like to know if I can rename a table from SQL
>query analyzer.
> Thanks
> Dimitris
>|||And how can this be done when in query analyzer the current DB is different
than the databe of table?
I have tried:
EXEC sp_rename '[FACOM].[dbo].[AddressBook]',
'[FACOM].[dbo].[_FACAddressBook]'
but I get error:
Error: The qualified @.oldname references a database (FACOM) other than the
current database.
"Ryan" <Ryan_Waight@.nospam.hotmail.com>
news:%23mHbkqMeGHA.4276@.TK2MSFTNGP03.phx.gbl...
> EXEC sp_rename 'customers', 'custs'
>
> --
> HTH. Ryan
>
> "Dimitris Nikolakakis" <dn@.hol.gr> wrote in message
> news:OnIXVmMeGHA.3900@.TK2MSFTNGP05.phx.gbl...
>|||>From BOL
You can change the name of an object or data type in the current
database only.
Thanks
Ajay Rengunthwar
MCDBA|||sp_rename works with objects in the current database only
To switch database :-
USE dbname
HTH. Ryan
"Dimitris Nikolakakis" <dn@.hol.gr> wrote in message
news:eWDOXvMeGHA.4900@.TK2MSFTNGP02.phx.gbl...
> And how can this be done when in query analyzer the current DB is
> different than the databe of table?
> I have tried:
>
> EXEC sp_rename '[FACOM].[dbo].[AddressBook]',
> '[FACOM].[dbo].[_FACAddressBook]'
> but I get error:
> Error: The qualified @.oldname references a database (FACOM) other than the
> current database.
>
> "Ryan" <Ryan_Waight@.nospam.hotmail.com>
> news:%23mHbkqMeGHA.4276@.TK2MSFTNGP03.phx.gbl...
>|||To add to the other responses, you can also execute sp_rename (and other
system procs) in the context of another database by qualifying with the
target database name:
EXEC FACOM..sp_rename '[dbo].[AddressBook]', '_FACAddressBook'
Also, specify *only* the new object name as the @.newname parameter value.
Do not specify the owner and do not enclose the name.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dimitris Nikolakakis" <dn@.hol.gr> wrote in message
news:eWDOXvMeGHA.4900@.TK2MSFTNGP02.phx.gbl...
> And how can this be done when in query analyzer the current DB is
> different than the databe of table?
> I have tried:
>
> EXEC sp_rename '[FACOM].[dbo].[AddressBook]',
> '[FACOM].[dbo].[_FACAddressBook]'
> but I get error:
> Error: The qualified @.oldname references a database (FACOM) other than the
> current database.
>
> "Ryan" <Ryan_Waight@.nospam.hotmail.com>
> news:%23mHbkqMeGHA.4276@.TK2MSFTNGP03.phx.gbl...
>

Rename tables from query analyzer

I have SQL 2k and I would like to know if I can rename a table from SQL
query analyzer.
Thanks
DimitrisEXEC sp_rename 'customers', 'custs'
HTH. Ryan
"Dimitris Nikolakakis" <dn@.hol.gr> wrote in message
news:OnIXVmMeGHA.3900@.TK2MSFTNGP05.phx.gbl...
>I have SQL 2k and I would like to know if I can rename a table from SQL
>query analyzer.
> Thanks
> Dimitris
>|||And how can this be done when in query analyzer the current DB is different
than the databe of table?
I have tried:
EXEC sp_rename '[FACOM].[dbo].[AddressBook]',
'[FACOM].[dbo].[_FACAddressBook]'
but I get error:
Error: The qualified @.oldname references a database (FACOM) other than the
current database.
Ï "Ryan" <Ryan_Waight@.nospam.hotmail.com> Ýãñáøå óôï ìÞíõìá
news:%23mHbkqMeGHA.4276@.TK2MSFTNGP03.phx.gbl...
> EXEC sp_rename 'customers', 'custs'
>
> --
> HTH. Ryan
>
> "Dimitris Nikolakakis" <dn@.hol.gr> wrote in message
> news:OnIXVmMeGHA.3900@.TK2MSFTNGP05.phx.gbl...
>>I have SQL 2k and I would like to know if I can rename a table from SQL
>>query analyzer.
>> Thanks
>> Dimitris
>|||>From BOL
You can change the name of an object or data type in the current
database only.
Thanks
Ajay Rengunthwar
MCDBA|||sp_rename works with objects in the current database only
To switch database :-
USE dbname
--
HTH. Ryan
"Dimitris Nikolakakis" <dn@.hol.gr> wrote in message
news:eWDOXvMeGHA.4900@.TK2MSFTNGP02.phx.gbl...
> And how can this be done when in query analyzer the current DB is
> different than the databe of table?
> I have tried:
>
> EXEC sp_rename '[FACOM].[dbo].[AddressBook]',
> '[FACOM].[dbo].[_FACAddressBook]'
> but I get error:
> Error: The qualified @.oldname references a database (FACOM) other than the
> current database.
>
> Ï "Ryan" <Ryan_Waight@.nospam.hotmail.com> Ýãñáøå óôï ìÞíõìá
> news:%23mHbkqMeGHA.4276@.TK2MSFTNGP03.phx.gbl...
>> EXEC sp_rename 'customers', 'custs'
>>
>> --
>> HTH. Ryan
>>
>> "Dimitris Nikolakakis" <dn@.hol.gr> wrote in message
>> news:OnIXVmMeGHA.3900@.TK2MSFTNGP05.phx.gbl...
>>I have SQL 2k and I would like to know if I can rename a table from SQL
>>query analyzer.
>> Thanks
>> Dimitris
>>
>|||To add to the other responses, you can also execute sp_rename (and other
system procs) in the context of another database by qualifying with the
target database name:
EXEC FACOM..sp_rename '[dbo].[AddressBook]', '_FACAddressBook'
Also, specify *only* the new object name as the @.newname parameter value.
Do not specify the owner and do not enclose the name.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dimitris Nikolakakis" <dn@.hol.gr> wrote in message
news:eWDOXvMeGHA.4900@.TK2MSFTNGP02.phx.gbl...
> And how can this be done when in query analyzer the current DB is
> different than the databe of table?
> I have tried:
>
> EXEC sp_rename '[FACOM].[dbo].[AddressBook]',
> '[FACOM].[dbo].[_FACAddressBook]'
> but I get error:
> Error: The qualified @.oldname references a database (FACOM) other than the
> current database.
>
> Ï "Ryan" <Ryan_Waight@.nospam.hotmail.com> Ýãñáøå óôï ìÞíõìá
> news:%23mHbkqMeGHA.4276@.TK2MSFTNGP03.phx.gbl...
>> EXEC sp_rename 'customers', 'custs'
>>
>> --
>> HTH. Ryan
>>
>> "Dimitris Nikolakakis" <dn@.hol.gr> wrote in message
>> news:OnIXVmMeGHA.3900@.TK2MSFTNGP05.phx.gbl...
>>I have SQL 2k and I would like to know if I can rename a table from SQL
>>query analyzer.
>> Thanks
>> Dimitris
>>
>

Rename tables and fields

I want to rename all tables and fields that starts with 'AAA_' to start with
'BBB_' instead. How can I do that?Check out "sp_rename" in the SQL Server books online.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"aEva" <aEva@.discussions.microsoft.com> wrote in message
news:35B7A3F3-459B-4B5E-8471-48668716D26B@.microsoft.com...
> I want to rename all tables and fields that starts with 'AAA_' to start
> with
> 'BBB_' instead. How can I do that?|||examnotes (aEva@.discussions.microsoft.com) writes:
> I want to rename all tables and fields that starts with 'AAA_' to start
> with 'BBB_' instead. How can I do that?
SELECT 'EXEC sp_rename ''' + name + ''', ''BBB_' +
substring(name, 4, len(name)) + ''''
FROM sysobjects
WHERE type = 'U'
AND name like 'AAA[_]%'
SELECT 'EXEC sp_rename ''' + o.name + '.' + c.name + ''', ''BBB_' +
substring(c.name, 4, len(c.name)) + '', ''column'''
FROM syobejcts o
JOIN syscolumns c ON o.id = c.id
WHERE c.name LIKE 'AAA[_]%'
AND o.type = 'U'
Cut and past result into query window and run.
Untested, so you may have weed out some syntax errors.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Rename MSDE table using SQL?

I am migrating a little VB6 app from Access to MSDE that
among other things has to rename some tables in a
database. That can be easily done with Enterprise
manager, but in my case has to be done programmatically
through ADO. I am wondering if this is possible, and if
so what is the syntax?
Take a look at sp_rename.
Mike
"s nikolsky" <anonymous@.discussions.microsoft.com> wrote in message
news:008c01c4aafd$480436a0$a401280a@.phx.gbl...
> I am migrating a little VB6 app from Access to MSDE that
> among other things has to rename some tables in a
> database. That can be easily done with Enterprise
> manager, but in my case has to be done programmatically
> through ADO. I am wondering if this is possible, and if
> so what is the syntax?

rename login (Possible OT)

I need to rename a login that owns some tables. I cannot do this via the
enterprise manager? Can I do it via SQL scripting?
Please advise (also if there is a better NG to post this to)
Thanks
Don't think so, butyou could create the new login and then change the owner
of all the object that the old login owns using sp_changeobjectowner and/or
sp_changedbowner (if they own a database). Doing this would accomplish the
rename.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Matt Pollicove" <matt@.maxware.com> wrote in message
news:e0tH9msLEHA.3596@.tk2msftngp13.phx.gbl...
> I need to rename a login that owns some tables. I cannot do this via the
> enterprise manager? Can I do it via SQL scripting?
> Please advise (also if there is a better NG to post this to)
> Thanks
>
|||Hi,
There is no commands in sql server to rename a Login. The better
recommendation is to set a strong password
which can not be guessed by any one.
Otherwise create a new login/user and assign the object owner to that new
user using sp_changeobjectowner procedure
and drop the existing user.
Thanks
Hari
MCDBA
"Matt Pollicove" <matt@.maxware.com> wrote in message
news:e0tH9msLEHA.3596@.tk2msftngp13.phx.gbl...
> I need to rename a login that owns some tables. I cannot do this via the
> enterprise manager? Can I do it via SQL scripting?
> Please advise (also if there is a better NG to post this to)
> Thanks
>
|||Matt,
Is this a SQL Server login or a domain login? (I assume SQL Server.) There
is not a way to do this directly (although you can monkey with system tables
this is to be avoided).
Suggestion:
1. Create a login with the new name.
2. Give it the same rights the old login had.
3. Use sp_changeobjectowner for all objects affected to point them to the
new owner.
4. Drop the old login.
Russell Fields
"Matt Pollicove" <matt@.maxware.com> wrote in message
news:e0tH9msLEHA.3596@.tk2msftngp13.phx.gbl...
> I need to rename a login that owns some tables. I cannot do this via the
> enterprise manager? Can I do it via SQL scripting?
> Please advise (also if there is a better NG to post this to)
> Thanks
>
|||Thanks.
I have to reset the user not because of password issues but because the
database was moved without detaching it first and the username was held in
the db but not the login name.
thanks for all your help, I will try this method.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:OmTBpusLEHA.3216@.tk2msftngp13.phx.gbl...
> Matt,
> Is this a SQL Server login or a domain login? (I assume SQL Server.)
There
> is not a way to do this directly (although you can monkey with system
tables[vbcol=seagreen]
> this is to be avoided).
> Suggestion:
> 1. Create a login with the new name.
> 2. Give it the same rights the old login had.
> 3. Use sp_changeobjectowner for all objects affected to point them to the
> new owner.
> 4. Drop the old login.
> Russell Fields
> "Matt Pollicove" <matt@.maxware.com> wrote in message
> news:e0tH9msLEHA.3596@.tk2msftngp13.phx.gbl...
the
>

rename login (Possible OT)

I need to rename a login that owns some tables. I cannot do this via the
enterprise manager? Can I do it via SQL scripting?
Please advise (also if there is a better NG to post this to)
ThanksDon't think so, butyou could create the new login and then change the owner
of all the object that the old login owns using sp_changeobjectowner and/or
sp_changedbowner (if they own a database). Doing this would accomplish the
rename.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Matt Pollicove" <matt@.maxware.com> wrote in message
news:e0tH9msLEHA.3596@.tk2msftngp13.phx.gbl...
> I need to rename a login that owns some tables. I cannot do this via the
> enterprise manager? Can I do it via SQL scripting?
> Please advise (also if there is a better NG to post this to)
> Thanks
>|||Hi,
There is no commands in sql server to rename a Login. The better
recommendation is to set a strong password
which can not be guessed by any one.
Otherwise create a new login/user and assign the object owner to that new
user using sp_changeobjectowner procedure
and drop the existing user.
Thanks
Hari
MCDBA
"Matt Pollicove" <matt@.maxware.com> wrote in message
news:e0tH9msLEHA.3596@.tk2msftngp13.phx.gbl...
> I need to rename a login that owns some tables. I cannot do this via the
> enterprise manager? Can I do it via SQL scripting?
> Please advise (also if there is a better NG to post this to)
> Thanks
>|||Matt,
Is this a SQL Server login or a domain login? (I assume SQL Server.) There
is not a way to do this directly (although you can monkey with system tables
this is to be avoided).
Suggestion:
1. Create a login with the new name.
2. Give it the same rights the old login had.
3. Use sp_changeobjectowner for all objects affected to point them to the
new owner.
4. Drop the old login.
Russell Fields
"Matt Pollicove" <matt@.maxware.com> wrote in message
news:e0tH9msLEHA.3596@.tk2msftngp13.phx.gbl...
> I need to rename a login that owns some tables. I cannot do this via the
> enterprise manager? Can I do it via SQL scripting?
> Please advise (also if there is a better NG to post this to)
> Thanks
>|||Thanks.
I have to reset the user not because of password issues but because the
database was moved without detaching it first and the username was held in
the db but not the login name.
thanks for all your help, I will try this method.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:OmTBpusLEHA.3216@.tk2msftngp13.phx.gbl...
> Matt,
> Is this a SQL Server login or a domain login? (I assume SQL Server.)
There
> is not a way to do this directly (although you can monkey with system
tables
> this is to be avoided).
> Suggestion:
> 1. Create a login with the new name.
> 2. Give it the same rights the old login had.
> 3. Use sp_changeobjectowner for all objects affected to point them to the
> new owner.
> 4. Drop the old login.
> Russell Fields
> "Matt Pollicove" <matt@.maxware.com> wrote in message
> news:e0tH9msLEHA.3596@.tk2msftngp13.phx.gbl...
> > I need to rename a login that owns some tables. I cannot do this via
the
> > enterprise manager? Can I do it via SQL scripting?
> >
> > Please advise (also if there is a better NG to post this to)
> >
> > Thanks
> >
> >
>

rename login (Possible OT)

I need to rename a login that owns some tables. I cannot do this via the
enterprise manager? Can I do it via SQL scripting?
Please advise (also if there is a better NG to post this to)
ThanksDon't think so, butyou could create the new login and then change the owner
of all the object that the old login owns using sp_changeobjectowner and/or
sp_changedbowner (if they own a database). Doing this would accomplish the
rename.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Matt Pollicove" <matt@.maxware.com> wrote in message
news:e0tH9msLEHA.3596@.tk2msftngp13.phx.gbl...
> I need to rename a login that owns some tables. I cannot do this via the
> enterprise manager? Can I do it via SQL scripting?
> Please advise (also if there is a better NG to post this to)
> Thanks
>|||Hi,
There is no commands in sql server to rename a Login. The better
recommendation is to set a strong password
which can not be guessed by any one.
Otherwise create a new login/user and assign the object owner to that new
user using sp_changeobjectowner procedure
and drop the existing user.
Thanks
Hari
MCDBA
"Matt Pollicove" <matt@.maxware.com> wrote in message
news:e0tH9msLEHA.3596@.tk2msftngp13.phx.gbl...
> I need to rename a login that owns some tables. I cannot do this via the
> enterprise manager? Can I do it via SQL scripting?
> Please advise (also if there is a better NG to post this to)
> Thanks
>|||Matt,
Is this a SQL Server login or a domain login? (I assume SQL Server.) There
is not a way to do this directly (although you can monkey with system tables
this is to be avoided).
Suggestion:
1. Create a login with the new name.
2. Give it the same rights the old login had.
3. Use sp_changeobjectowner for all objects affected to point them to the
new owner.
4. Drop the old login.
Russell Fields
"Matt Pollicove" <matt@.maxware.com> wrote in message
news:e0tH9msLEHA.3596@.tk2msftngp13.phx.gbl...
> I need to rename a login that owns some tables. I cannot do this via the
> enterprise manager? Can I do it via SQL scripting?
> Please advise (also if there is a better NG to post this to)
> Thanks
>|||Thanks.
I have to reset the user not because of password issues but because the
database was moved without detaching it first and the username was held in
the db but not the login name.
thanks for all your help, I will try this method.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:OmTBpusLEHA.3216@.tk2msftngp13.phx.gbl...
> Matt,
> Is this a SQL Server login or a domain login? (I assume SQL Server.)
There
> is not a way to do this directly (although you can monkey with system
tables
> this is to be avoided).
> Suggestion:
> 1. Create a login with the new name.
> 2. Give it the same rights the old login had.
> 3. Use sp_changeobjectowner for all objects affected to point them to the
> new owner.
> 4. Drop the old login.
> Russell Fields
> "Matt Pollicove" <matt@.maxware.com> wrote in message
> news:e0tH9msLEHA.3596@.tk2msftngp13.phx.gbl...
the[vbcol=seagreen]
>

Re-name a Column in an Anonymous Merge Publication

Upsized an Access .MDB and Published successfully, EXCEPT forgot to make
visible the sysobjects, so two Tables each have four Columns named: Expr1,
Expr2, Expr3, Expr4. When I try to rename them with SSEM in the SQL Database,
I get errors like this:
'tblNoteAdmit' table
- Unable to rename column from 'Expr1' to 'Pump'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot rename the
table because it is published for replication.
It looks like time to Create a Script, that the Snapshot Agent can
understand ...
Sometime back, I blundered through that process to Add a Table
(Successfully) but am having trouble finding the SP_ to trick Column Names.
Aubrey Kelley
How do I Rename a Column in an Active Replicated Database?
24 hr update: Got this far: (SP is sp_rename, but still blocked by
Replication.)
USE CareData
GO
EXEC sp_rename 'tblNoteAdmit.Expr1', 'Pump', 'COLUMN'
Server: Msg 15051, Level 11, State 1, Procedure sp_rename, Line 172
Cannot rename the table because it is published for replication.
Aubrey Kelley
"Aubrey" wrote:

> Upsized an Access .MDB and Published successfully, EXCEPT forgot to make
> visible the sysobjects, so two Tables each have four Columns named: Expr1,
> Expr2, Expr3, Expr4. When I try to rename them with SSEM in the SQL Database,
> I get errors like this:
> 'tblNoteAdmit' table
> - Unable to rename column from 'Expr1' to 'Pump'.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot rename the
> table because it is published for replication.
> It looks like time to Create a Script, that the Snapshot Agent can
> understand ...
> Sometime back, I blundered through that process to Add a Table
> (Successfully) but am having trouble finding the SP_ to trick Column Names.
> --
> Aubrey Kelley
|||You will have to do sp_repladdcolumn to add a dummy column with the same
data type as the column you wish to rename. Then update this temp column
with the values in the column you are wishing to rename. Then drop the
column you wish to rename using sp_repldropcolumn. Then readd the column
with the new name using sp_repladdcolumn. Then update this column with the
values in the temp column. Then drop the temp column using
sp_repldropcolumn.
Alternatively you may wish to create a temp table with the values in the
column you wish to rename along with the PK. Then drop the column you wish
to rename using sp_repldropcolumn, and then add it back with the new name
using sp_repladdcolumn. Update this new column with the values in the temp
table.
Hilary Cotter
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
"Aubrey" <miscuates@.online.nospam> wrote in message
news:51D04366-E78F-463C-A7C0-17E2CE461867@.microsoft.com...[vbcol=seagreen]
> How do I Rename a Column in an Active Replicated Database?
> 24 hr update: Got this far: (SP is sp_rename, but still blocked by
> Replication.)
> USE CareData
> GO
> EXEC sp_rename 'tblNoteAdmit.Expr1', 'Pump', 'COLUMN'
> Server: Msg 15051, Level 11, State 1, Procedure sp_rename, Line 172
> Cannot rename the table because it is published for replication.
> --
> Aubrey Kelley
>
> "Aubrey" wrote:
Expr1,[vbcol=seagreen]
Database,[vbcol=seagreen]
the[vbcol=seagreen]
Names.[vbcol=seagreen]
|||Thanks, Hilary! and only 43 minutes after my updated post, Sunday early AM?
That was what it looked like in your SS2K T&SR Book. Was not sure it applied
to Anonymous Merge. Will try it Real-Soon-Now ...
Aubrey Kelley
"Hilary Cotter" wrote:

> You will have to do sp_repladdcolumn to add a dummy column with the same
> data type as the column you wish to rename. Then update this temp column
> with the values in the column you are wishing to rename. Then drop the
> column you wish to rename using sp_repldropcolumn. Then readd the column
> with the new name using sp_repladdcolumn. Then update this column with the
> values in the temp column. Then drop the temp column using
> sp_repldropcolumn.
> Alternatively you may wish to create a temp table with the values in the
> column you wish to rename along with the PK. Then drop the column you wish
> to rename using sp_repldropcolumn, and then add it back with the new name
> using sp_repladdcolumn. Update this new column with the values in the temp
> table.
> --
> Hilary Cotter
> 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
> "Aubrey" <miscuates@.online.nospam> wrote in message
> news:51D04366-E78F-463C-A7C0-17E2CE461867@.microsoft.com...
> Expr1,
> Database,
> the
> Names.
>
>
|||What I did: Opened Query Analyzer
USE CareData
GO
EXEC sp_repladdcolumn N'Orders', N'IsSaved', 'bit', N'CareData'
[RUN]
Results:
Warning: only Subscribers running SQL Server 2000 can synchronize with
publication 'CareData' because schema replication is performed.
Cannot add rows to sysdepends for the current stored procedure because it
depends on the missing object 'sp_sel_B469A5B2466148AC3170B37FE7C24426_pal'.
The stored procedure will still be created.
Updated Values and Tested from Remote Client. Voila! Worked like a charm.
Why did you suggest a Temp column? Seemed superfluous to Copy from OldColumn
to Temp, then copy Temp to NewColumn, when OldColumn directly to NewColumn
works great.
Aubrey Kelley
"Aubrey" wrote:
[vbcol=seagreen]
> Thanks, Hilary! and only 43 minutes after my updated post, Sunday early AM?
> That was what it looked like in your SS2K T&SR Book. Was not sure it applied
> to Anonymous Merge. Will try it Real-Soon-Now ...
> --
> Aubrey Kelley
>
> "Hilary Cotter" wrote:
|||I'm glad it worked.
The temp table solution works great if you are able to kick your users off
while you do it. If not and the table was huge, the temp table could take
come time to populate and will get progressively out of sync if people are
banging away at it. So you would loose consistency as you do it.
However, my approach will "break" the table as you are making the changes so
the app which is using this column will fail as you do your renaming.
So neither solutions are perfect - mine takes longer but might guarantee
better consistency than yours. Your method is shorter, but your method is
preferred if you can kick all of your users off your system.
Hilary Cotter
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
"Aubrey" <miscuates@.online.nospam> wrote in message
news:A9C71B7B-F1AF-4DAD-A202-6CF3DD2703FB@.microsoft.com...
> What I did: Opened Query Analyzer
> USE CareData
> GO
> EXEC sp_repladdcolumn N'Orders', N'IsSaved', 'bit', N'CareData'
> [RUN]
> Results:
> Warning: only Subscribers running SQL Server 2000 can synchronize with
> publication 'CareData' because schema replication is performed.
> Cannot add rows to sysdepends for the current stored procedure because it
> depends on the missing object
'sp_sel_B469A5B2466148AC3170B37FE7C24426_pal'.
> The stored procedure will still be created.
> Updated Values and Tested from Remote Client. Voila! Worked like a charm.
> Why did you suggest a Temp column? Seemed superfluous to Copy from
OldColumn[vbcol=seagreen]
> to Temp, then copy Temp to NewColumn, when OldColumn directly to NewColumn
> works great.
> --
> Aubrey Kelley
>
> "Aubrey" wrote:
AM?[vbcol=seagreen]
applied[vbcol=seagreen]
same[vbcol=seagreen]
column[vbcol=seagreen]
column[vbcol=seagreen]
the[vbcol=seagreen]
the[vbcol=seagreen]
wish[vbcol=seagreen]
name[vbcol=seagreen]
temp[vbcol=seagreen]
to make[vbcol=seagreen]
named:[vbcol=seagreen]
SQL[vbcol=seagreen]
rename[vbcol=seagreen]
Column[vbcol=seagreen]
|||Correct; I was fortunate that ALL Users are Merge Clients, No ONE is directly
updating the Publisher Database.
Just to Test, I added a couple of Rows while all this was running, and they
were missing the Values in the New Columns. Fortunately I had WHERE Clauses
in the Data Copy Form, Event Procedures just in case the Project Locked Up or
Aborted.
Now, onward and upward ... Have a Great Week!
Aubrey Kelley
"Hilary Cotter" wrote:

> I'm glad it worked.
> The temp table solution works great if you are able to kick your users off
> while you do it. If not and the table was huge, the temp table could take
> come time to populate and will get progressively out of sync if people are
> banging away at it. So you would loose consistency as you do it.
> However, my approach will "break" the table as you are making the changes so
> the app which is using this column will fail as you do your renaming.
> So neither solutions are perfect - mine takes longer but might guarantee
> better consistency than yours. Your method is shorter, but your method is
> preferred if you can kick all of your users off your system.
> --
> Hilary Cotter
> 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
> "Aubrey" <miscuates@.online.nospam> wrote in message
> news:A9C71B7B-F1AF-4DAD-A202-6CF3DD2703FB@.microsoft.com...
> 'sp_sel_B469A5B2466148AC3170B37FE7C24426_pal'.
> OldColumn
> AM?
> applied
> same
> column
> column
> the
> the
> wish
> name
> temp
> to make
> named:
> SQL
> rename
> Column
>
>

Removing system replication tables.

Hi, i restored a backup from a database thas has replication configured.
When i restored it the system tables that the merge replication creates
are restored too. I was investigating on internet and I found that i can
delete it using this query:
sp_configure 'allow updates', 1
go
reconfigure with override
go
DROP TABLE aonflict_SiacDataEEC_security_info
...
sp_configure 'allow updates', 0
go
reconfigure with override
go
Do somebody know if i use this queries to delete this tables i can
damage the database or is correct to use it.
Thanks a lot for your help.
*** Sent via Developersdex http://www.codecomments.com ***
Maria,
most system metadata is removed by sp_removedbreplication, but this is an
exception and it is common practice to drop these tables manually as part of
a cleanup.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||If the database is no longer replicating then it will not damage the
database to delete the old replication objects.
In fact, if you do not delete the replication triggers you will
probably encounter problems.
|||I agree. sp_MSdroparticletriggers is called from sp_removedbreplication but
has been known to not work in rare circumstances. I have a script which
removed redundant duplicate triggers that may be of interest in these cases:
http://www.replicationanswers.com/Script8.asp
HTH
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Removing system replication tables.

Hi, i restored a backup from a database thas has replication configured.
When i restored it the system tables that the merge replication creates
are restored too. I was investigating on internet and I found that i can
delete it using this query:

sp_configure 'allow updates', 1
go
reconfigure with override
go

DROP TABLE aonflict_SiacDataEEC_security_info
...

sp_configure 'allow updates', 0
go
reconfigure with override
go

Do somebody know if i use this queries to delete this tables i can
damage the database or is correct to use it.

Thanks a lot for your help.

*** Sent via Developersdex http://www.developersdex.com ***You might get a better response in
microsoft.public.sqlserver.replication

Simon

Removing Old/Unused Stored Procs....

Hello Friends,
Situation:
I have been charged with cleaning up a large legacy database.
During this process I have identified tables no longer used and removed them
from the schema.
Question:
I now have may stored procs that reference these tables and are no longer
needed.
How can I identify the procs and remove them programmatically?
Do I need to recompile each one and look for errors?
Any help at all would be most welcome.
Thanks in advance,
B.Hi
Use the sysobjects, sysdepends tables to identify all the procedures using
the given tables.Build a query and from the resultset u know hat objects nee
d
to be dropped.
Imtiaz
"Tam O'Shanter" wrote:

> Hello Friends,
> Situation:
> I have been charged with cleaning up a large legacy database.
> During this process I have identified tables no longer used and removed th
em
> from the schema.
> Question:
> I now have may stored procs that reference these tables and are no longer
> needed.
> How can I identify the procs and remove them programmatically?
> Do I need to recompile each one and look for errors?
> Any help at all would be most welcome.
>
> Thanks in advance,
> B.
>
>|||Cool idea.
However, what if the tables that have been removed are unknown?
Thanks in advance for any tips.
B.
"Imtiaz" <Imtiaz@.discussions.microsoft.com> wrote in message
news:9BE5007C-30BD-465D-A9E7-C54EB5158CE9@.microsoft.com...
> Hi
> Use the sysobjects, sysdepends tables to identify all the procedures using
> the given tables.Build a query and from the resultset u know hat objects
need[vbcol=seagreen]
> to be dropped.
> Imtiaz
> "Tam O'Shanter" wrote:
>
them[vbcol=seagreen]
longer[vbcol=seagreen]|||Tam O'Shanter wrote:[vbcol=seagreen]
> Cool idea.
> However, what if the tables that have been removed are unknown?
> Thanks in advance for any tips.
> B.
> "Imtiaz" <Imtiaz@.discussions.microsoft.com> wrote in message
> news:9BE5007C-30BD-465D-A9E7-C54EB5158CE9@.microsoft.com...
I was going to suggest you try and recompile all your procedures and see
which ones fail because of missing table references, but that does not
seem to work:
create table whatever (col1 int)
go
create proc whateverproc
as
select * from whatever
go
sp_depends whateverproc
go
drop table whatever
go
sp_depends whateverproc
go
drop proc whateverproc
go
If you have a list of tables you dropped, you could search syscomments
table in the database for the table names using:
Select object_name(id)
from syscomments
where text like N'%<table_name>%'
However, if your procedures are encrypted that won't work either.
--
David G.|||If you have a list of tables that you removed, then you can find out
all the stored procedures which are using the tables that were
removed.
Do a
select * from syscomments where text like '%tablename%'
this will give you a list of all the objects which were using the
table that you deleted and hence all this objects should be invalid
and hence you can delete them.
Thank You
-Pranay
"Tam O'Shanter" <Tam@.Oshanter.com> wrote in message news:<SPsWc.22207195$Id.3687174@.news.eas
ynews.com>...
> Hello Friends,
> Situation:
> I have been charged with cleaning up a large legacy database.
> During this process I have identified tables no longer used and removed th
em
> from the schema.
> Question:
> I now have may stored procs that reference these tables and are no longer
> needed.
> How can I identify the procs and remove them programmatically?
> Do I need to recompile each one and look for errors?
> Any help at all would be most welcome.
>
> Thanks in advance,
> B.

Removing Old/Unused Stored Procs....

Hello Friends,
Situation:
I have been charged with cleaning up a large legacy database.
During this process I have identified tables no longer used and removed them
from the schema.
Question:
I now have may stored procs that reference these tables and are no longer
needed.
How can I identify the procs and remove them programmatically?
Do I need to recompile each one and look for errors?
Any help at all would be most welcome.
Thanks in advance,
B.
Hi
Use the sysobjects, sysdepends tables to identify all the procedures using
the given tables.Build a query and from the resultset u know hat objects need
to be dropped.
Imtiaz
"Tam O'Shanter" wrote:

> Hello Friends,
> Situation:
> I have been charged with cleaning up a large legacy database.
> During this process I have identified tables no longer used and removed them
> from the schema.
> Question:
> I now have may stored procs that reference these tables and are no longer
> needed.
> How can I identify the procs and remove them programmatically?
> Do I need to recompile each one and look for errors?
> Any help at all would be most welcome.
>
> Thanks in advance,
> B.
>
>
|||Cool idea.
However, what if the tables that have been removed are unknown?
Thanks in advance for any tips.
B.
"Imtiaz" <Imtiaz@.discussions.microsoft.com> wrote in message
news:9BE5007C-30BD-465D-A9E7-C54EB5158CE9@.microsoft.com...
> Hi
> Use the sysobjects, sysdepends tables to identify all the procedures using
> the given tables.Build a query and from the resultset u know hat objects
need[vbcol=seagreen]
> to be dropped.
> Imtiaz
> "Tam O'Shanter" wrote:
them[vbcol=seagreen]
longer[vbcol=seagreen]
|||Tam O'Shanter wrote:[vbcol=seagreen]
> Cool idea.
> However, what if the tables that have been removed are unknown?
> Thanks in advance for any tips.
> B.
> "Imtiaz" <Imtiaz@.discussions.microsoft.com> wrote in message
> news:9BE5007C-30BD-465D-A9E7-C54EB5158CE9@.microsoft.com...
I was going to suggest you try and recompile all your procedures and see
which ones fail because of missing table references, but that does not
seem to work:
create table whatever (col1 int)
go
create proc whateverproc
as
select * from whatever
go
sp_depends whateverproc
go
drop table whatever
go
sp_depends whateverproc
go
drop proc whateverproc
go
If you have a list of tables you dropped, you could search syscomments
table in the database for the table names using:
Select object_name(id)
from syscomments
where text like N'%<table_name>%'
However, if your procedures are encrypted that won't work either.
David G.
|||If you have a list of tables that you removed, then you can find out
all the stored procedures which are using the tables that were
removed.
Do a
select * from syscomments where text like '%tablename%'
this will give you a list of all the objects which were using the
table that you deleted and hence all this objects should be invalid
and hence you can delete them.
Thank You
-Pranay
"Tam O'Shanter" <Tam@.Oshanter.com> wrote in message news:<SPsWc.22207195$Id.3687174@.news.easynews.com> ...
> Hello Friends,
> Situation:
> I have been charged with cleaning up a large legacy database.
> During this process I have identified tables no longer used and removed them
> from the schema.
> Question:
> I now have may stored procs that reference these tables and are no longer
> needed.
> How can I identify the procs and remove them programmatically?
> Do I need to recompile each one and look for errors?
> Any help at all would be most welcome.
>
> Thanks in advance,
> B.

Removing Old/Unused Stored Procs....

Hello Friends,
Situation:
I have been charged with cleaning up a large legacy database.
During this process I have identified tables no longer used and removed them
from the schema.
Question:
I now have may stored procs that reference these tables and are no longer
needed.
How can I identify the procs and remove them programmatically?
Do I need to recompile each one and look for errors?
Any help at all would be most welcome.
Thanks in advance,
B.Cool idea.
However, what if the tables that have been removed are unknown?
Thanks in advance for any tips.
B.
"Imtiaz" <Imtiaz@.discussions.microsoft.com> wrote in message
news:9BE5007C-30BD-465D-A9E7-C54EB5158CE9@.microsoft.com...
> Hi
> Use the sysobjects, sysdepends tables to identify all the procedures using
> the given tables.Build a query and from the resultset u know hat objects
need
> to be dropped.
> Imtiaz
> "Tam O'Shanter" wrote:
> > Hello Friends,
> >
> > Situation:
> >
> > I have been charged with cleaning up a large legacy database.
> > During this process I have identified tables no longer used and removed
them
> > from the schema.
> >
> > Question:
> >
> > I now have may stored procs that reference these tables and are no
longer
> > needed.
> >
> > How can I identify the procs and remove them programmatically?
> >
> > Do I need to recompile each one and look for errors?
> >
> > Any help at all would be most welcome.
> >
> >
> > Thanks in advance,
> >
> > B.
> >
> >
> >|||Tam O'Shanter wrote:
> Cool idea.
> However, what if the tables that have been removed are unknown?
> Thanks in advance for any tips.
> B.
> "Imtiaz" <Imtiaz@.discussions.microsoft.com> wrote in message
> news:9BE5007C-30BD-465D-A9E7-C54EB5158CE9@.microsoft.com...
>> Hi
>> Use the sysobjects, sysdepends tables to identify all the procedures
>> using the given tables.Build a query and from the resultset u know
>> hat objects need to be dropped.
>> Imtiaz
>> "Tam O'Shanter" wrote:
>> Hello Friends,
>> Situation:
>> I have been charged with cleaning up a large legacy database.
>> During this process I have identified tables no longer used and
>> removed them from the schema.
>> Question:
>> I now have may stored procs that reference these tables and are no
>> longer needed.
>> How can I identify the procs and remove them programmatically?
>> Do I need to recompile each one and look for errors?
>> Any help at all would be most welcome.
>>
>> Thanks in advance,
>> B.
I was going to suggest you try and recompile all your procedures and see
which ones fail because of missing table references, but that does not
seem to work:
create table whatever (col1 int)
go
create proc whateverproc
as
select * from whatever
go
sp_depends whateverproc
go
drop table whatever
go
sp_depends whateverproc
go
drop proc whateverproc
go
If you have a list of tables you dropped, you could search syscomments
table in the database for the table names using:
Select object_name(id)
from syscomments
where text like N'%<table_name>%'
However, if your procedures are encrypted that won't work either.
--
David G.|||If you have a list of tables that you removed, then you can find out
all the stored procedures which are using the tables that were
removed.
Do a
select * from syscomments where text like '%tablename%'
this will give you a list of all the objects which were using the
table that you deleted and hence all this objects should be invalid
and hence you can delete them.
Thank You
-Pranay
"Tam O'Shanter" <Tam@.Oshanter.com> wrote in message news:<SPsWc.22207195$Id.3687174@.news.easynews.com>...
> Hello Friends,
> Situation:
> I have been charged with cleaning up a large legacy database.
> During this process I have identified tables no longer used and removed them
> from the schema.
> Question:
> I now have may stored procs that reference these tables and are no longer
> needed.
> How can I identify the procs and remove them programmatically?
> Do I need to recompile each one and look for errors?
> Any help at all would be most welcome.
>
> Thanks in advance,
> B.

Removing Identity Property

Folks,
I am trying to alter my database thru SQL Scripts. I have a few tables where
I have columns having Identity Property. I now want to modify these columns
having no Identity Property. But I can't find SQL Statement that can do
this.
Can some one help?
Regards
Shailaindra SharmaYou cannot remove this property... you must add another column, move the id
info to the new column, drop the old column, and rename the new column...
Or do the same thing at the table level... However,
If you go into SEM, select the table , right click and go into design, you
can simply select the column and uncheck Identity at the bottom... SEM will
generate a script to do this work for you - if there are lots of rows it may
take a while.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Shailaindra Sharma" <shailaindra@.abosoftware.com> wrote in message
news:eIHMP%23pZFHA.3840@.tk2msftngp13.phx.gbl...
> Folks,
> I am trying to alter my database thru SQL Scripts. I have a few tables
where
> I have columns having Identity Property. I now want to modify these
columns
> having no Identity Property. But I can't find SQL Statement that can do
> this.
> Can some one help?
> Regards
> Shailaindra Sharma
>|||Why can't we do things like that which we can easily do in access. How hard
can it be for ms to add these things which will make life so easy.
Regards
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:ewlf9KqZFHA.3048@.TK2MSFTNGP14.phx.gbl...
> You cannot remove this property... you must add another column, move the
> id
> info to the new column, drop the old column, and rename the new column...
> Or do the same thing at the table level... However,
> If you go into SEM, select the table , right click and go into design, you
> can simply select the column and uncheck Identity at the bottom... SEM
> will
> generate a script to do this work for you - if there are lots of rows it
> may
> take a while.
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "Shailaindra Sharma" <shailaindra@.abosoftware.com> wrote in message
> news:eIHMP%23pZFHA.3840@.tk2msftngp13.phx.gbl...
>> Folks,
>> I am trying to alter my database thru SQL Scripts. I have a few tables
> where
>> I have columns having Identity Property. I now want to modify these
> columns
>> having no Identity Property. But I can't find SQL Statement that can do
>> this.
>> Can some one help?
>> Regards
>> Shailaindra Sharma
>>
>|||Consider proposing this:
sqlwish@.microsoft.com
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John" <John@.nospam.infovis.co.uk> wrote in message news:%23iv4mYrZFHA.2664@.TK2MSFTNGP15.phx.gbl...
> Why can't we do things like that which we can easily do in access. How hard
> can it be for ms to add these things which will make life so easy.
> Regards
>|||You mean no one has found it to be a problem as yet? :)
Regards
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:esZQgZsZFHA.3596@.tk2msftngp13.phx.gbl...
> Consider proposing this:
> sqlwish@.microsoft.com
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:%23iv4mYrZFHA.2664@.TK2MSFTNGP15.phx.gbl...
>> Why can't we do things like that which we can easily do in access. How
>> hard can it be for ms to add these things which will make life so easy.
>> Regards

Removing Identity Property

Folks,
I am trying to alter my database thru SQL Scripts. I have a few tables where
I have columns having Identity Property. I now want to modify these columns
having no Identity Property. But I can't find SQL Statement that can do
this.
Can some one help?
Regards
Shailaindra Sharma
You cannot remove this property... you must add another column, move the id
info to the new column, drop the old column, and rename the new column...
Or do the same thing at the table level... However,
If you go into SEM, select the table , right click and go into design, you
can simply select the column and uncheck Identity at the bottom... SEM will
generate a script to do this work for you - if there are lots of rows it may
take a while.
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Shailaindra Sharma" <shailaindra@.abosoftware.com> wrote in message
news:eIHMP%23pZFHA.3840@.tk2msftngp13.phx.gbl...
> Folks,
> I am trying to alter my database thru SQL Scripts. I have a few tables
where
> I have columns having Identity Property. I now want to modify these
columns
> having no Identity Property. But I can't find SQL Statement that can do
> this.
> Can some one help?
> Regards
> Shailaindra Sharma
>
|||Why can't we do things like that which we can easily do in access. How hard
can it be for ms to add these things which will make life so easy.
Regards
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:ewlf9KqZFHA.3048@.TK2MSFTNGP14.phx.gbl...
> You cannot remove this property... you must add another column, move the
> id
> info to the new column, drop the old column, and rename the new column...
> Or do the same thing at the table level... However,
> If you go into SEM, select the table , right click and go into design, you
> can simply select the column and uncheck Identity at the bottom... SEM
> will
> generate a script to do this work for you - if there are lots of rows it
> may
> take a while.
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "Shailaindra Sharma" <shailaindra@.abosoftware.com> wrote in message
> news:eIHMP%23pZFHA.3840@.tk2msftngp13.phx.gbl...
> where
> columns
>
|||Consider proposing this:
sqlwish@.microsoft.com
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John" <John@.nospam.infovis.co.uk> wrote in message news:%23iv4mYrZFHA.2664@.TK2MSFTNGP15.phx.gbl...
> Why can't we do things like that which we can easily do in access. How hard
> can it be for ms to add these things which will make life so easy.
> Regards
>
|||You mean no one has found it to be a problem as yet?
Regards
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:esZQgZsZFHA.3596@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Consider proposing this:
> sqlwish@.microsoft.com
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:%23iv4mYrZFHA.2664@.TK2MSFTNGP15.phx.gbl...

Removing Identity Property

Folks,
I am trying to alter my database thru SQL Scripts. I have a few tables where
I have columns having Identity Property. I now want to modify these columns
having no Identity Property. But I can't find SQL Statement that can do
this.
Can some one help?
Regards
Shailaindra SharmaYou cannot remove this property... you must add another column, move the id
info to the new column, drop the old column, and rename the new column...
Or do the same thing at the table level... However,
If you go into SEM, select the table , right click and go into design, you
can simply select the column and uncheck Identity at the bottom... SEM will
generate a script to do this work for you - if there are lots of rows it may
take a while.
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Shailaindra Sharma" <shailaindra@.abosoftware.com> wrote in message
news:eIHMP%23pZFHA.3840@.tk2msftngp13.phx.gbl...
> Folks,
> I am trying to alter my database thru SQL Scripts. I have a few tables
where
> I have columns having Identity Property. I now want to modify these
columns
> having no Identity Property. But I can't find SQL Statement that can do
> this.
> Can some one help?
> Regards
> Shailaindra Sharma
>|||Why can't we do things like that which we can easily do in access. How hard
can it be for ms to add these things which will make life so easy.
Regards
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:ewlf9KqZFHA.3048@.TK2MSFTNGP14.phx.gbl...
> You cannot remove this property... you must add another column, move the
> id
> info to the new column, drop the old column, and rename the new column...
> Or do the same thing at the table level... However,
> If you go into SEM, select the table , right click and go into design, you
> can simply select the column and uncheck Identity at the bottom... SEM
> will
> generate a script to do this work for you - if there are lots of rows it
> may
> take a while.
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "Shailaindra Sharma" <shailaindra@.abosoftware.com> wrote in message
> news:eIHMP%23pZFHA.3840@.tk2msftngp13.phx.gbl...
> where
> columns
>|||Consider proposing this:
sqlwish@.microsoft.com
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John" <John@.nospam.infovis.co.uk> wrote in message news:%23iv4mYrZFHA.2664@.TK2MSFTNGP15.phx
.gbl...
> Why can't we do things like that which we can easily do in access. How har
d
> can it be for ms to add these things which will make life so easy.
> Regards
>|||You mean no one has found it to be a problem as yet?
Regards
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:esZQgZsZFHA.3596@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Consider proposing this:
> sqlwish@.microsoft.com
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:%23iv4mYrZFHA.2664@.TK2MSFTNGP15.phx.gbl...

Removing IDENTITY from existing column

I have been testing some code that has been inserting large amounts of data
into some tables. All of the tables have a Primary Key that is also an
Identity. After a bad run, I go through and DELETE all of the entries in
each table (I would TRUNCATE, but they all have at least three other tables
referencing them and you can't TRUNCATE a linked table). Only problem is
that the IDs are getting extremely large and make it harder for me to
determine if the code is working correctly. In the TSQL that I use to DELET
E
the contents of the tables, I would like to set the IDENTITY to NO, then bac
k
to YES so that the IDs increment from 1 again instead of some very large
number. The only way that I can see is to drop the column and then add it
again. I am not sure how well this would work since I have lots of foreign
keys referencing these PKs.
Thanks in advance.
--
Chris Lieb
UPS CACH, Hodgekins, IL
Tech Support Group - Systems/AppsDrop the IDENTITY and not the column, then put the IDENTITY back on the
column -- one way.
Owen
"Chris Lieb" <ChrisLieb@.discussions.microsoft.com> wrote in message
news:27E1D52C-BE0C-425D-BCA2-04BB2A89B367@.microsoft.com...
>I have been testing some code that has been inserting large amounts of data
> into some tables. All of the tables have a Primary Key that is also an
> Identity. After a bad run, I go through and DELETE all of the entries in
> each table (I would TRUNCATE, but they all have at least three other
> tables
> referencing them and you can't TRUNCATE a linked table). Only problem is
> that the IDs are getting extremely large and make it harder for me to
> determine if the code is working correctly. In the TSQL that I use to
> DELETE
> the contents of the tables, I would like to set the IDENTITY to NO, then
> back
> to YES so that the IDs increment from 1 again instead of some very large
> number. The only way that I can see is to drop the column and then add it
> again. I am not sure how well this would work since I have lots of
> foreign
> keys referencing these PKs.
> Thanks in advance.
> --
> Chris Lieb
> UPS CACH, Hodgekins, IL
> Tech Support Group - Systems/Apps|||See "DBCC CHECKIDENT" in BOL.
AMB
"Chris Lieb" wrote:

> I have been testing some code that has been inserting large amounts of dat
a
> into some tables. All of the tables have a Primary Key that is also an
> Identity. After a bad run, I go through and DELETE all of the entries in
> each table (I would TRUNCATE, but they all have at least three other table
s
> referencing them and you can't TRUNCATE a linked table). Only problem is
> that the IDs are getting extremely large and make it harder for me to
> determine if the code is working correctly. In the TSQL that I use to DEL
ETE
> the contents of the tables, I would like to set the IDENTITY to NO, then b
ack
> to YES so that the IDs increment from 1 again instead of some very large
> number. The only way that I can see is to drop the column and then add it
> again. I am not sure how well this would work since I have lots of foreig
n
> keys referencing these PKs.
> Thanks in advance.
> --
> Chris Lieb
> UPS CACH, Hodgekins, IL
> Tech Support Group - Systems/Apps|||Try: DBCC CHECKIDENT (tableName)
Look it ub in BOL.
"Chris Lieb" <ChrisLieb@.discussions.microsoft.com> wrote in message
news:27E1D52C-BE0C-425D-BCA2-04BB2A89B367@.microsoft.com...
>I have been testing some code that has been inserting large amounts of data
> into some tables. All of the tables have a Primary Key that is also an
> Identity. After a bad run, I go through and DELETE all of the entries in
> each table (I would TRUNCATE, but they all have at least three other
> tables
> referencing them and you can't TRUNCATE a linked table). Only problem is
> that the IDs are getting extremely large and make it harder for me to
> determine if the code is working correctly. In the TSQL that I use to
> DELETE
> the contents of the tables, I would like to set the IDENTITY to NO, then
> back
> to YES so that the IDs increment from 1 again instead of some very large
> number. The only way that I can see is to drop the column and then add it
> again. I am not sure how well this would work since I have lots of
> foreign
> keys referencing these PKs.
> Thanks in advance.
> --
> Chris Lieb
> UPS CACH, Hodgekins, IL
> Tech Support Group - Systems/Apps|||Use
DBCC CHECKIDENT ('table_name',RESEED,0)
to reset the IDENTITY value.
David Portas
SQL Server MVP
--|||I tried to add IDENTITY to an existing column, and it didn't work. I was
told on this forum that it can't be done.
"Owen Mortensen" <ojm.NO_SPAM@.acm.org> wrote in message
news:O%23vzDYhYFHA.980@.TK2MSFTNGP12.phx.gbl...
> Drop the IDENTITY and not the column, then put the IDENTITY back on the
> column -- one way.
> Owen
> "Chris Lieb" <ChrisLieb@.discussions.microsoft.com> wrote in message
> news:27E1D52C-BE0C-425D-BCA2-04BB2A89B367@.microsoft.com...
>|||Strange. I do that all the time (using Enterprise Manager)...
Owen
"Paul Pedersen" <no-reply@.swen.com> wrote in message
news:eI6kqdhYFHA.3960@.TK2MSFTNGP10.phx.gbl...
>I tried to add IDENTITY to an existing column, and it didn't work. I was
>told on this forum that it can't be done.
>
> "Owen Mortensen" <ojm.NO_SPAM@.acm.org> wrote in message
> news:O%23vzDYhYFHA.980@.TK2MSFTNGP12.phx.gbl...
>|||Strange that you use Enterprise Manager to make changes to table
structures :-)
Paul is in fact correct that you cannot do this with a single statement
in TSQL. One of the (many) problems with using Enerprise Manager is
that to achieve this it will drop your table, constraints and indexes,
recreate and then copy all your data over. Definitely not recommended
on a system that's in use!
David Portas
SQL Server MVP
--|||That's because behind the scenes EM will jump through the hoops
of creating a temp table copy of the original table, creating a
new table with identity column assigned, inserting data from the
the temp table, and then dropping the temp table. There's no
easy way of doing it through Query Analyzer other than
what I've outlined.
"Owen Mortensen" <ojm.NO_SPAM@.acm.org> wrote in message
news:e$HrUihYFHA.3320@.TK2MSFTNGP12.phx.gbl...
> Strange. I do that all the time (using Enterprise Manager)...
> Owen
> "Paul Pedersen" <no-reply@.swen.com> wrote in message
> news:eI6kqdhYFHA.3960@.TK2MSFTNGP10.phx.gbl...
an
then
large
add
>|||EM does a lot of work behind the scenes.
A SIMPLE example on a table with no foreign key references where Identity is
removed:
It creates a new table, copies the data, drops the old table and renames the
new one.
If there where foreign key references, EM has to handle this too.
In EM, you have an icon in the table design view to save the script when you
make a change and before committing it.
Try it (on a test table of course).
"Owen Mortensen" <ojm.NO_SPAM@.acm.org> wrote in message
news:e$HrUihYFHA.3320@.TK2MSFTNGP12.phx.gbl...
> Strange. I do that all the time (using Enterprise Manager)...
> Owen
> "Paul Pedersen" <no-reply@.swen.com> wrote in message
> news:eI6kqdhYFHA.3960@.TK2MSFTNGP10.phx.gbl...

Removing HASH Match / Inner Join

Hi,

I'm at my wits end! I have two large tables one with 1.2mill one with 2.3 mill and they are very wide tables. I have a select with an inner join. All columns used in the join are contained in indexes. But it does an index scan and a massive hashmatch. Why is this? both tables have columns in the index ordered the same, all datatypes of the indexes are ints. The code looks like this.

SELECT TblActivities.*
FROM TblActivities
INNER JOIN TblBookingsCraig
ON
TblActivities.clientID = TblBookingsCraig.clientid AND
TblActivities.campaignID = TblBookingsCraig.campaignid AND
TblActivities.SupplierID = TblBookingsCraig.SupplierID and
TblActivities.CreativeVersion = TblBookingsCraig.CreativeVersion

Does anyone know why it won't perform an index seek?

Cheers
CYou should have the indexes on the fields you want to join, do you have them like that?

TblActivities.clientID = TblBookingsCraig.clientid AND
TblActivities.campaignID = TblBookingsCraig.campaignid AND
TblActivities.SupplierID = TblBookingsCraig.SupplierID and
TblActivities.CreativeVersion = TblBookingsCraig.CreativeVersion|||Hi,

Thanks for the reply. Yes I do have the indexes identically setup on both tables. Could it be ignoring the indexes because of the size difference of the two tables? One has 1millish and the other 2.5mill ish?

Cheers
C|||Did you check the values of these columns? if they contain duplicate values or highly identical values for number of records; say for 100 records at least the indexes have same values. In this case, the index scan would be the decision that SQL optimizer might take!

Removing Duplicates rows from Inner Join

i have two tables, tab1 having N1 col and tab2 N2 col. now N1 is subset
of N2.
I need the information from tab2 (having N2) of all rows having the
matching entry in N1 in tab1.
For this i am using Inner Join on cols N1 and n2. But result is giving
duplicate rows. Can anyone suggest how do u i remove those duplicate
rows? or may be a better way to do the above work... Thanks
Sounds like you should be using EXISTS
rather than a join

SELECT * FROM tab2
WHERE EXISTS (SELECT * FROM tab1 WHERE tab1.N1=tab2.N2)|||thanks mark
but i have tables with large data and running this querry is taking a
lot of time.
can u suggest a better method or optimize this querry?|||Did you try to use DISTINCT to remove the duplicates?
SELECT DISTINCT tab2.* FROM tab2 INNER JOIN tab1 ON tab2.N2 = tab1.N1|||Can you post your DDL including indexes|||asgars (asgars@.gmail.com) writes:
> thanks mark
> but i have tables with large data and running this querry is taking a
> lot of time.
> can u suggest a better method or optimize this querry?

Which query? It's very difficult to suggest optimizations to a query
without seeing it, and without knowledge of the tables.

Please post:

o The query you are using now.
o CREATE TABLE and CREATE INDEX statements for the inolved tables.
o Some indication on number of rows in the table.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx