Sql Server Alignment problem: syntax

Showing posts with label syntax. Show all posts

Tuesday, March 27, 2012

Altering column names?

I have been looking in the SQL Server Help Files, but I cant seem to find the syntax to change the name of a column name..?
Can it be done? if so, what is the syntax?well, i guess you could do this:alter table foo
add column barnew datatype etc.

update table foo
set barnew = bar

update table foo
drop column bar

but why? i mean, you can change the name in any select statement:select bar as barnew
from foo|||I want to be able to change the names of columns because I am building an interface to an SQL Database.

That code you gave me doesnt work, you dont use the word 'column' when adding a new column, you just use 'add' alone.|||sorry, i do not always test out the syntax i suggest

but at least i got across the idea of the approach to use

you're welcome|||Well .. i think sp_rename works on columnt too ... need to check that out ..

create table myTab (mycol int)
go
sp_rename 'mytab.mycol' ,'mytab.mycol1'
go
select mycol1 from myTab
go

Seems to worksql

Sunday, March 25, 2012

ALTER USER WITH LOGIN

What's the syntax to use the ALTER USER command to remap an orphaned user to
an existing login in SS2005?It should be:
ALTER USER user_name WITH LOGIN = login_name
Replace the <user_name> and <login_name> accordingly with the orphaned user
and the existing login.
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Do you know about the "sp_change_users_login" sp?
You can check it out from the following link if you don't.
http://technet.microsoft.com/en-us/library/ms174378.aspx
--
Ekrem Ã?nsoy
"ken s" <kens@.discussions.microsoft.com> wrote in message
news:8C6629E3-C7D7-467A-A54E-0D9DC5F480F9@.microsoft.com...
> What's the syntax to use the ALTER USER command to remap an orphaned user
> to
> an existing login in SS2005?|||The only catch is it that sp_change_users_login works only for SQL Server
logins, while ALTER USER WITH LOGIN supports both SQL Server and Windows
logins.
Plamen Ratchev
http://www.SQLStudio.com|||Yup, I already know that but Ken did not mention this need. I did not need
to mentioned this catch because it's already declared in the link I gave.
Alternative is alternative.
--
Ekrem Önsoy
"Plamen Ratchev" <Plamen@.SQLStudio.com> wrote in message
news:15E92A6A-2E39-40B8-A9F9-0F90007BB3D7@.microsoft.com...
> The only catch is it that sp_change_users_login works only for SQL Server
> logins, while ALTER USER WITH LOGIN supports both SQL Server and Windows
> logins.
> Plamen Ratchev
> http://www.SQLStudio.com|||That worked fine on my development machine, but on the server I get this error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'psts_web'.
Here's the code I tried to run:
ALTER USER psts_web WITH LOGIN psts_web
"psts_web" is the user name and the login name and they both exist in the db.
Thanks
/Ken|||Hi Ken,
You used incorrect syntax. It should be:
ALTER USER psts_web WITH LOGIN = psts_web
Note the part "... LOGIN = psts_web", you were missing the "=".
HTH,
Plamen Ratchev
http://www.SQLStudio.com

ALTER USER WITH LOGIN

What's the syntax to use the ALTER USER command to remap an orphaned user to
an existing login in SS2005?
It should be:
ALTER USER user_name WITH LOGIN = login_name
Replace the <user_name> and <login_name> accordingly with the orphaned user
and the existing login.
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||Do you know about the "sp_change_users_login" sp?
You can check it out from the following link if you don't.
http://technet.microsoft.com/en-us/library/ms174378.aspx
Ekrem ?nsoy
"ken s" <kens@.discussions.microsoft.com> wrote in message
news:8C6629E3-C7D7-467A-A54E-0D9DC5F480F9@.microsoft.com...
> What's the syntax to use the ALTER USER command to remap an orphaned user
> to
> an existing login in SS2005?
|||The only catch is it that sp_change_users_login works only for SQL Server
logins, while ALTER USER WITH LOGIN supports both SQL Server and Windows
logins.
Plamen Ratchev
http://www.SQLStudio.com
|||Yup, I already know that but Ken did not mention this need. I did not need
to mentioned this catch because it's already declared in the link I gave.
Alternative is alternative.
Ekrem nsoy
"Plamen Ratchev" <Plamen@.SQLStudio.com> wrote in message
news:15E92A6A-2E39-40B8-A9F9-0F90007BB3D7@.microsoft.com...
> The only catch is it that sp_change_users_login works only for SQL Server
> logins, while ALTER USER WITH LOGIN supports both SQL Server and Windows
> logins.
> Plamen Ratchev
> http://www.SQLStudio.com
|||That worked fine on my development machine, but on the server I get this error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'psts_web'.
Here's the code I tried to run:
ALTER USER psts_web WITH LOGIN psts_web
"psts_web" is the user name and the login name and they both exist in the db.
Thanks
/Ken
|||Hi Ken,
You used incorrect syntax. It should be:
ALTER USER psts_web WITH LOGIN = psts_web
Note the part "... LOGIN = psts_web", you were missing the "=".
HTH,
Plamen Ratchev
http://www.SQLStudio.com

ALTER TABLE/COLUMN syntax

Hi!
I want a add default value to existing column with int type with
following syntax:
ALTER TABLE firmNoliktava_test ALTER COLUMN valstsID SET DEFAULT (1)
but got error
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SET'.
Server SQL 2005 x64, in server Help Contents i see example
ALTER TABLE MyCustomers ALTER COLUMN CompanyName SET DEFAULT 'A. Datum
Corporation'
What i do wrong ?
Sry my poor Eng.
AndrisSimply add the default with an ALTER TABLE:
ALTER TABLE firmNoliktava_test
ADD CONSTRAINT DF1_firmNoliktava_test
DEFAULT 1 FOR valstsID
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Andris" <spameris@.gmail.com> wrote in message
news:eY4DCdHiGHA.3956@.TK2MSFTNGP02.phx.gbl...
Hi!
I want a add default value to existing column with int type with
following syntax:
ALTER TABLE firmNoliktava_test ALTER COLUMN valstsID SET DEFAULT (1)
but got error
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SET'.
Server SQL 2005 x64, in server Help Contents i see example
ALTER TABLE MyCustomers ALTER COLUMN CompanyName SET DEFAULT 'A. Datum
Corporation'
What i do wrong ?
Sry my poor Eng.
Andris|||On Mon, 05 Jun 2006 11:08:02 +0300, Andris wrote:

>Hi!
>I want a add default value to existing column with int type with
>following syntax:
>ALTER TABLE firmNoliktava_test ALTER COLUMN valstsID SET DEFAULT (1)
>but got error
>Msg 156, Level 15, State 1, Line 2
>Incorrect syntax near the keyword 'SET'.
>Server SQL 2005 x64, in server Help Contents i see example
>ALTER TABLE MyCustomers ALTER COLUMN CompanyName SET DEFAULT 'A. Datum
>Corporation'
>
>What i do wrong ?
Hi Andris,
The example you have seen is not for SQL Server, but for SQL Server
Mobile edition. There are many syntax difference between "normal" SQL
Server and the mobile version. I've been tricked by this myself quite a
few times already - just remember to always check the heading of the
subject in Books Online to check if you're looking at a Mobile or a
T-SQL subject.
Hugo Kornelis, SQL Server MVP

ALTER TABLE/COLUMN syntax

Hi!
I want a add default value to existing column with int type with
following syntax:
ALTER TABLE firmNoliktava_test ALTER COLUMN valstsID SET DEFAULT (1)
but got error
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SET'.
Server SQL 2005 x64, in server Help Contents i see example
ALTER TABLE MyCustomers ALTER COLUMN CompanyName SET DEFAULT 'A. Datum
Corporation'
What i do wrong ?
Sry my poor Eng.
AndrisSimply add the default with an ALTER TABLE:
ALTER TABLE firmNoliktava_test
ADD CONSTRAINT DF1_firmNoliktava_test
DEFAULT 1 FOR valstsID
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Andris" <spameris@.gmail.com> wrote in message
news:eY4DCdHiGHA.3956@.TK2MSFTNGP02.phx.gbl...
Hi!
I want a add default value to existing column with int type with
following syntax:
ALTER TABLE firmNoliktava_test ALTER COLUMN valstsID SET DEFAULT (1)
but got error
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SET'.
Server SQL 2005 x64, in server Help Contents i see example
ALTER TABLE MyCustomers ALTER COLUMN CompanyName SET DEFAULT 'A. Datum
Corporation'
What i do wrong ?
Sry my poor Eng.
Andris|||On Mon, 05 Jun 2006 11:08:02 +0300, Andris wrote:
>Hi!
>I want a add default value to existing column with int type with
>following syntax:
>ALTER TABLE firmNoliktava_test ALTER COLUMN valstsID SET DEFAULT (1)
>but got error
>Msg 156, Level 15, State 1, Line 2
>Incorrect syntax near the keyword 'SET'.
>Server SQL 2005 x64, in server Help Contents i see example
>ALTER TABLE MyCustomers ALTER COLUMN CompanyName SET DEFAULT 'A. Datum
>Corporation'
>
>What i do wrong ?
Hi Andris,
The example you have seen is not for SQL Server, but for SQL Server
Mobile edition. There are many syntax difference between "normal" SQL
Server and the mobile version. I've been tricked by this myself quite a
few times already - just remember to always check the heading of the
subject in Books Online to check if you're looking at a Mobile or a
T-SQL subject.
--
Hugo Kornelis, SQL Server MVP

Thursday, March 22, 2012

ALTER TABLE statement to lengthen column

In SQL7 when trying to use ALTER TABLE to make a column longer I get bad syn
tax.
ALTER TABLE table ALTER COLUMN column varchar(90)
It says bad syntax near 'COLUMN'
Other ALTER TABLE statements work like ADD
This works in SQL2000 and as I read it should work in SQL7
Any help appreciatedcheck the compatibility level of the database.
exec sp_dbcmptlevel <database name>
If not 7 then run following command.
sp_dbcmptlevel '<databasename>','70'
Vishal Parkar
vgparkar@.yahoo.co.in|||> If not 7 then run following command.
> sp_dbcmptlevel '<databasename>','70'
Well, make sure it's not in 6.5 compatibility for a reason (e.g. to prevent
ALTER COLUMN statements, or maybe better reason(s)). :-)
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Thanks, that was the issue. This database was update from 6.5 some time ago.
Thanks again

Thursday, March 8, 2012

Alter Index with REBUILD on master database?

SQL Server 2005:
We plan to use the alter index with rebuild syntax to rebuild our indexes
weekly in a job. Should master and msdb tables be included? I have no
interest in doing it manually a couple times per year if it needs it.
Thanks,
MarkI remember asking the same thing in a SQL 2000 forum a long time ago and the
consensus was you never need to include any of the system databases in
reindexing / update stats. I would gather the same is applicable to SQL
2005.
HTH,
Rubens
"Mark" <mark@.idonotlikespam.com> wrote in message
news:e0zii6#oIHA.420@.TK2MSFTNGP02.phx.gbl...
> SQL Server 2005:
> We plan to use the alter index with rebuild syntax to rebuild our indexes
> weekly in a job. Should master and msdb tables be included? I have no
> interest in doing it manually a couple times per year if it needs it.
> Thanks,
> Mark
>|||Why not?
"Rubens" <rubensrose@.hotmail.com> wrote in message
news:uUmDCKBpIHA.4912@.TK2MSFTNGP03.phx.gbl...
>I remember asking the same thing in a SQL 2000 forum a long time ago and
>the consensus was you never need to include any of the system databases in
>reindexing / update stats. I would gather the same is applicable to SQL
>2005.
> HTH,
> Rubens
> "Mark" <mark@.idonotlikespam.com> wrote in message
> news:e0zii6#oIHA.420@.TK2MSFTNGP02.phx.gbl...
>> SQL Server 2005:
>> We plan to use the alter index with rebuild syntax to rebuild our indexes
>> weekly in a job. Should master and msdb tables be included? I have no
>> interest in doing it manually a couple times per year if it needs it.
>> Thanks,
>> Mark|||Because in SQL2005 there shouldn't be any tables (of consequence) in the
master database that you can actually run UPDATE STATISTICS or rebuild
indexes.
Linchi
"Mark" wrote:
> Why not?
> "Rubens" <rubensrose@.hotmail.com> wrote in message
> news:uUmDCKBpIHA.4912@.TK2MSFTNGP03.phx.gbl...
> >I remember asking the same thing in a SQL 2000 forum a long time ago and
> >the consensus was you never need to include any of the system databases in
> >reindexing / update stats. I would gather the same is applicable to SQL
> >2005.
> >
> > HTH,
> > Rubens
> >
> > "Mark" <mark@.idonotlikespam.com> wrote in message
> > news:e0zii6#oIHA.420@.TK2MSFTNGP02.phx.gbl...
> >> SQL Server 2005:
> >>
> >> We plan to use the alter index with rebuild syntax to rebuild our indexes
> >> weekly in a job. Should master and msdb tables be included? I have no
> >> interest in doing it manually a couple times per year if it needs it.
> >>
> >> Thanks,
> >> Mark
> >>
>
>

alter index syntax

I'm using sql server 2005 sp1 on win2003 server
I want to reorganize all indices in my database.
Online-docu says that dbcc indexdefrag should not be used anymore.
Instead, 'alter index' should be used
My syntax for one table is like this:
alter index all on bew reorganize
sql server says:
Meldung 156, Ebene 15, Status 1, Zeile 1
Incorrect syntax near the keyword 'index'.
What's wrong ?
Furthermore I would like to know, what the sql-command looks like for
reorganizing indices for
ALL the tables in the database?My guess database isn't in 90 compatibility mode. See sp_dbcmptlevel. Also, see Books Online for
sample code on how to reorg all indexes for a database:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/d294dd8e-82d5-4628-aa2d-e57702230613.htm
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"keltchen" <m.kaltenboeck@.powersoftware.at> wrote in message
news:1154958955.385435.160610@.n13g2000cwa.googlegroups.com...
> I'm using sql server 2005 sp1 on win2003 server
> I want to reorganize all indices in my database.
> Online-docu says that dbcc indexdefrag should not be used anymore.
> Instead, 'alter index' should be used
> My syntax for one table is like this:
> alter index all on bew reorganize
> sql server says:
> Meldung 156, Ebene 15, Status 1, Zeile 1
> Incorrect syntax near the keyword 'index'.
> What's wrong ?
> Furthermore I would like to know, what the sql-command looks like for
> reorganizing indices for
> ALL the tables in the database?
>|||Hi Tibor,
that might be the reason.
So I tried to use this command
sp_dbcmptlevel mydb, 90
I get this error:
Meldung 15416, Ebene 16, Status 1, Prozedur sp_dbcmptlevel, Zeile 92
Usage: sp_dbcmptlevel [dbname [, compatibilitylevel]]
Inside the procedure I can see that only the levels 60, 65, 70, 80 are
allowed.
Also using object explorer (db options) I only can see 70 and 80.
What's the matter?
I'm testing with a new installed 2005 instance,
The database 'comes' from SQL 7.0 and I did a restore of the .bak file
on the 2005 server
Thank you for more help|||This sounds like you have connected to a SQL 2000 instance.
Try this:
SELECT serverproperty('ProductVersion')
--
HTH
Kalen Delaney, SQL Server MVP
"keltchen" <m.kaltenboeck@.powersoftware.at> wrote in message
news:1155022695.236345.244990@.b28g2000cwb.googlegroups.com...
> Hi Tibor,
> that might be the reason.
> So I tried to use this command
> sp_dbcmptlevel mydb, 90
> I get this error:
> Meldung 15416, Ebene 16, Status 1, Prozedur sp_dbcmptlevel, Zeile 92
> Usage: sp_dbcmptlevel [dbname [, compatibilitylevel]]
> Inside the procedure I can see that only the levels 60, 65, 70, 80 are
> allowed.
> Also using object explorer (db options) I only can see 70 and 80.
> What's the matter?
> I'm testing with a new installed 2005 instance,
> The database 'comes' from SQL 7.0 and I did a restore of the .bak file
> on the 2005 server
> Thank you for more help
>

alter index syntax

I'm using sql server 2005 sp1 on win2003 server
I want to reorganize all indices in my database.
Online-docu says that dbcc indexdefrag should not be used anymore.
Instead, 'alter index' should be used
My syntax for one table is like this:
alter index all on bew reorganize
sql server says:
Meldung 156, Ebene 15, Status 1, Zeile 1
Incorrect syntax near the keyword 'index'.
What's wrong ?
Furthermore I would like to know, what the sql-command looks like for
reorganizing indices for
ALL the tables in the database?My guess database isn't in 90 compatibility mode. See sp_dbcmptlevel. Also,
see Books Online for
sample code on how to reorg all indexes for a database:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/d294dd8e-82d5-4628-aa2d-
e57702230613.htm
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"keltchen" <m.kaltenboeck@.powersoftware.at> wrote in message
news:1154958955.385435.160610@.n13g2000cwa.googlegroups.com...
> I'm using sql server 2005 sp1 on win2003 server
> I want to reorganize all indices in my database.
> Online-docu says that dbcc indexdefrag should not be used anymore.
> Instead, 'alter index' should be used
> My syntax for one table is like this:
> alter index all on bew reorganize
> sql server says:
> Meldung 156, Ebene 15, Status 1, Zeile 1
> Incorrect syntax near the keyword 'index'.
> What's wrong ?
> Furthermore I would like to know, what the sql-command looks like for
> reorganizing indices for
> ALL the tables in the database?
>|||Hi Tibor,
that might be the reason.
So I tried to use this command
sp_dbcmptlevel mydb, 90
I get this error:
Meldung 15416, Ebene 16, Status 1, Prozedur sp_dbcmptlevel, Zeile 92
Usage: sp_dbcmptlevel [dbname [, compatibilitylevel]]
Inside the procedure I can see that only the levels 60, 65, 70, 80 are
allowed.
Also using object explorer (db options) I only can see 70 and 80.
What's the matter?
I'm testing with a new installed 2005 instance,
The database 'comes' from SQL 7.0 and I did a restore of the .bak file
on the 2005 server
Thank you for more help|||This sounds like you have connected to a SQL 2000 instance.
Try this:
SELECT serverproperty('ProductVersion')
HTH
Kalen Delaney, SQL Server MVP
"keltchen" <m.kaltenboeck@.powersoftware.at> wrote in message
news:1155022695.236345.244990@.b28g2000cwb.googlegroups.com...
> Hi Tibor,
> that might be the reason.
> So I tried to use this command
> sp_dbcmptlevel mydb, 90
> I get this error:
> Meldung 15416, Ebene 16, Status 1, Prozedur sp_dbcmptlevel, Zeile 92
> Usage: sp_dbcmptlevel [dbname [, compatibilitylevel]]
> Inside the procedure I can see that only the levels 60, 65, 70, 80 are
> allowed.
> Also using object explorer (db options) I only can see 70 and 80.
> What's the matter?
> I'm testing with a new installed 2005 instance,
> The database 'comes' from SQL 7.0 and I did a restore of the .bak file
> on the 2005 server
> Thank you for more help
>

Alter Identity Column question

What is the syntax for changing an identity column in a table to "Not For Replication"?
Alter table x
?? identity column, Not For Replication
Thanx!
JLS,
this is not possible in TSQL. You can do it in EM, but if you run profiler you'll see that a huge amount of work goes on behind the scenes, including the creation, population and renaming of a temporary table.
HTH,
Paul Ibison
|||try this
sp_configure 'allow_updates', 1
go
reconfigure with override
go
update syscolumns set colstat=colstat|0x0008 where colstat & 0x0001 <> 0 and
colstat & 0x0008 =0
go
sp_configure 'allow updates', 0
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23gElYLsMEHA.620@.TK2MSFTNGP10.phx.gbl...
What is the syntax for changing an identity column in a table to "Not For Replication"?
Alter table x
?? identity column, Not For Replication
Thanx!
|||I thought so...
I ran profiler and couldn't pick up any Alter statement, so I kinda expected this answer.
Thanx anyway!
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:ONWdeetMEHA.2244@.tk2msftngp13.phx.gbl...
JLS,
this is not possible in TSQL. You can do it in EM, but if you run profiler you'll see that a huge amount of work goes on behind the scenes, including the creation, population and renaming of a temporary table.
HTH,
Paul Ibison
|||I'm sorry, but I don't understand what this will do. Where do I place the table/column name of the identity column that I want to change to "Not for Replication" in your script?
What will this change to syscolumns provide? A default of setting all my identity columns to "Yes (Not for Replication)"?
"Hilary Cotter" <hilaryk@.att.net> wrote in message news:OL9Z4FxMEHA.740@.TK2MSFTNGP12.phx.gbl...
try this
sp_configure 'allow_updates', 1
go
reconfigure with override
go
update syscolumns set colstat=colstat|0x0008 where colstat & 0x0001 <> 0 and
colstat & 0x0008 =0
go
sp_configure 'allow updates', 0
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23gElYLsMEHA.620@.TK2MSFTNGP10.phx.gbl...
What is the syntax for changing an identity column in a table to "Not For Replication"?
Alter table x
?? identity column, Not For Replication
Thanx!
|||run this in your publication database. Here I am setting the identity column for the jobs table to NFR
sp_configure 'allow updates', 1
GO
reconfigure with override
GO
update syscolumns set colstat = colstat | 0x0008 where colstat & 0x0001 <> 0 and colstat & 0x0008 = 0 and id=object_id('jobs')
GO
sp_configure 'allow updates', 0
"JLS" <jlshoop@.hotmail.com> wrote in message news:O3iV6P2MEHA.1608@.TK2MSFTNGP12.phx.gbl...
I'm sorry, but I don't understand what this will do. Where do I place the table/column name of the identity column that I want to change to "Not for Replication" in your script?
What will this change to syscolumns provide? A default of setting all my identity columns to "Yes (Not for Replication)"?
"Hilary Cotter" <hilaryk@.att.net> wrote in message news:OL9Z4FxMEHA.740@.TK2MSFTNGP12.phx.gbl...
try this
sp_configure 'allow_updates', 1
go
reconfigure with override
go
update syscolumns set colstat=colstat|0x0008 where colstat & 0x0001 <> 0 and
colstat & 0x0008 =0
go
sp_configure 'allow updates', 0
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23gElYLsMEHA.620@.TK2MSFTNGP10.phx.gbl...
What is the syntax for changing an identity column in a table to "Not For Replication"?
Alter table x
?? identity column, Not For Replication
Thanx!
|||AWESOME! That's the answer, THANK YOU!
Now I will pay you back by buying your book once it hits the market. :-)
"Hilary Cotter" <hilaryk@.att.net> wrote in message news:%23f7rE0BNEHA.4036@.TK2MSFTNGP12.phx.gbl...
run this in your publication database. Here I am setting the identity column for the jobs table to NFR
sp_configure 'allow updates', 1
GO
reconfigure with override
GO
update syscolumns set colstat = colstat | 0x0008 where colstat & 0x0001 <> 0 and colstat & 0x0008 = 0 and id=object_id('jobs')
GO
sp_configure 'allow updates', 0
"JLS" <jlshoop@.hotmail.com> wrote in message news:O3iV6P2MEHA.1608@.TK2MSFTNGP12.phx.gbl...
I'm sorry, but I don't understand what this will do. Where do I place the table/column name of the identity column that I want to change to "Not for Replication" in your script?
What will this change to syscolumns provide? A default of setting all my identity columns to "Yes (Not for Replication)"?
"Hilary Cotter" <hilaryk@.att.net> wrote in message news:OL9Z4FxMEHA.740@.TK2MSFTNGP12.phx.gbl...
try this
sp_configure 'allow_updates', 1
go
reconfigure with override
go
update syscolumns set colstat=colstat|0x0008 where colstat & 0x0001 <> 0 and
colstat & 0x0008 =0
go
sp_configure 'allow updates', 0
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23gElYLsMEHA.620@.TK2MSFTNGP10.phx.gbl...
What is the syntax for changing an identity column in a table to "Not For Replication"?
Alter table x
?? identity column, Not For Replication
Thanx!

alter database syntax

ok i must be blind as I can't find/figure it out. What is the correct syntax
for alter database using the recovery full option. I want to change the
recovery method to full from simple. Thanks for the info.
Jasonalter database dbname
set recovery full
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Jason Meyer" <jason.meyer@.nospam.roseville.k12.mn.us> wrote in message
news:eUDT3RJ1DHA.3824@.TK2MSFTNGP11.phx.gbl...
> ok i must be blind as I can't find/figure it out. What is the correct
syntax
> for alter database using the recovery full option. I want to change the
> recovery method to full from simple. Thanks for the info.
>
> Jason
>|||alter database XXX
set recovery full
"Jason Meyer" <jason.meyer@.nospam.roseville.k12.mn.us> wrote in message
news:eUDT3RJ1DHA.3824@.TK2MSFTNGP11.phx.gbl...
> ok i must be blind as I can't find/figure it out. What is the correct
syntax
> for alter database using the recovery full option. I want to change the
> recovery method to full from simple. Thanks for the info.
>
> Jason
>|||never mind, found it. thanks
"Jason Meyer" <jason.meyer@.nospam.roseville.k12.mn.us> wrote in message
news:eUDT3RJ1DHA.3824@.TK2MSFTNGP11.phx.gbl...
> ok i must be blind as I can't find/figure it out. What is the correct
syntax
> for alter database using the recovery full option. I want to change the
> recovery method to full from simple. Thanks for the info.
>
> Jason
>

Wednesday, March 7, 2012

Alter Database Move column

Hi,
Is it possible to move a column using an SQL script?
I have looked at the Alter Table syntax help but can't see any indication of
ordinal control .
I have a table that I have had to add an indentity column to.
I want to move it to pos 0.
My script so far is :
alter table MasterStationTest
drop constraint pk_masterstationtest
go
alter table MasterStationTest
add id int
IDENTITY(1,1)
PRIMARY KEY
go
thanks
BobNo, you have to drop and re-create the table for that.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bob Clegg" <bclegg@.clear.net.nz> wrote in message news:%23cIRcW9PEHA.3304@.TK2MSFTNGP12.phx.
gbl...
> Hi,
> Is it possible to move a column using an SQL script?
> I have looked at the Alter Table syntax help but can't see any indication
of
> ordinal control .
> I have a table that I have had to add an indentity column to.
> I want to move it to pos 0.
> My script so far is :
> alter table MasterStationTest
> drop constraint pk_masterstationtest
> go
> alter table MasterStationTest
> add id int
> IDENTITY(1,1)
> PRIMARY KEY
> go
> thanks
> Bob
>|||Hi,
No , You cant change the column position using ALTER table statement. The
only solution is:-
1. Create a new table with new structure with identity property
2. Insert data into the new table from actual table (Execlude the col1 which
is identiy)
insert into new_table(col2,col3...coln) select col1,col2,col3...coln from
actual_table
3. Generate Script for indexes and dependant objects
4. Verify all the data are moved successfully
5. Drop the actual table
6. Rename the new_table to actual table using (sp_rename new_table,
actual_table)
7. Create the index in the table (execute the script generated in step-3)
Note:
Enterprise manager will do the above steps when you change column postions
or add columns .....
Thanks
Hari
MCDBA
"Bob Clegg" <bclegg@.clear.net.nz> wrote in message
news:#cIRcW9PEHA.3304@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Is it possible to move a column using an SQL script?
> I have looked at the Alter Table syntax help but can't see any indication
of
> ordinal control .
> I have a table that I have had to add an indentity column to.
> I want to move it to pos 0.
> My script so far is :
> alter table MasterStationTest
> drop constraint pk_masterstationtest
> go
> alter table MasterStationTest
> add id int
> IDENTITY(1,1)
> PRIMARY KEY
> go
> thanks
> Bob
>|||Although you didn't ask, Bob, it is often considered to be a flaw when we
depend on the physical ordering of the columns in a table... When I first
started doing this stuff many years ago, I tried to keep columns in tables
in a particular order, and found myself dropping/recreating tables all of
the time ( all on nights and weekends as well).
I encouraged the programmers to always use a column list and life got
better...
I'm not trying to tell you how to do your job, just making an observation
you might find helpful...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Bob Clegg" <bclegg@.clear.net.nz> wrote in message
news:%23cIRcW9PEHA.3304@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Is it possible to move a column using an SQL script?
> I have looked at the Alter Table syntax help but can't see any indication
of
> ordinal control .
> I have a table that I have had to add an indentity column to.
> I want to move it to pos 0.
> My script so far is :
> alter table MasterStationTest
> drop constraint pk_masterstationtest
> go
> alter table MasterStationTest
> add id int
> IDENTITY(1,1)
> PRIMARY KEY
> go
> thanks
> Bob
>

Alter Database Move column

Hi,
Is it possible to move a column using an SQL script?
I have looked at the Alter Table syntax help but can't see any indication of
ordinal control .
I have a table that I have had to add an indentity column to.
I want to move it to pos 0.
My script so far is :
alter table MasterStationTest
drop constraint pk_masterstationtest
go
alter table MasterStationTest
add id int
IDENTITY(1,1)
PRIMARY KEY
go
thanks
BobNo, you have to drop and re-create the table for that.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bob Clegg" <bclegg@.clear.net.nz> wrote in message news:%23cIRcW9PEHA.3304@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Is it possible to move a column using an SQL script?
> I have looked at the Alter Table syntax help but can't see any indication of
> ordinal control .
> I have a table that I have had to add an indentity column to.
> I want to move it to pos 0.
> My script so far is :
> alter table MasterStationTest
> drop constraint pk_masterstationtest
> go
> alter table MasterStationTest
> add id int
> IDENTITY(1,1)
> PRIMARY KEY
> go
> thanks
> Bob
>|||Hi,
No , You cant change the column position using ALTER table statement. The
only solution is:-
1. Create a new table with new structure with identity property
2. Insert data into the new table from actual table (Execlude the col1 which
is identiy)
insert into new_table(col2,col3...coln) select col1,col2,col3...coln from
actual_table
3. Generate Script for indexes and dependant objects
4. Verify all the data are moved successfully
5. Drop the actual table
6. Rename the new_table to actual table using (sp_rename new_table,
actual_table)
7. Create the index in the table (execute the script generated in step-3)
Note:
Enterprise manager will do the above steps when you change column postions
or add columns .....
Thanks
Hari
MCDBA
"Bob Clegg" <bclegg@.clear.net.nz> wrote in message
news:#cIRcW9PEHA.3304@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Is it possible to move a column using an SQL script?
> I have looked at the Alter Table syntax help but can't see any indication
of
> ordinal control .
> I have a table that I have had to add an indentity column to.
> I want to move it to pos 0.
> My script so far is :
> alter table MasterStationTest
> drop constraint pk_masterstationtest
> go
> alter table MasterStationTest
> add id int
> IDENTITY(1,1)
> PRIMARY KEY
> go
> thanks
> Bob
>|||Although you didn't ask, Bob, it is often considered to be a flaw when we
depend on the physical ordering of the columns in a table... When I first
started doing this stuff many years ago, I tried to keep columns in tables
in a particular order, and found myself dropping/recreating tables all of
the time ( all on nights and weekends as well).
I encouraged the programmers to always use a column list and life got
better...
I'm not trying to tell you how to do your job, just making an observation
you might find helpful...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Bob Clegg" <bclegg@.clear.net.nz> wrote in message
news:%23cIRcW9PEHA.3304@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Is it possible to move a column using an SQL script?
> I have looked at the Alter Table syntax help but can't see any indication
of
> ordinal control .
> I have a table that I have had to add an indentity column to.
> I want to move it to pos 0.
> My script so far is :
> alter table MasterStationTest
> drop constraint pk_masterstationtest
> go
> alter table MasterStationTest
> add id int
> IDENTITY(1,1)
> PRIMARY KEY
> go
> thanks
> Bob
>

Alter Database Move column

Hi,
Is it possible to move a column using an SQL script?
I have looked at the Alter Table syntax help but can't see any indication of
ordinal control .
I have a table that I have had to add an indentity column to.
I want to move it to pos 0.
My script so far is :
alter table MasterStationTest
drop constraint pk_masterstationtest
go
alter table MasterStationTest
add id int
IDENTITY(1,1)
PRIMARY KEY
go
thanks
Bob
No, you have to drop and re-create the table for that.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bob Clegg" <bclegg@.clear.net.nz> wrote in message news:%23cIRcW9PEHA.3304@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Is it possible to move a column using an SQL script?
> I have looked at the Alter Table syntax help but can't see any indication of
> ordinal control .
> I have a table that I have had to add an indentity column to.
> I want to move it to pos 0.
> My script so far is :
> alter table MasterStationTest
> drop constraint pk_masterstationtest
> go
> alter table MasterStationTest
> add id int
> IDENTITY(1,1)
> PRIMARY KEY
> go
> thanks
> Bob
>
|||Hi,
No , You cant change the column position using ALTER table statement. The
only solution is:-
1. Create a new table with new structure with identity property
2. Insert data into the new table from actual table (Execlude the col1 which
is identiy)
insert into new_table(col2,col3...coln) select col1,col2,col3...coln from
actual_table
3. Generate Script for indexes and dependant objects
4. Verify all the data are moved successfully
5. Drop the actual table
6. Rename the new_table to actual table using (sp_rename new_table,
actual_table)
7. Create the index in the table (execute the script generated in step-3)
Note:
Enterprise manager will do the above steps when you change column postions
or add columns .....
Thanks
Hari
MCDBA
"Bob Clegg" <bclegg@.clear.net.nz> wrote in message
news:#cIRcW9PEHA.3304@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Is it possible to move a column using an SQL script?
> I have looked at the Alter Table syntax help but can't see any indication
of
> ordinal control .
> I have a table that I have had to add an indentity column to.
> I want to move it to pos 0.
> My script so far is :
> alter table MasterStationTest
> drop constraint pk_masterstationtest
> go
> alter table MasterStationTest
> add id int
> IDENTITY(1,1)
> PRIMARY KEY
> go
> thanks
> Bob
>
|||Although you didn't ask, Bob, it is often considered to be a flaw when we
depend on the physical ordering of the columns in a table... When I first
started doing this stuff many years ago, I tried to keep columns in tables
in a particular order, and found myself dropping/recreating tables all of
the time ( all on nights and weekends as well).
I encouraged the programmers to always use a column list and life got
better...
I'm not trying to tell you how to do your job, just making an observation
you might find helpful...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Bob Clegg" <bclegg@.clear.net.nz> wrote in message
news:%23cIRcW9PEHA.3304@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Is it possible to move a column using an SQL script?
> I have looked at the Alter Table syntax help but can't see any indication
of
> ordinal control .
> I have a table that I have had to add an indentity column to.
> I want to move it to pos 0.
> My script so far is :
> alter table MasterStationTest
> drop constraint pk_masterstationtest
> go
> alter table MasterStationTest
> add id int
> IDENTITY(1,1)
> PRIMARY KEY
> go
> thanks
> Bob
>

Alter column to set default

I know that the correct syntax to set the default on a column in SQL Server 2005 is:

Alter Table <TableName> Add Constraint <ConstraintName> Default <DefaultValue> For <ColumnName>

But from what I can gather, the SQL-92 syntax is:

Alter Table <TableName> Alter Column <ColumnName> Set Default <DefaultValue>

This generates an error on SQL Server 2005.

Am I wrong about the standard syntax for this statement? If this is the standard, why doesn't SQL Server 2005 support it? I am trying to avoid code that will only work on certain database managers.

Thanks.

SQL Server in only entry level SQL-92 compliant. There are however some features that are full level and so on. The alter table syntax to add a default is not supported yet. If you want to use code that works on various database systems then it is best to stick to CREATE TABLE DDL with basic SQL-92 syntax. This will have a better chance of executing against more database systems. So define the defaults/constraints etc as part of the CREATE TABLE itself. You have to use ALTER TABLE on a case-by-case basis. The syntax differences are huge between ANSI SQL standard, SQL Server, Oracle and DB2 for various DDLs.

alter column syntax for enabling cascades

Hi,
I have a question about sql syntax on how to enable both
ON Delete cascade and On Update Cascade for already existing data tables.
One table contains all the users:
CREATE TABLE DBO.USERS(
USER_NAME VARCHAR(20) PRIMARY KEY,
FIRST_NAME VARCHAR(40),
LAST_NAME VARCHAR(40)
)
The next table contains user protigraphs
CREATE TABLE DBO.USER_PICTURES(
USER_NAME VARCHAR(20) PRIMARY KEY REFERENCES USERS(USER_NAME),
PICTURE IMAGE NOT NULL
)
My QUESTION is how to add both on delete cascade constraint and on update
cascade constraints to
the EXISTING USER_PICTURES(USER_NAME) FIELD, because primary key already
exists for this table.
Thank you
Vadim"Vadim" <vadim@.dontsend.com> wrote in message
news:O5YKYB1DHHA.572@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have a question about sql syntax on how to enable both
> ON Delete cascade and On Update Cascade for already existing data tables.
> One table contains all the users:
> CREATE TABLE DBO.USERS(
> USER_NAME VARCHAR(20) PRIMARY KEY,
> FIRST_NAME VARCHAR(40),
> LAST_NAME VARCHAR(40)
> )
> The next table contains user protigraphs
> CREATE TABLE DBO.USER_PICTURES(
> USER_NAME VARCHAR(20) PRIMARY KEY REFERENCES USERS(USER_NAME),
> PICTURE IMAGE NOT NULL
> )
> My QUESTION is how to add both on delete cascade constraint and on update
> cascade constraints to
> the EXISTING USER_PICTURES(USER_NAME) FIELD, because primary key already
> exists for this table.
>
You just need to drop the existing constraint and create a new one. The
only trick is finding the name of the constraint, since it is
system-generated.
sp_help user_pictures
--discover the system-generated name of the constraint
alter table USER_PICTURES drop constraint FK__USER_PICT__USER___014935CB
alter table user_pictures
add constraint fk_user_pictures_users
foreign key (user_name) references users(user_name)
on delete cascade
And don't use cascade updates, since you shouldn't be updating primary keys
in the first place.
David|||David,
Thank you for your reply.
I was hoping to be able to write a GENERAL script that would enable cascade
updates and deletes, sp_help might not be an option since the script will
have to be run at multiple customers' locations.
What could be other options? maybe to write a stored procedure that moves
the data to a temp table from the user_pictures table and properly creates
the new table and moves the data back.
I was wondering if it was possible to specify both options on delete and on
update since if user_name changes in the users table the same change would
then propagate to the user_pictures table.
Thanks again
Vadim
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23RbR%23S1DHHA.4620@.TK2MSFTNGP04.phx.gbl...
>
> "Vadim" <vadim@.dontsend.com> wrote in message
> news:O5YKYB1DHHA.572@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I have a question about sql syntax on how to enable both
>> ON Delete cascade and On Update Cascade for already existing data tables.
>> One table contains all the users:
>> CREATE TABLE DBO.USERS(
>> USER_NAME VARCHAR(20) PRIMARY KEY,
>> FIRST_NAME VARCHAR(40),
>> LAST_NAME VARCHAR(40)
>> )
>> The next table contains user protigraphs
>> CREATE TABLE DBO.USER_PICTURES(
>> USER_NAME VARCHAR(20) PRIMARY KEY REFERENCES USERS(USER_NAME),
>> PICTURE IMAGE NOT NULL
>> )
>> My QUESTION is how to add both on delete cascade constraint and on update
>> cascade constraints to
>> the EXISTING USER_PICTURES(USER_NAME) FIELD, because primary key already
>> exists for this table.
> You just need to drop the existing constraint and create a new one. The
> only trick is finding the name of the constraint, since it is
> system-generated.
> sp_help user_pictures
> --discover the system-generated name of the constraint
> alter table USER_PICTURES drop constraint FK__USER_PICT__USER___014935CB
> alter table user_pictures
> add constraint fk_user_pictures_users
> foreign key (user_name) references users(user_name)
> on delete cascade
> And don't use cascade updates, since you shouldn't be updating primary
> keys in the first place.
> David|||"Vadim" <vadim@.dontsend.com> wrote in message
news:e3tgBe1DHHA.4144@.TK2MSFTNGP06.phx.gbl...
> David,
> Thank you for your reply.
> I was hoping to be able to write a GENERAL script that would enable
> cascade updates and deletes, sp_help might not be an option since the
> script will have to be run at multiple customers' locations.
> What could be other options? maybe to write a stored procedure that moves
> the data to a temp table from the user_pictures table and properly creates
> the new table and moves the data back.
> I was wondering if it was possible to specify both options on delete and
> on update since if user_name changes in the users table the same change
> would then propagate to the user_pictures table.
>
Sure it's possible, it's just not usually a good idea to modify primary
keys. However if you are doing it, then you should probably cascade the
change.
To write a general script you will need to use a bit of dynamic SQL. Like
this:
CREATE TABLE DBO.USERS(
USER_NAME VARCHAR(20) PRIMARY KEY,
FIRST_NAME VARCHAR(40),
LAST_NAME VARCHAR(40)
)
CREATE TABLE DBO.USER_PICTURES(
USER_NAME VARCHAR(20) PRIMARY KEY REFERENCES USERS(USER_NAME),
PICTURE IMAGE NOT NULL
)
go
declare @.constraint varchar(30)
declare @.sql varchar(2000)
set @.constraint =(
select object_name(constid) from sysforeignkeys
where rkeyid = object_id('dbo.users')
and fkeyid = object_id('dbo.user_pictures')
)
set @.sql = 'alter table dbo.user_pictures drop constraint [' + @.constraint +
']'
exec (@.sql)
go
alter table user_pictures
add constraint fk_user_pictures_users
foreign key (user_name) references users(user_name)
on delete cascade
on update cascade
David|||David,
Thank you very much for such a detailed reply, This is what I was looking
for.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:um%23s%23s1DHHA.1748@.TK2MSFTNGP02.phx.gbl...
>
> "Vadim" <vadim@.dontsend.com> wrote in message
> news:e3tgBe1DHHA.4144@.TK2MSFTNGP06.phx.gbl...
>> David,
>> Thank you for your reply.
>> I was hoping to be able to write a GENERAL script that would enable
>> cascade updates and deletes, sp_help might not be an option since the
>> script will have to be run at multiple customers' locations.
>> What could be other options? maybe to write a stored procedure that moves
>> the data to a temp table from the user_pictures table and properly
>> creates the new table and moves the data back.
>> I was wondering if it was possible to specify both options on delete and
>> on update since if user_name changes in the users table the same change
>> would then propagate to the user_pictures table.
> Sure it's possible, it's just not usually a good idea to modify primary
> keys. However if you are doing it, then you should probably cascade the
> change.
>
> To write a general script you will need to use a bit of dynamic SQL. Like
> this:
> CREATE TABLE DBO.USERS(
> USER_NAME VARCHAR(20) PRIMARY KEY,
> FIRST_NAME VARCHAR(40),
> LAST_NAME VARCHAR(40)
> )
>
> CREATE TABLE DBO.USER_PICTURES(
> USER_NAME VARCHAR(20) PRIMARY KEY REFERENCES USERS(USER_NAME),
> PICTURE IMAGE NOT NULL
> )
> go
> declare @.constraint varchar(30)
> declare @.sql varchar(2000)
> set @.constraint => (
> select object_name(constid) from sysforeignkeys
> where rkeyid = object_id('dbo.users')
> and fkeyid = object_id('dbo.user_pictures')
> )
> set @.sql = 'alter table dbo.user_pictures drop constraint [' + @.constraint
> + ']'
> exec (@.sql)
> go
> alter table user_pictures
> add constraint fk_user_pictures_users
> foreign key (user_name) references users(user_name)
> on delete cascade
> on update cascade
> David|||... and remember to name your constraints in the future... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Vadim" <vadim@.dontsend.com> wrote in message news:%23ir%234z1DHHA.3836@.TK2MSFTNGP02.phx.gbl...
> David,
> Thank you very much for such a detailed reply, This is what I was looking
> for.
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:um%23s%23s1DHHA.1748@.TK2MSFTNGP02.phx.gbl...
>>
>> "Vadim" <vadim@.dontsend.com> wrote in message
>> news:e3tgBe1DHHA.4144@.TK2MSFTNGP06.phx.gbl...
>> David,
>> Thank you for your reply.
>> I was hoping to be able to write a GENERAL script that would enable
>> cascade updates and deletes, sp_help might not be an option since the
>> script will have to be run at multiple customers' locations.
>> What could be other options? maybe to write a stored procedure that moves
>> the data to a temp table from the user_pictures table and properly
>> creates the new table and moves the data back.
>> I was wondering if it was possible to specify both options on delete and
>> on update since if user_name changes in the users table the same change
>> would then propagate to the user_pictures table.
>> Sure it's possible, it's just not usually a good idea to modify primary
>> keys. However if you are doing it, then you should probably cascade the
>> change.
>>
>> To write a general script you will need to use a bit of dynamic SQL. Like
>> this:
>> CREATE TABLE DBO.USERS(
>> USER_NAME VARCHAR(20) PRIMARY KEY,
>> FIRST_NAME VARCHAR(40),
>> LAST_NAME VARCHAR(40)
>> )
>>
>> CREATE TABLE DBO.USER_PICTURES(
>> USER_NAME VARCHAR(20) PRIMARY KEY REFERENCES USERS(USER_NAME),
>> PICTURE IMAGE NOT NULL
>> )
>> go
>> declare @.constraint varchar(30)
>> declare @.sql varchar(2000)
>> set @.constraint =>> (
>> select object_name(constid) from sysforeignkeys
>> where rkeyid = object_id('dbo.users')
>> and fkeyid = object_id('dbo.user_pictures')
>> )
>> set @.sql = 'alter table dbo.user_pictures drop constraint [' + @.constraint
>> + ']'
>> exec (@.sql)
>> go
>> alter table user_pictures
>> add constraint fk_user_pictures_users
>> foreign key (user_name) references users(user_name)
>> on delete cascade
>> on update cascade
>> David
>

Saturday, February 25, 2012

ALTER COLUMN

Hi all,
I am trying to alter a column with the following statement.
Can anyone help me get the syntax correct..
ALTER TABLE asmt_v1_question_passed ALTER COLUMN pass_dt SET DEFAULT
getdate()
Cheers,
AdamTry
ALTER TABLE dbo.asmt_v1_question_passed ADD CONSTRAINT
DF_C_Name DEFAULT CURRENT_TIMESTAM FOR pass_dt
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Adam Knight" <dev@.brightidea.com.au> wrote in message
news:ugFl8Qs2FHA.3272@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I am trying to alter a column with the following statement.
> Can anyone help me get the syntax correct..
> ALTER TABLE asmt_v1_question_passed ALTER COLUMN pass_dt SET DEFAULT
> getdate()
> Cheers,
> Adam
>|||Hi Adam ,
I think it can be a solution for your problem :
ALTER TABLE asmt_v1_question_passed WITH NOCHECK ADD
DEFAULT getdate() FOR [pass_dt]
"Adam Knight" wrote:

> Hi all,
> I am trying to alter a column with the following statement.
> Can anyone help me get the syntax correct..
> ALTER TABLE asmt_v1_question_passed ALTER COLUMN pass_dt SET DEFAULT
> getdate()
> Cheers,
> Adam
>
>

Tuesday, March 27, 2012

Altering column names?

Sunday, March 25, 2012

ALTER USER WITH LOGIN

ALTER USER WITH LOGIN

ALTER USER WITH LOGIN

ALTER TABLE/COLUMN syntax

ALTER TABLE/COLUMN syntax

Thursday, March 22, 2012

ALTER TABLE statement to lengthen column

Thursday, March 8, 2012

Alter Index with REBUILD on master database?

alter index syntax

alter index syntax

Alter Identity Column question

alter database syntax

Wednesday, March 7, 2012

Alter Database Move column

Alter Database Move column

Alter Database Move column

Alter column to set default

alter column syntax for enabling cascades

alter column syntax for enabling cascades

alter column syntax for enabling cascades

Saturday, February 25, 2012

ALTER COLUMN

Sql Server Alignment problem

Blog Archive

About Me