Altering columns...getting complicated...

I have a need to write many scripts to alter a LOT the underlying database
structure of our database in the field. We have many fields out there with
a type of float, and I've been told to change those to numeric(19,5) -- easy
enough. Unless there is a constraint, in which case I have to drop the
constraint, alter the field, add the constraint back in. Easy enough again,
once you know what you are doing.
Now, they tell me to change all the nvarchar(XX) fields to varchar(XX) --
easy enough again, unless they have a default -- use the same scheme as
above, and it all works. UNLESS they are part of a primary key. Uh oh --
now I hit something I don't know how to solve...
What I'm thinking is that I should dump all of the indexes and primary keys
and defaults out of all tables, and then just rebuild them all from scratch.
However, this database was "created" by using the Access upsizing wizard, so
I don't know all the primary key names, constraint names, etc.
Can anyone point me in the right direction to dump all indexes and defaults
on every column in a database? I can re-create them pretty easily...
Any advice would be appreciated, or even an alternate method to do what I
need to do.
Thanks in advance.
Matt
In message <OU#WxZ8WFHA.2420@.TK2MSFTNGP12.phx.gbl>, YYZ <none@.none.com>
writes
>I have a need to write many scripts to alter a LOT the underlying database
>structure of our database in the field. We have many fields out there with
>a type of float, and I've been told to change those to numeric(19,5) -- easy
>enough. Unless there is a constraint, in which case I have to drop the
>constraint, alter the field, add the constraint back in. Easy enough again,
>once you know what you are doing.
>Now, they tell me to change all the nvarchar(XX) fields to varchar(XX) --
>easy enough again, unless they have a default -- use the same scheme as
>above, and it all works. UNLESS they are part of a primary key. Uh oh --
>now I hit something I don't know how to solve...
>What I'm thinking is that I should dump all of the indexes and primary keys
>and defaults out of all tables, and then just rebuild them all from scratch.
>However, this database was "created" by using the Access upsizing wizard, so
>I don't know all the primary key names, constraint names, etc.
>Can anyone point me in the right direction to dump all indexes and defaults
>on every column in a database? I can re-create them pretty easily...
>Any advice would be appreciated, or even an alternate method to do what I
>need to do.
>
Use a CURSOR to enumerate the SYSINDEXES system table in your database
to find all the indexes on it. Alternatively, if you tied this up with
the INFORMATION_SCHEMA.TABLES you can list the indexes on a table by
table basis.
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com
|||hi Matt,
YYZ wrote:
> I have a need to write many scripts to alter a LOT the underlying
> database structure of our database in the field. We have many fields
> out there with a type of float, and I've been told to change those to
> numeric(19,5) -- easy enough. Unless there is a constraint, in which
> case I have to drop the constraint, alter the field, add the
> constraint back in. Easy enough again, once you know what you are
> doing.
> Now, they tell me to change all the nvarchar(XX) fields to
> varchar(XX) -- easy enough again, unless they have a default -- use
> the same scheme as above, and it all works. UNLESS they are part of
> a primary key. Uh oh -- now I hit something I don't know how to
> solve...
> What I'm thinking is that I should dump all of the indexes and
> primary keys and defaults out of all tables, and then just rebuild
> them all from scratch. However, this database was "created" by using
> the Access upsizing wizard, so I don't know all the primary key
> names, constraint names, etc.
> Can anyone point me in the right direction to dump all indexes and
> defaults on every column in a database? I can re-create them pretty
> easily...
> Any advice would be appreciated, or even an alternate method to do
> what I need to do.
you can perhaps search www.sqlservercentral.com... there's plenty of
maintenance scripts...
ie: http://www.sqlservercentral.com/scri...utions/935.asp to drop
and recreate all indexes on a db..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Altering column fields with a Stored Procedure

I have some columns of data in SQL server that are of NVARCHAR(420)
format but they are dates. The dates are in DD/MM/YY format. I want to
be able to convert them to our accounting system format which is
YYYYMMDD. I know the format is strange but it will make things easier
in the long run if all of the dates are the same when working between
the 2 different databases. Basically, I need to take a look at the
year portion (with a SUBSTRING function maybe) to see if it is greater
than 50 (there will not be any dates that are less than 1950) and if
it is concatenate 19 with it (ex. 65 = 1965). Then, concatenate the
month and day from the rest to form the date we need in NUMERIC(8).
So, a date of January 17, 2003 (currently in the format of 17/01/03)
would become 20030117. In VB, the function I would write is something
like the following:
/*
Dim sCurrentDate as String
Dim sMon as string
Dim sDay as String
Dim sYear as String
Dim sNewDate as String

sCurrentDate = "17/01/03"
sMon = Mid(sCurrentDate, 4, 2)
sDay = Mid(sCurrentDate, 1, 2)
sYear = Mid(sCurrentDate, 7, 2)

If sYear < 50 Then
sYear = "20" & sYear
ElseIf sYear > 50 Then
sYear = "19" & sYear
End if
sNewDate = sYear & sMon & sDay
*/
I was thinking of doing this in a Stored Procedure but am really rusty
with SQL (it's been since college).

The datatype would end up being NUMERIC(8). How I would write it if I
new how to write it would be: grab the column name prior to the
procedure, create a temp column, format the values, place them into
the temp column, delete the old column, and then rename the temp
column to the name of the column that I grabbed in the beginning of
the procedure. Most likely this is the only way to do it but I have no
idea how to go about it.mwoodward@.quinnpumps.com (Milo Woodward) wrote in message news:<1615a5e3.0308142112.30d6548c@.posting.google.com>...
> I have some columns of data in SQL server that are of NVARCHAR(420)
> format but they are dates. The dates are in DD/MM/YY format. I want to
> be able to convert them to our accounting system format which is
> YYYYMMDD. I know the format is strange but it will make things easier
> in the long run if all of the dates are the same when working between
> the 2 different databases. Basically, I need to take a look at the
> year portion (with a SUBSTRING function maybe) to see if it is greater
> than 50 (there will not be any dates that are less than 1950) and if
> it is concatenate 19 with it (ex. 65 = 1965). Then, concatenate the
> month and day from the rest to form the date we need in NUMERIC(8).
> So, a date of January 17, 2003 (currently in the format of 17/01/03)
> would become 20030117. In VB, the function I would write is something
> like the following:
> /*
> Dim sCurrentDate as String
> Dim sMon as string
> Dim sDay as String
> Dim sYear as String
> Dim sNewDate as String
> sCurrentDate = "17/01/03"
> sMon = Mid(sCurrentDate, 4, 2)
> sDay = Mid(sCurrentDate, 1, 2)
> sYear = Mid(sCurrentDate, 7, 2)
> If sYear < 50 Then
> sYear = "20" & sYear
> ElseIf sYear > 50 Then
> sYear = "19" & sYear
> End if
> sNewDate = sYear & sMon & sDay
> */
> I was thinking of doing this in a Stored Procedure but am really rusty
> with SQL (it's been since college).
> The datatype would end up being NUMERIC(8). How I would write it if I
> new how to write it would be: grab the column name prior to the
> procedure, create a temp column, format the values, place them into
> the temp column, delete the old column, and then rename the temp
> column to the name of the column that I grabbed in the beginning of
> the procedure. Most likely this is the only way to do it but I have no
> idea how to go about it.

I strongly suggest that you rethink your approach, and change the
column to datetime. You can then do date calculations using the
standard functions (DATEADD etc.), compare the values to datetime
variables without conversion, etc. You can use CONVERT() to extract
dates in a particular format for passing to other systems.

Using numeric will give you serious problems in the long run, although
I appreciate that you may have limited control over the data model.
But if you really have no option but to use numeric, then this should
work (assuming that as you said, all dates are 1950 or later):

update dbo.MyTable
set DateColumn = convert(char(8), convert(datetime, DateColumn, 3),
112)

alter table dbo.MyTable
alter column DateColumn numeric(8)

Simon

ALTER TABLE to add NOT NULL fields

I'm using the following statement to create two new fields in a table:
ALTER TABLE tblActivity ADD LOGUserID [INT] NOT NULL, LOGDATE [DATETIME] NOT
NULL
When I run it in QA, I get this error message:
ALTER TABLE only allows columns to be added that can contain nulls or have a
DEFAULT definition specified. Column 'LOGUserID' cannot be added to table
'tblActivity' because it does not allow nulls and does not specify a DEFAULT
definition.
How can I get those two fields to be created via the ALTER TABLE statement,
I can not use Enterprise Manager, since the statement that I'm using is bein
g
generated by another script to add those fields to all the tables in the
database...You need to specify a default value...
ALTER TABLE tblActivity ADD LOGUserID [INT] NOT NULL, LOGDATE [DATETIME] NOT
NULL
DEFAULT( 0 )
Make sure whatever default value you specify is meaningful to your
application; also, you could drop the DEFAULT constraint afterward...
ALTER TABLE ... DROP CONSTRAINT ...
Example...
create table t (
id int null )
insert t values( 1 )
insert t values( null )
go
alter table t add mycol int not null default( 0 )
go
sp_help t
go
alter table t drop constraint DF__t__mycol__781FBE44
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"scuba79" <scuba79@.discussions.microsoft.com> wrote in message
news:EB8AF4A5-1217-4ADD-8E41-291A1E812B46@.microsoft.com...
> I'm using the following statement to create two new fields in a table:
> ALTER TABLE tblActivity ADD LOGUserID [INT] NOT NULL, LOGDATE [DATETIME]
> NOT
> NULL
> When I run it in QA, I get this error message:
> ALTER TABLE only allows columns to be added that can contain nulls or have
> a
> DEFAULT definition specified. Column 'LOGUserID' cannot be added to table
> 'tblActivity' because it does not allow nulls and does not specify a
> DEFAULT
> definition.
> How can I get those two fields to be created via the ALTER TABLE
> statement,
> I can not use Enterprise Manager, since the statement that I'm using is
> being
> generated by another script to add those fields to all the tables in the
> database...|||To add on to Tony's response, you can specify an explicit constraint name to
make subsequent table maintenance easier:
ALTER TABLE tblActivity
ADD
LOGUserID [INT] NOT NULL
CONSTRAINT DF_tblActivity_LOGUserID DEFAULT( 0 ),
LOGDATE [DATETIME] NOT NULL
CONSTRAINT DF_tblActivity_LOGDATE DEFAULT( GETDATE() )
Hope this helps.
Dan Guzman
SQL Server MVP
"scuba79" <scuba79@.discussions.microsoft.com> wrote in message
news:EB8AF4A5-1217-4ADD-8E41-291A1E812B46@.microsoft.com...
> I'm using the following statement to create two new fields in a table:
> ALTER TABLE tblActivity ADD LOGUserID [INT] NOT NULL, LOGDATE [DATETIME]
> NOT
> NULL
> When I run it in QA, I get this error message:
> ALTER TABLE only allows columns to be added that can contain nulls or have
> a
> DEFAULT definition specified. Column 'LOGUserID' cannot be added to table
> 'tblActivity' because it does not allow nulls and does not specify a
> DEFAULT
> definition.
> How can I get those two fields to be created via the ALTER TABLE
> statement,
> I can not use Enterprise Manager, since the statement that I'm using is
> being
> generated by another script to add those fields to all the tables in the
> database...sql

Alter Table add Column - How do you add a column after say the second column

When you use "Alter Table add Column", it adds the column to the end of the list of fields.

How do you insert the new column to position number 2 for instance given that you may have more than 2 columns?

Create table T1 ( a varchar(20), b varchar(20), c varchar(20))

Alter table add column x varchar(20)

so that the resulting table is

T1 a varchar(20), x varchar(20), b varchar(20), c varchar(20)

Can this be done programmatically?

One option is to do the following (but it might take a while if you have lots of data):

- insert all data in a temp table

- drop the original table

- create a table with the new column in place

- insert all the data from the temp table in the new table

Make sure you take into account all constraints, indexes, triggers, ...

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||Order of columns is irrelevant in a table. You can retrieve columns in a specific order when you query the table. It doesn't have to be physically added in the same order and the internal row format also doesn't store it in the order you specified in the CREATE TABLE. So why do you care where the column is in the CREATE TABLE? What are you trying to do with that information?|||

I was under the impression some SQL scripts would break. Am I mistaken?

insert into T1 select * from T2 ( I do run into these SQL scripts)

How does the Enterprise Manager insert a column into the table without having to drop the table and re-creating it?

Thanks.

|||

If you alter either T1 or T2, then you need to address the query anyway.

Enterprise manager copies the data out, recreates the table with the column in the position you designated, then copies the data back in....much as was described in an earlier post.

|||

I was hoping that was able to run a script that would be able to add a column (let's say column in the second position)

to both T1 and T2 so that I wouldn't have to searching for all the previous scripts that would break.

When you do use a script to Alter a table the column is added to the end. I end up having to go to the Enterprise Manager and drag and drop the column that is at the end of the table to the 2nd position.

I don't think the Enterprise Manager is recreating the table at this time. It should be simply moving the pointers in syscolumns for instance. I am just guessing, but that would be more` efficient.

|||

Then you are quite 'wrong' with your 'guess'.

Enterprise Managler most definitely

creates a new table with the column order you desire,

transfers all the data to the new table,

scripts out and adds the necessary constraints,

drops the old table,

and then renames the new table to the same name as the old table.

Alter Table add Column - How do you add a column after say the second column

When you use "Alter Table add Column", it adds the column to the end of the list of fields.

How do you insert the new column to position number 2 for instance given that you may have more than 2 columns?

Create table T1 ( a varchar(20), b varchar(20), c varchar(20))

Alter table add column x varchar(20)

so that the resulting table is

T1 a varchar(20), x varchar(20), b varchar(20), c varchar(20)

Can this be done programmatically?

One option is to do the following (but it might take a while if you have lots of data):

- insert all data in a temp table

- drop the original table

- create a table with the new column in place

- insert all the data from the temp table in the new table

Make sure you take into account all constraints, indexes, triggers, ...

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||Order of columns is irrelevant in a table. You can retrieve columns in a specific order when you query the table. It doesn't have to be physically added in the same order and the internal row format also doesn't store it in the order you specified in the CREATE TABLE. So why do you care where the column is in the CREATE TABLE? What are you trying to do with that information?|||

I was under the impression some SQL scripts would break. Am I mistaken?

insert into T1 select * from T2 ( I do run into these SQL scripts)

How does the Enterprise Manager insert a column into the table without having to drop the table and re-creating it?

Thanks.

|||

If you alter either T1 or T2, then you need to address the query anyway.

Enterprise manager copies the data out, recreates the table with the column in the position you designated, then copies the data back in....much as was described in an earlier post.

|||

I was hoping that was able to run a script that would be able to add a column (let's say column in the second position)

to both T1 and T2 so that I wouldn't have to searching for all the previous scripts that would break.

When you do use a script to Alter a table the column is added to the end. I end up having to go to the Enterprise Manager and drag and drop the column that is at the end of the table to the 2nd position.

I don't think the Enterprise Manager is recreating the table at this time. It should be simply moving the pointers in syscolumns for instance. I am just guessing, but that would be more` efficient.

|||

Then you are quite 'wrong' with your 'guess'.

Enterprise Managler most definitely

creates a new table with the column order you desire,

transfers all the data to the new table,

scripts out and adds the necessary constraints,

drops the old table,

and then renames the new table to the same name as the old table.

Alter Table - Add Colum - Ordinal Position?

I am using SQL Server 2000. As part of a new development project I find that
I frequently need to add new columns/fields to existing tables using SQL
Scripts in Query Analyzer. I would like to add the new column at a specific
Ordinal Position but when I use the standard Alter Table Add ... syntax for
adding a new column the column always gets added to the end of the table.
Accomplishing this task is relatively easy using Enterprise Manager, you
simply select the column where you want the new column to be placed and click
"Insert" and the new column gets inserted above the selected column. Is
there a way to add the column in a specific ordinal location from Query
Analyzer? If so, what is the syntax to make this happen. For example, I
want to add a new column ([columntwo] [int] NULL,) between [columnone] and
[columnthree] to the table layout below:
ColumnOne
ColumnThree
ColumnFour
Using standard "alter table' syntax [columntwo] get added to the bottom.
ColumnOne
ColumnThree
ColumnFour
ColumnTwo
I want it to look like this:
ColumnOne
ColumnTwo
ColumnThree
ColumnFour
I realize I can simply create a new table with the properly ordered fields,
copy the data over to the new table and then drop the existing table but this
is approach is not optimal.
So Much - Yet - So Little
Try running Profiler when you insert the column in Enterprise Manager. I
think you will find it does exactly what you said you don't want to do -
creates a new table, inserts the data from the old table, and then droops
the old table. The right thing to do here is to write your SQL statements
to not depend on a particular column order so that you can use Alter Table.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"SwingVoter" <swingvoter@.nospam.nospam> wrote in message
news:91DDD1FB-961D-4976-9C36-C2EC3AE02C66@.microsoft.com...
>I am using SQL Server 2000. As part of a new development project I find
>that
> I frequently need to add new columns/fields to existing tables using SQL
> Scripts in Query Analyzer. I would like to add the new column at a
> specific
> Ordinal Position but when I use the standard Alter Table Add ... syntax
> for
> adding a new column the column always gets added to the end of the table.
> Accomplishing this task is relatively easy using Enterprise Manager, you
> simply select the column where you want the new column to be placed and
> click
> "Insert" and the new column gets inserted above the selected column. Is
> there a way to add the column in a specific ordinal location from Query
> Analyzer? If so, what is the syntax to make this happen. For example, I
> want to add a new column ([columntwo] [int] NULL,) between [columnone] and
> [columnthree] to the table layout below:
> ColumnOne
> ColumnThree
> ColumnFour
> Using standard "alter table' syntax [columntwo] get added to the bottom.
> ColumnOne
> ColumnThree
> ColumnFour
> ColumnTwo
> I want it to look like this:
> ColumnOne
> ColumnTwo
> ColumnThree
> ColumnFour
> I realize I can simply create a new table with the properly ordered
> fields,
> copy the data over to the new table and then drop the existing table but
> this
> is approach is not optimal.
> --
> So Much - Yet - So Little
|||Hi,
How many rows there in your table?
Creating a new table and dropping old one is only optimal option. This can
be achieved through Enterprose Manageralso , right click the table => choose
"Design Table" and drag and drop the new cloumn created by you from the end
position to desired Ordinal Position. But go for this only if the table rows
are only a few hundreds or else it will hang.
Thanks,
Sree
"Roger Wolter[MSFT]" wrote:

> Try running Profiler when you insert the column in Enterprise Manager. I
> think you will find it does exactly what you said you don't want to do -
> creates a new table, inserts the data from the old table, and then droops
> the old table. The right thing to do here is to write your SQL statements
> to not depend on a particular column order so that you can use Alter Table.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "SwingVoter" <swingvoter@.nospam.nospam> wrote in message
> news:91DDD1FB-961D-4976-9C36-C2EC3AE02C66@.microsoft.com...
>
>
|||Hi Roger,
ALTER TABLE tab ADD COLUMN newcol [ AFTER | BEFORE ] curcol, just like
mysql does is there any option in Sql Server 2005.
Thanks,
Sree
"Roger Wolter[MSFT]" wrote:

> Try running Profiler when you insert the column in Enterprise Manager. I
> think you will find it does exactly what you said you don't want to do -
> creates a new table, inserts the data from the old table, and then droops
> the old table. The right thing to do here is to write your SQL statements
> to not depend on a particular column order so that you can use Alter Table.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "SwingVoter" <swingvoter@.nospam.nospam> wrote in message
> news:91DDD1FB-961D-4976-9C36-C2EC3AE02C66@.microsoft.com...
>
>
|||> ALTER TABLE tab ADD COLUMN newcol [ AFTER | BEFORE ] curcol, just like
> mysql does is there any option in Sql Server 2005.
You can submit this product enhancement request directly at
http://lab.msdn.microsoft.com/productfeedback/. Also specify the business
case for why this feature is important to you.
Hope this helps.
Dan Guzman
SQL Server MVP
"Sreejith G" <SreejithG@.discussions.microsoft.com> wrote in message
news:1AD84F7E-9603-48DF-812E-D332EC40DC49@.microsoft.com...[vbcol=seagreen]
> Hi Roger,
> ALTER TABLE tab ADD COLUMN newcol [ AFTER | BEFORE ] curcol, just like
> mysql does is there any option in Sql Server 2005.
> Thanks,
> Sree
> "Roger Wolter[MSFT]" wrote:
|||It seems that there would be a script that is available to simply change the
ordinal position of the field for that table in the master table as part of
the alter table script. I am not sure of the impacts of this method. Has
anyone used this approach.
So Much - Yet - So Little
"Roger Wolter[MSFT]" wrote:

> Try running Profiler when you insert the column in Enterprise Manager. I
> think you will find it does exactly what you said you don't want to do -
> creates a new table, inserts the data from the old table, and then droops
> the old table. The right thing to do here is to write your SQL statements
> to not depend on a particular column order so that you can use Alter Table.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "SwingVoter" <swingvoter@.nospam.nospam> wrote in message
> news:91DDD1FB-961D-4976-9C36-C2EC3AE02C66@.microsoft.com...
>
>
|||Hi,
As Roger metioned, In SQL server, you need to create a new table, inserts
the data from the old table, and then drop the old table.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Alter Table - Add Colum - Ordinal Position?
>thread-index: AcYtkhaya59bWEyOR5ylpD9wDQSW8A==
>X-WBNR-Posting-Host: 207.59.213.130
>From: "=?Utf-8?B?U3dpbmdWb3Rlcg==?=" <swingvoter@.nospam.nospam>
>References: <91DDD1FB-961D-4976-9C36-C2EC3AE02C66@.microsoft.com>
<#JBE6aTLGHA.3164@.TK2MSFTNGP11.phx.gbl>
>Subject: Re: Alter Table - Add Colum - Ordinal Position?
>Date: Thu, 9 Feb 2006 08:01:29 -0800
>Lines: 66
>Message-ID: <C2BE3CB0-839A-4CE9-9FDE-96BC31931745@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:420560
>X-Tomcat-NG: microsoft.public.sqlserver.server
> It seems that there would be a script that is available to simply change
the
>ordinal position of the field for that table in the master table as part
of[vbcol=seagreen]
>the alter table script. I am not sure of the impacts of this method. Has
>anyone used this approach.
>--
>So Much - Yet - So Little
>
>"Roger Wolter[MSFT]" wrote:
I[vbcol=seagreen]
droops[vbcol=seagreen]
statements[vbcol=seagreen]
Table.[vbcol=seagreen]
rights.[vbcol=seagreen]
find[vbcol=seagreen]
SQL[vbcol=seagreen]
syntax[vbcol=seagreen]
table.[vbcol=seagreen]
you[vbcol=seagreen]
and[vbcol=seagreen]
Is[vbcol=seagreen]
example, I[vbcol=seagreen]
and[vbcol=seagreen]
bottom.[vbcol=seagreen]
but
>

Alter Table - Add Colum - Ordinal Position?

I am using SQL Server 2000. As part of a new development project I find tha
t
I frequently need to add new columns/fields to existing tables using SQL
Scripts in Query Analyzer. I would like to add the new column at a specific
Ordinal Position but when I use the standard Alter Table Add ... syntax for
adding a new column the column always gets added to the end of the table.
Accomplishing this task is relatively easy using Enterprise Manager, you
simply select the column where you want the new column to be placed and clic
k
"Insert" and the new column gets inserted above the selected column. Is
there a way to add the column in a specific ordinal location from Query
Analyzer? If so, what is the syntax to make this happen. For example, I
want to add a new column ([columntwo] [int] NULL,) between [colu
mnone] and
[columnthree] to the table layout below:
ColumnOne
ColumnThree
ColumnFour
Using standard "alter table' syntax [columntwo] get added to the bottom.
ColumnOne
ColumnThree
ColumnFour
ColumnTwo
I want it to look like this:
ColumnOne
ColumnTwo
ColumnThree
ColumnFour
I realize I can simply create a new table with the properly ordered fields,
copy the data over to the new table and then drop the existing table but thi
s
is approach is not optimal.
--
So Much - Yet - So LittleTry running Profiler when you insert the column in Enterprise Manager. I
think you will find it does exactly what you said you don't want to do -
creates a new table, inserts the data from the old table, and then droops
the old table. The right thing to do here is to write your SQL statements
to not depend on a particular column order so that you can use Alter Table.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"SwingVoter" <swingvoter@.nospam.nospam> wrote in message
news:91DDD1FB-961D-4976-9C36-C2EC3AE02C66@.microsoft.com...
>I am using SQL Server 2000. As part of a new development project I find
>that
> I frequently need to add new columns/fields to existing tables using SQL
> Scripts in Query Analyzer. I would like to add the new column at a
> specific
> Ordinal Position but when I use the standard Alter Table Add ... syntax
> for
> adding a new column the column always gets added to the end of the table.
> Accomplishing this task is relatively easy using Enterprise Manager, you
> simply select the column where you want the new column to be placed and
> click
> "Insert" and the new column gets inserted above the selected column. Is
> there a way to add the column in a specific ordinal location from Query
> Analyzer? If so, what is the syntax to make this happen. For example, I
> want to add a new column ([columntwo] [int] NULL,) between [co
lumnone] and
> [columnthree] to the table layout below:
> ColumnOne
> ColumnThree
> ColumnFour
> Using standard "alter table' syntax [columntwo] get added to the botto
m.
> ColumnOne
> ColumnThree
> ColumnFour
> ColumnTwo
> I want it to look like this:
> ColumnOne
> ColumnTwo
> ColumnThree
> ColumnFour
> I realize I can simply create a new table with the properly ordered
> fields,
> copy the data over to the new table and then drop the existing table but
> this
> is approach is not optimal.
> --
> So Much - Yet - So Little|||Hi,
How many rows there in your table?
Creating a new table and dropping old one is only optimal option. This can
be achieved through Enterprose Manageralso , right click the table => choose
"Design Table" and drag and drop the new cloumn created by you from the end
position to desired Ordinal Position. But go for this only if the table rows
are only a few hundreds or else it will hang.
Thanks,
Sree
"Roger Wolter[MSFT]" wrote:

> Try running Profiler when you insert the column in Enterprise Manager. I
> think you will find it does exactly what you said you don't want to do -
> creates a new table, inserts the data from the old table, and then droops
> the old table. The right thing to do here is to write your SQL statements
> to not depend on a particular column order so that you can use Alter Table
.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "SwingVoter" <swingvoter@.nospam.nospam> wrote in message
> news:91DDD1FB-961D-4976-9C36-C2EC3AE02C66@.microsoft.com...
>
>|||Hi Roger,
ALTER TABLE tab ADD COLUMN newcol [ AFTER | BEFORE ] curcol, just like
mysql does is there any option in Sql Server 2005.
Thanks,
Sree
"Roger Wolter[MSFT]" wrote:

> Try running Profiler when you insert the column in Enterprise Manager. I
> think you will find it does exactly what you said you don't want to do -
> creates a new table, inserts the data from the old table, and then droops
> the old table. The right thing to do here is to write your SQL statements
> to not depend on a particular column order so that you can use Alter Table
.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "SwingVoter" <swingvoter@.nospam.nospam> wrote in message
> news:91DDD1FB-961D-4976-9C36-C2EC3AE02C66@.microsoft.com...
>
>|||> ALTER TABLE tab ADD COLUMN newcol [ AFTER | BEFORE ] curcol, just like
> mysql does is there any option in Sql Server 2005.
You can submit this product enhancement request directly at
http://lab.msdn.microsoft.com/productfeedback/. Also specify the business
case for why this feature is important to you.
Hope this helps.
Dan Guzman
SQL Server MVP
"Sreejith G" <SreejithG@.discussions.microsoft.com> wrote in message
news:1AD84F7E-9603-48DF-812E-D332EC40DC49@.microsoft.com...[vbcol=seagreen]
> Hi Roger,
> ALTER TABLE tab ADD COLUMN newcol [ AFTER | BEFORE ] curcol, just like
> mysql does is there any option in Sql Server 2005.
> Thanks,
> Sree
> "Roger Wolter[MSFT]" wrote:
>|||It seems that there would be a script that is available to simply change the
ordinal position of the field for that table in the master table as part of
the alter table script. I am not sure of the impacts of this method. Has
anyone used this approach.
So Much - Yet - So Little
"Roger Wolter[MSFT]" wrote:

> Try running Profiler when you insert the column in Enterprise Manager. I
> think you will find it does exactly what you said you don't want to do -
> creates a new table, inserts the data from the old table, and then droops
> the old table. The right thing to do here is to write your SQL statements
> to not depend on a particular column order so that you can use Alter Table
.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "SwingVoter" <swingvoter@.nospam.nospam> wrote in message
> news:91DDD1FB-961D-4976-9C36-C2EC3AE02C66@.microsoft.com...
>
>|||Hi,
As Roger metioned, In SQL server, you need to create a new table, inserts
the data from the old table, and then drop the old table.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Alter Table - Add Colum - Ordinal Position'
>thread-index: AcYtkhaya59bWEyOR5ylpD9wDQSW8A==
>X-WBNR-Posting-Host: 207.59.213.130
>From: "examnotes" <swingvoter@.nospam.nospam>
>References: <91DDD1FB-961D-4976-9C36-C2EC3AE02C66@.microsoft.com>
<#JBE6aTLGHA.3164@.TK2MSFTNGP11.phx.gbl>
>Subject: Re: Alter Table - Add Colum - Ordinal Position'
>Date: Thu, 9 Feb 2006 08:01:29 -0800
>Lines: 66
>Message-ID: <C2BE3CB0-839A-4CE9-9FDE-96BC31931745@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:420560
>X-Tomcat-NG: microsoft.public.sqlserver.server
> It seems that there would be a script that is available to simply change
the
>ordinal position of the field for that table in the master table as part
of
>the alter table script. I am not sure of the impacts of this method. Has
>anyone used this approach.
>--
>So Much - Yet - So Little
>
>"Roger Wolter[MSFT]" wrote:
>
I[vbcol=seagreen]
droops[vbcol=seagreen]
statements[vbcol=seagreen]
Table.[vbcol=seagreen]
rights.[vbcol=seagreen]
find[vbcol=seagreen]
SQL[vbcol=seagreen]
syntax[vbcol=seagreen]
table.[vbcol=seagreen]
you[vbcol=seagreen]
and[vbcol=seagreen]
Is[vbcol=seagreen]
example, I[vbcol=seagreen]
and[vbcol=seagreen]
bottom.[vbcol=seagreen]
but[vbcol=seagreen]
>

Alter Table - Add Colum - Ordinal Position?

I am using SQL Server 2000. As part of a new development project I find that
I frequently need to add new columns/fields to existing tables using SQL
Scripts in Query Analyzer. I would like to add the new column at a specific
Ordinal Position but when I use the standard Alter Table Add ... syntax for
adding a new column the column always gets added to the end of the table.
Accomplishing this task is relatively easy using Enterprise Manager, you
simply select the column where you want the new column to be placed and click
"Insert" and the new column gets inserted above the selected column. Is
there a way to add the column in a specific ordinal location from Query
Analyzer? If so, what is the syntax to make this happen. For example, I
want to add a new column ([columntwo] [int] NULL,) between [columnone] and
[columnthree] to the table layout below:
ColumnOne
ColumnThree
ColumnFour
Using standard "alter table' syntax [columntwo] get added to the bottom.
ColumnOne
ColumnThree
ColumnFour
ColumnTwo
I want it to look like this:
ColumnOne
ColumnTwo
ColumnThree
ColumnFour
I realize I can simply create a new table with the properly ordered fields,
copy the data over to the new table and then drop the existing table but this
is approach is not optimal.
--
So Much - Yet - So LittleTry running Profiler when you insert the column in Enterprise Manager. I
think you will find it does exactly what you said you don't want to do -
creates a new table, inserts the data from the old table, and then droops
the old table. The right thing to do here is to write your SQL statements
to not depend on a particular column order so that you can use Alter Table.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"SwingVoter" <swingvoter@.nospam.nospam> wrote in message
news:91DDD1FB-961D-4976-9C36-C2EC3AE02C66@.microsoft.com...
>I am using SQL Server 2000. As part of a new development project I find
>that
> I frequently need to add new columns/fields to existing tables using SQL
> Scripts in Query Analyzer. I would like to add the new column at a
> specific
> Ordinal Position but when I use the standard Alter Table Add ... syntax
> for
> adding a new column the column always gets added to the end of the table.
> Accomplishing this task is relatively easy using Enterprise Manager, you
> simply select the column where you want the new column to be placed and
> click
> "Insert" and the new column gets inserted above the selected column. Is
> there a way to add the column in a specific ordinal location from Query
> Analyzer? If so, what is the syntax to make this happen. For example, I
> want to add a new column ([columntwo] [int] NULL,) between [columnone] and
> [columnthree] to the table layout below:
> ColumnOne
> ColumnThree
> ColumnFour
> Using standard "alter table' syntax [columntwo] get added to the bottom.
> ColumnOne
> ColumnThree
> ColumnFour
> ColumnTwo
> I want it to look like this:
> ColumnOne
> ColumnTwo
> ColumnThree
> ColumnFour
> I realize I can simply create a new table with the properly ordered
> fields,
> copy the data over to the new table and then drop the existing table but
> this
> is approach is not optimal.
> --
> So Much - Yet - So Little|||Hi,
How many rows there in your table?
Creating a new table and dropping old one is only optimal option. This can
be achieved through Enterprose Manageralso , right click the table => choose
"Design Table" and drag and drop the new cloumn created by you from the end
position to desired Ordinal Position. But go for this only if the table rows
are only a few hundreds or else it will hang.
Thanks,
Sree
"Roger Wolter[MSFT]" wrote:
> Try running Profiler when you insert the column in Enterprise Manager. I
> think you will find it does exactly what you said you don't want to do -
> creates a new table, inserts the data from the old table, and then droops
> the old table. The right thing to do here is to write your SQL statements
> to not depend on a particular column order so that you can use Alter Table.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "SwingVoter" <swingvoter@.nospam.nospam> wrote in message
> news:91DDD1FB-961D-4976-9C36-C2EC3AE02C66@.microsoft.com...
> >I am using SQL Server 2000. As part of a new development project I find
> >that
> > I frequently need to add new columns/fields to existing tables using SQL
> > Scripts in Query Analyzer. I would like to add the new column at a
> > specific
> > Ordinal Position but when I use the standard Alter Table Add ... syntax
> > for
> > adding a new column the column always gets added to the end of the table.
> > Accomplishing this task is relatively easy using Enterprise Manager, you
> > simply select the column where you want the new column to be placed and
> > click
> > "Insert" and the new column gets inserted above the selected column. Is
> > there a way to add the column in a specific ordinal location from Query
> > Analyzer? If so, what is the syntax to make this happen. For example, I
> > want to add a new column ([columntwo] [int] NULL,) between [columnone] and
> > [columnthree] to the table layout below:
> > ColumnOne
> > ColumnThree
> > ColumnFour
> >
> > Using standard "alter table' syntax [columntwo] get added to the bottom.
> > ColumnOne
> > ColumnThree
> > ColumnFour
> > ColumnTwo
> >
> > I want it to look like this:
> > ColumnOne
> > ColumnTwo
> > ColumnThree
> > ColumnFour
> >
> > I realize I can simply create a new table with the properly ordered
> > fields,
> > copy the data over to the new table and then drop the existing table but
> > this
> > is approach is not optimal.
> > --
> > So Much - Yet - So Little
>
>|||Hi Roger,
ALTER TABLE tab ADD COLUMN newcol [ AFTER | BEFORE ] curcol, just like
mysql does is there any option in Sql Server 2005.
Thanks,
Sree
"Roger Wolter[MSFT]" wrote:
> Try running Profiler when you insert the column in Enterprise Manager. I
> think you will find it does exactly what you said you don't want to do -
> creates a new table, inserts the data from the old table, and then droops
> the old table. The right thing to do here is to write your SQL statements
> to not depend on a particular column order so that you can use Alter Table.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "SwingVoter" <swingvoter@.nospam.nospam> wrote in message
> news:91DDD1FB-961D-4976-9C36-C2EC3AE02C66@.microsoft.com...
> >I am using SQL Server 2000. As part of a new development project I find
> >that
> > I frequently need to add new columns/fields to existing tables using SQL
> > Scripts in Query Analyzer. I would like to add the new column at a
> > specific
> > Ordinal Position but when I use the standard Alter Table Add ... syntax
> > for
> > adding a new column the column always gets added to the end of the table.
> > Accomplishing this task is relatively easy using Enterprise Manager, you
> > simply select the column where you want the new column to be placed and
> > click
> > "Insert" and the new column gets inserted above the selected column. Is
> > there a way to add the column in a specific ordinal location from Query
> > Analyzer? If so, what is the syntax to make this happen. For example, I
> > want to add a new column ([columntwo] [int] NULL,) between [columnone] and
> > [columnthree] to the table layout below:
> > ColumnOne
> > ColumnThree
> > ColumnFour
> >
> > Using standard "alter table' syntax [columntwo] get added to the bottom.
> > ColumnOne
> > ColumnThree
> > ColumnFour
> > ColumnTwo
> >
> > I want it to look like this:
> > ColumnOne
> > ColumnTwo
> > ColumnThree
> > ColumnFour
> >
> > I realize I can simply create a new table with the properly ordered
> > fields,
> > copy the data over to the new table and then drop the existing table but
> > this
> > is approach is not optimal.
> > --
> > So Much - Yet - So Little
>
>|||I don=B4t know why you need ordinal positions. Since youcan use the name
of a column picking the columns by the ordinal positions should not be
needed anymore, if you use a view for displaying the data or a
selection string,you can "reorder" the columns on your own, on the fly.
EM does this odd drop and create for alter, using QA does not, so if
there are many rows in your table use QA for this, rather than EM.
HTH, Jens Suessmeyer.|||Sreejith G wrote:
> Hi Roger,
> ALTER TABLE tab ADD COLUMN newcol [ AFTER | BEFORE ] curcol, just like
> mysql does is there any option in Sql Server 2005.
> Thanks,
> Sree
>
No, there is no such option in SQL Server 2005.
As Roger explained, if you apply good practices like never using SELECT
* in your code then physical column order is relatively unimportant. As
far as end users are concerned, column order is determined by the
column list in a SELECT statement, not by the physical placement on
disc.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||:) you guys are right... there is no need of ordnial position. But just think
of a design with 1000+ tables. And having employees data in a table with
ordinal position,
1.Created Date
2.EmpFirstName
3.EmpLastName
4.UpdatedDate
5.DOB
6.Sex
7.EmpMiddleName
8.Location
And on the run you added few feilds and messed up all your tables like this.
In future a datawarehouse analyst might try to map OLTP feilds to OLAP design
to satisfy kep performance indicators and they might get blown away seeing
such positioning of table feilds.
Always below one make you feel good :)..
1.EmpFirstName
2.EmpMiddleName
3.EmpLastName
4.DOB
5.Sex
6.Location
7.Created Date
8.UpdatedDate
Thanks,
Sree
"Jens" wrote:
> I don´t know why you need ordinal positions. Since youcan use the name
> of a column picking the columns by the ordinal positions should not be
> needed anymore, if you use a view for displaying the data or a
> selection string,you can "reorder" the columns on your own, on the fly.
> EM does this odd drop and create for alter, using QA does not, so if
> there are many rows in your table use QA for this, rather than EM.
> HTH, Jens Suessmeyer.
>|||:) you guys are right... there is no need of ordnial position. But just think
of a design with 1000+ tables. And having employees data in a table with
ordinal position,
1.Created Date
2.EmpFirstName
3.EmpLastName
4.UpdatedDate
5.DOB
6.Sex
7.EmpMiddleName
8.Location
And on the run you added few feilds and messed up all your tables like this.
In future a datawarehouse analyst might try to map OLTP feilds to OLAP design
to satisfy kep performance indicators and they might get blown away seeing
such positioning of table feilds.
Always below one make you feel good :)..
1.EmpFirstName
2.EmpMiddleName
3.EmpLastName
4.DOB
5.Sex
6.Location
7.Created Date
8.UpdatedDate
Thanks,
Sree
"Jens" wrote:
> I don´t know why you need ordinal positions. Since youcan use the name
> of a column picking the columns by the ordinal positions should not be
> needed anymore, if you use a view for displaying the data or a
> selection string,you can "reorder" the columns on your own, on the fly.
> EM does this odd drop and create for alter, using QA does not, so if
> there are many rows in your table use QA for this, rather than EM.
> HTH, Jens Suessmeyer.
>|||> ALTER TABLE tab ADD COLUMN newcol [ AFTER | BEFORE ] curcol, just like
> mysql does is there any option in Sql Server 2005.
You can submit this product enhancement request directly at
http://lab.msdn.microsoft.com/productfeedback/. Also specify the business
case for why this feature is important to you.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Sreejith G" <SreejithG@.discussions.microsoft.com> wrote in message
news:1AD84F7E-9603-48DF-812E-D332EC40DC49@.microsoft.com...
> Hi Roger,
> ALTER TABLE tab ADD COLUMN newcol [ AFTER | BEFORE ] curcol, just like
> mysql does is there any option in Sql Server 2005.
> Thanks,
> Sree
> "Roger Wolter[MSFT]" wrote:
>> Try running Profiler when you insert the column in Enterprise Manager. I
>> think you will find it does exactly what you said you don't want to do -
>> creates a new table, inserts the data from the old table, and then droops
>> the old table. The right thing to do here is to write your SQL
>> statements
>> to not depend on a particular column order so that you can use Alter
>> Table.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> Use of included script samples are subject to the terms specified at
>> http://www.microsoft.com/info/cpyright.htm
>> "SwingVoter" <swingvoter@.nospam.nospam> wrote in message
>> news:91DDD1FB-961D-4976-9C36-C2EC3AE02C66@.microsoft.com...
>> >I am using SQL Server 2000. As part of a new development project I find
>> >that
>> > I frequently need to add new columns/fields to existing tables using
>> > SQL
>> > Scripts in Query Analyzer. I would like to add the new column at a
>> > specific
>> > Ordinal Position but when I use the standard Alter Table Add ...
>> > syntax
>> > for
>> > adding a new column the column always gets added to the end of the
>> > table.
>> > Accomplishing this task is relatively easy using Enterprise Manager,
>> > you
>> > simply select the column where you want the new column to be placed and
>> > click
>> > "Insert" and the new column gets inserted above the selected column.
>> > Is
>> > there a way to add the column in a specific ordinal location from Query
>> > Analyzer? If so, what is the syntax to make this happen. For example,
>> > I
>> > want to add a new column ([columntwo] [int] NULL,) between [columnone]
>> > and
>> > [columnthree] to the table layout below:
>> > ColumnOne
>> > ColumnThree
>> > ColumnFour
>> >
>> > Using standard "alter table' syntax [columntwo] get added to the
>> > bottom.
>> > ColumnOne
>> > ColumnThree
>> > ColumnFour
>> > ColumnTwo
>> >
>> > I want it to look like this:
>> > ColumnOne
>> > ColumnTwo
>> > ColumnThree
>> > ColumnFour
>> >
>> > I realize I can simply create a new table with the properly ordered
>> > fields,
>> > copy the data over to the new table and then drop the existing table
>> > but
>> > this
>> > is approach is not optimal.
>> > --
>> > So Much - Yet - So Little
>>|||From a programming and data usability perspective you are correct. There is
no programming need for ordinal position however we have some tables with a
lot of fields and we want to keep "like" fields together for ease of mapping
and ease of visual recognition. It is much more difficult for a designer to
determine missing entities from the layout if the fields are scattered in an
non-logical order. As you can see from the list below in may cases simply
using a system table view and ordering by [name] is not helpful because the
similiar fields may not sort in that manner. It seems that there would be a
way to simply change the ordinal position in one of the master table as part
of the alter table script. Has anyone used this approach.
1.FirstName
2.MiddleName
.
7.City
8.State
Alter Table Add [LastName]
9.LastName
So Much - Yet - So Little
"Sreejith G" wrote:
> :) you guys are right... there is no need of ordnial position. But just think
> of a design with 1000+ tables. And having employees data in a table with
> ordinal position,
> 1.Created Date
> 2.EmpFirstName
> 3.EmpLastName
> 4.UpdatedDate
> 5.DOB
> 6.Sex
> 7.EmpMiddleName
> 8.Location
> And on the run you added few feilds and messed up all your tables like this.
> In future a datawarehouse analyst might try to map OLTP feilds to OLAP design
> to satisfy kep performance indicators and they might get blown away seeing
> such positioning of table feilds.
> Always below one make you feel good :)..
> 1.EmpFirstName
> 2.EmpMiddleName
> 3.EmpLastName
> 4.DOB
> 5.Sex
> 6.Location
> 7.Created Date
> 8.UpdatedDate
> Thanks,
> Sree
>
> "Jens" wrote:
> > I don´t know why you need ordinal positions. Since youcan use the name
> > of a column picking the columns by the ordinal positions should not be
> > needed anymore, if you use a view for displaying the data or a
> > selection string,you can "reorder" the columns on your own, on the fly.
> > EM does this odd drop and create for alter, using QA does not, so if
> > there are many rows in your table use QA for this, rather than EM.
> >
> > HTH, Jens Suessmeyer.
> >
> >|||It seems that there would be a script that is available to simply change the
ordinal position of the field for that table in the master table as part of
the alter table script. I am not sure of the impacts of this method. Has
anyone used this approach.
--
So Much - Yet - So Little
"Roger Wolter[MSFT]" wrote:
> Try running Profiler when you insert the column in Enterprise Manager. I
> think you will find it does exactly what you said you don't want to do -
> creates a new table, inserts the data from the old table, and then droops
> the old table. The right thing to do here is to write your SQL statements
> to not depend on a particular column order so that you can use Alter Table.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "SwingVoter" <swingvoter@.nospam.nospam> wrote in message
> news:91DDD1FB-961D-4976-9C36-C2EC3AE02C66@.microsoft.com...
> >I am using SQL Server 2000. As part of a new development project I find
> >that
> > I frequently need to add new columns/fields to existing tables using SQL
> > Scripts in Query Analyzer. I would like to add the new column at a
> > specific
> > Ordinal Position but when I use the standard Alter Table Add ... syntax
> > for
> > adding a new column the column always gets added to the end of the table.
> > Accomplishing this task is relatively easy using Enterprise Manager, you
> > simply select the column where you want the new column to be placed and
> > click
> > "Insert" and the new column gets inserted above the selected column. Is
> > there a way to add the column in a specific ordinal location from Query
> > Analyzer? If so, what is the syntax to make this happen. For example, I
> > want to add a new column ([columntwo] [int] NULL,) between [columnone] and
> > [columnthree] to the table layout below:
> > ColumnOne
> > ColumnThree
> > ColumnFour
> >
> > Using standard "alter table' syntax [columntwo] get added to the bottom.
> > ColumnOne
> > ColumnThree
> > ColumnFour
> > ColumnTwo
> >
> > I want it to look like this:
> > ColumnOne
> > ColumnTwo
> > ColumnThree
> > ColumnFour
> >
> > I realize I can simply create a new table with the properly ordered
> > fields,
> > copy the data over to the new table and then drop the existing table but
> > this
> > is approach is not optimal.
> > --
> > So Much - Yet - So Little
>
>|||Hi,
This may be a silly question, what are the drawbacks to altering the
column order (colorder) through the syscolumns table?
- Ben|||Hi,
As Roger metioned, In SQL server, you need to create a new table, inserts
the data from the old table, and then drop the old table.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
>Thread-Topic: Alter Table - Add Colum - Ordinal Position'
>thread-index: AcYtkhaya59bWEyOR5ylpD9wDQSW8A==>X-WBNR-Posting-Host: 207.59.213.130
>From: "=?Utf-8?B?U3dpbmdWb3Rlcg==?=" <swingvoter@.nospam.nospam>
>References: <91DDD1FB-961D-4976-9C36-C2EC3AE02C66@.microsoft.com>
<#JBE6aTLGHA.3164@.TK2MSFTNGP11.phx.gbl>
>Subject: Re: Alter Table - Add Colum - Ordinal Position'
>Date: Thu, 9 Feb 2006 08:01:29 -0800
>Lines: 66
>Message-ID: <C2BE3CB0-839A-4CE9-9FDE-96BC31931745@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:420560
>X-Tomcat-NG: microsoft.public.sqlserver.server
> It seems that there would be a script that is available to simply change
the
>ordinal position of the field for that table in the master table as part
of
>the alter table script. I am not sure of the impacts of this method. Has
>anyone used this approach.
>--
>So Much - Yet - So Little
>
>"Roger Wolter[MSFT]" wrote:
>> Try running Profiler when you insert the column in Enterprise Manager.
I
>> think you will find it does exactly what you said you don't want to do -
>> creates a new table, inserts the data from the old table, and then
droops
>> the old table. The right thing to do here is to write your SQL
statements
>> to not depend on a particular column order so that you can use Alter
Table.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
rights.
>> Use of included script samples are subject to the terms specified at
>> http://www.microsoft.com/info/cpyright.htm
>> "SwingVoter" <swingvoter@.nospam.nospam> wrote in message
>> news:91DDD1FB-961D-4976-9C36-C2EC3AE02C66@.microsoft.com...
>> >I am using SQL Server 2000. As part of a new development project I
find
>> >that
>> > I frequently need to add new columns/fields to existing tables using
SQL
>> > Scripts in Query Analyzer. I would like to add the new column at a
>> > specific
>> > Ordinal Position but when I use the standard Alter Table Add ...
syntax
>> > for
>> > adding a new column the column always gets added to the end of the
table.
>> > Accomplishing this task is relatively easy using Enterprise Manager,
you
>> > simply select the column where you want the new column to be placed
and
>> > click
>> > "Insert" and the new column gets inserted above the selected column.
Is
>> > there a way to add the column in a specific ordinal location from Query
>> > Analyzer? If so, what is the syntax to make this happen. For
example, I
>> > want to add a new column ([columntwo] [int] NULL,) between [columnone]
and
>> > [columnthree] to the table layout below:
>> > ColumnOne
>> > ColumnThree
>> > ColumnFour
>> >
>> > Using standard "alter table' syntax [columntwo] get added to the
bottom.
>> > ColumnOne
>> > ColumnThree
>> > ColumnFour
>> > ColumnTwo
>> >
>> > I want it to look like this:
>> > ColumnOne
>> > ColumnTwo
>> > ColumnThree
>> > ColumnFour
>> >
>> > I realize I can simply create a new table with the properly ordered
>> > fields,
>> > copy the data over to the new table and then drop the existing table
but
>> > this
>> > is approach is not optimal.
>> > --
>> > So Much - Yet - So Little
>>
>|||> This may be a silly question, what are the drawbacks to altering the
> column order (colorder) through the syscolumns table?
Not a silly question at all, but a simple answer: Corrupt table.
Syscolumns isn't only for the engine in what order to retrieve the columns. But also it defines in
what order the columns are physically stored on disk (in pages, row structures).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ben" <vanevery@.gmail.com> wrote in message
news:1139501596.454222.136580@.g47g2000cwa.googlegroups.com...
> Hi,
> This may be a silly question, what are the drawbacks to altering the
> column order (colorder) through the syscolumns table?
> - Ben
>|||A similar suggestion has already been submitted to the Product Feedback
Center. You can vote on this suggestion here:
http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=ed13e43a-a55a-45ee-98fb-b56f330a2cfa
Razvan|||Tibor,
Thank you very much! This information makes the rest of the
information presented in this thread much more justifiable!|||On Thu, 9 Feb 2006 07:50:34 -0800, SwingVoter wrote:
>From a programming and data usability perspective you are correct.
(snip)
> It is much more difficult for a designer to
>determine missing entities from the layout if the fields are scattered in an
>non-logical order.
Hi SwingVoter,
The easiest way to solve this is to organize the columns in a logical
order in your documentation, but don't care about the order in the
database.
Designers should work from the logical model, not from the
implementation.
> It seems that there would be a
>way to simply change the ordinal position in one of the master table as part
>of the alter table script.
There isn't one. As Tibor pointed out - attempting to do this would
corrupt your database.
--
Hugo Kornelis, SQL Server MVP

Alter Table - Add a field to a table in a specific location

I'm using the command to add a field to my table which has 20 fields.

ALTER TABLE table_name ADD column_name datatype

This adds the new field to the bottom of the table as the 21st first. How can I make it so it shows up as the 5th field in the table?

Wow you would have to do some work to get that to happen. It can be done but why?

The quick and dirty way is to copy all records into a temp table. DROP and reCREATE the table with the fields in the order of your preference. Then import the records from the temp table.

Adamus

|||The reason why I want to add it to a specific spot is to keep my table organized. The field that I am adding is a status field and I want it to be next to the other status fields and not just put it randomly at the bottom.

When you modify tables in Enterprise Manager it's real easy to change the location of fields...simply by drag and drop. This leads me to believe that there's a sql command that will do the same thing I'm just not sure what the command is.|||

Behind the 'scenes', Enterprise Manager does just like Adamus indicated. It creates a temp table, transfers the data, drops the old table, and renames the temp table.

There is no 'magic' to Enterprise Manager -it just writes the code for you -and sometimes not the best code either...

|||

Bank5,

this order is actually important only for the human user, as applications do not care so much about it (at least SHOULD NOT). Why don't you just create a view with correct column order which you can later use instead of table? Garnet Chaney posted an article about this, you can read it here

Also, "ALTER TABLE syntax for changing column order" feature is considered for the next release - if you think it would be useful you can vote here.

Cheers,

michalz

ALTER TABLE

I sometimes use the ALTER TABLe to add certain fields in my table. I need to
do it programatically. I will not get into why eventhough I have access to
Enterprise manager and can use that to do it that way.
My question is I'd like to know if there is syntax that I can use when I
ALTER TABLE and ADD COLUMN to a table. I'd Like to specify where in the tabl
e
to place this new column. For example placing the new field in 3rd position
or place in a table of 20 fields. I'd like to specify where in the table to
place this new field...
thanks in advance...AFAIK you can not
If you look in EM when you do it (save change script icon) you will see that
EM creates a new table drops the old one and renames the newly created one t
o
the original one
http://sqlservercode.blogspot.com/
"Angel" wrote:

> I sometimes use the ALTER TABLe to add certain fields in my table. I need
to
> do it programatically. I will not get into why eventhough I have access to
> Enterprise manager and can use that to do it that way.
> My question is I'd like to know if there is syntax that I can use when I
> ALTER TABLE and ADD COLUMN to a table. I'd Like to specify where in the ta
ble
> to place this new column. For example placing the new field in 3rd positio
n
> or place in a table of 20 fields. I'd like to specify where in the table t
o
> place this new field...
> thanks in advance...|||And at the same time preserving the data?
"SQL" wrote:
> AFAIK you can not
> If you look in EM when you do it (save change script icon) you will see th
at
> EM creates a new table drops the old one and renames the newly created one
to
> the original one
> http://sqlservercode.blogspot.com/
>
> "Angel" wrote:
>|||Column order is completely irrelevant. If you need this for presentation
purposes, simply do it on the client. If you need this for documentation, us
e
the INFORMATION_SCHEMA system views.
ML|||> I'd Like to specify where in the table
> to place this new column.
Sorry, you cannot do this with ALTER TABLE.
You can, of course, try to do what Enterprise Manager does:
http://www.aspfaq.com/2528|||> And at the same time preserving the data?
Yep, it does. See http://www.aspfaq.com/2528|||Well if you look at the change script you will see that the isolation level
is SERIALIZABLE
This is the highest level, no updates,inserts or deletes can happen on this
table while this script runs
http://sqlservercode.blogspot.com/
"Angel" wrote:
> And at the same time preserving the data?
> "SQL" wrote:
>|||Assuming you can get this to work, don't forget to use sp_refreshview on all
the views based on the table you alter. If you don't, your views may start
returning unexpected results.
"Angel" wrote:

> I sometimes use the ALTER TABLe to add certain fields in my table. I need
to
> do it programatically. I will not get into why eventhough I have access to
> Enterprise manager and can use that to do it that way.
> My question is I'd like to know if there is syntax that I can use when I
> ALTER TABLE and ADD COLUMN to a table. I'd Like to specify where in the ta
ble
> to place this new column. For example placing the new field in 3rd positio
n
> or place in a table of 20 fields. I'd like to specify where in the table t
o
> place this new field...
> thanks in advance...|||On Thu, 22 Sep 2005 09:37:07 -0700, mike wrote:

>Assuming you can get this to work, don't forget to use sp_refreshview on al
l
>the views based on the table you alter. If you don't, your views may start
>returning unexpected results.
Hi Mike,
AFAIK, that's only necessary for views defined as SELECT * FROM ...
And since you shouldn't use SELECT * in production code anyway, there's
no need to worry.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Alter Add - Before Text Datatype

I am constantly updating tables in my database with new fields, a lot of tables have a field with text datatype as the last field in the table.

It's very time consuming to run a script that renames the table, creates a new table with the new field before the text field, and insert into new table using select from renamed table. (SQL BELOW)

execute sp_rename CUSTDEF, CUSTDEF_1030A
GO

CREATE TABLE [dbo].[CUSTDEF] (
[CustDef1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CustDef2] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CustDef3] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CustDef4] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CustDef5] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CustDefNEW] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NOTES] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE INDEX [CUSTDEF_ONE] ON [dbo].[CUSTDEF]([CUSTDEF1], [CUSTDEF2]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

INSERT INTO CUSTDEF (CustDef1, CustDef2, CustDef3, CustDef4, CustDef5)
SELECT CustDef1, CustDef2, CustDef3, CustDef4, CustDef5
FROM CUSTDEF_1030A
GO

What I would like to do is to be able to have sql where I can use an ALTER ADD to add in CustDefNEW before the text field. Is there any way that I can do this, and save time more time than doing an insert/select against 50,000 records.

Thanks alot!The order of columns in a database has no bearing on the perforance. Is your background DB2? It used to be that way for varchars..

And why do you have text columns? How big is the data?

Bigger than 8000 bytes?

And no, ALter Add does manage the order of the columns (at least as far as I understand).

You can do it in EM...I think it'll do all that work for you behind the scenes...

I'm just not too keen about doing work there...see some weird things...

Good Luck

Another idea might be to use a view which looks like what you want...|||Brett,

I think the order can make a difference if you have, say, a long varchar field before the values on which you are searching. The server would have to determine the length of the data for the varchar in each row in order to calculate the offset of any data after it. If you know something that contradicts this, let me know.

In any case, MHawkins19, the TEXT datatype is not even stored in your rowset. All that is stored is a fixed length pointer to the location where the TEXT data is stored. Therefore, it make little or no difference what order your columns are in.

blindman

Alpha Prefix - Can it be done in SQL?

Good Morning Db forum.
I am working on a query that I need to pull all fields that contain 3 alpha characters. for example BCB001, MCR001, CHP001 and so forth.
Is there a SQL alpha wildcard that I could use to pull all records that have the three alpha chars?where patindex('%[a-z][a-z][a-z]%',daColumn) > 0

:)|||Awesome thank you. Now would that be the same for say numbers/digits?

where patindex like [0-9][0-9][a-z]% if i wanted to return 145A or something along those lines?|||something along those lines -- you should test it yourself :)

Allowing for blank fields in date text boxes

Several textboxes are being on a web form for date entry. and we want to allow the user to leave date fields blank (if particular date is unknown). However, when the field is left blank, the SQL Server 2000 database defaults to 1/1/1900 (datatype = datetime).
My question is as follows: How is it possible to leave a date textbox field blank, and either send 00\00\0000 or spaces to the SQL Server 2000 database datetime field?
Any advice/insight is appreciated! RobEDIT
Both Datetime and SmallDatetime allow NULLs Datetime is 8bytes while Smalldatetime is 4bytes but its resolution is limited. If you want Time Span you have to use DateTime.
when you are using NULL with Datetime you must use COUNT (*) in all you calculations because all SQL Server aggregate functions ignore NULLs except COUNT (*).
Hope this helps.|||Check outthis article

allow null and assigned default value

Hi,
I am setting the schema of a table,
I am confused abut what is the defference bewteen the two fields:
one allow null and assigned default value
one is not allow null and assigned default value.Hi,
1. ALLOW NULL with Default value:-
This will allow null values if we exclusvely insert a NULL Into the column
Otherwise a default will be inserted.
2. Not ALLOW NULL with Default value:-
Fire an error if we exclusevely try to insert a NULL value in to the column.
Thanks
Hari
SQL Server MVP
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:%23GNxlOikFHA.3064@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I am setting the schema of a table,
> I am confused abut what is the defference bewteen the two fields:
> one allow null and assigned default value
> one is not allow null and assigned default value.
>
>

Allignment Problem in the Body of the Report

When I preview the report In design mode all contents in the body of the report allign perpectly. After I deployed my report, some of the fields did not allign properly.
I am using a combination of text boxes and tables. I have tried to put the text box and/or the table in rectangles, but this method does not solve my allignment problem. I also did not check The Can increase to accommodate contents.
Thanks,
AugustaI am assuming that you are rendering the report using HTML. By chance do you
have overlapping items in your report? HTML does not support overlapping
items -- they will "move" around when the report is rendered.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Augusta" <Augusta@.discussions.microsoft.com> wrote in message
news:BE01723F-FEE8-4471-B042-AFC25605B973@.microsoft.com...
> When I preview the report In design mode all contents in the body of the
report allign perpectly. After I deployed my report, some of the fields did
not allign properly.
> I am using a combination of text boxes and tables. I have tried to put
the text box and/or the table in rectangles, but this method does not solve
my allignment problem. I also did not check The Can increase to
accommodate contents.
> Thanks,
> Augusta
>|||Bruce:
You are 100% correct and Thank You so much for your help.
Thanks again,
Augusta
"Bruce Johnson [MSFT]" wrote:
> I am assuming that you are rendering the report using HTML. By chance do you
> have overlapping items in your report? HTML does not support overlapping
> items -- they will "move" around when the report is rendered.
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Augusta" <Augusta@.discussions.microsoft.com> wrote in message
> news:BE01723F-FEE8-4471-B042-AFC25605B973@.microsoft.com...
> >
> > When I preview the report In design mode all contents in the body of the
> report allign perpectly. After I deployed my report, some of the fields did
> not allign properly.
> >
> > I am using a combination of text boxes and tables. I have tried to put
> the text box and/or the table in rectangles, but this method does not solve
> my allignment problem. I also did not check The Can increase to
> accommodate contents.
> >
> > Thanks,
> > Augusta
> >
>
>

all the columns of a Foreign Key

Hi,
I have a table with a Foreign Key. I need to know which Fields of that table
are in that Foreign Key, to which other Table (that contains the Primary
Key) they are linked, and to which Fields in that primary Key they are
Linked...
I found a query on the internet that did this job almost fine, but it
doesn't work anymore when the Primary Key consist of more than one Field...
As you can see in the result I can't see if ClientID is linked to ClientID
(record 1) or to CodeArticle (record 2).
does anybody knows how I can achieve this? This info is in the SQL Server,
so there should be a way to get it back I guess'
Thanks a lot in advance,
Pieter
The records:
tblArticleClientSodimex | ClientID | tblArticleClient | ClientID |
FK_tblArticleClientSodimex_tblArticleClient
tblArticleClientSodimex | CodeArticle | tblArticleClient | ClientID |
FK_tblArticleClientSodimex_tblArticleClient
tblArticleClientSodimex | ClientID | tblArticleClient | CodeArticle |
FK_tblArticleClientSodimex_tblArticleClient
tblArticleClientSodimex | CodeArticle | tblArticleClient | CodeArticle |
FK_tblArticleClientSodimex_tblArticleClient
The query:
SELECT
FK_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN
(
SELECT
i1.TABLE_NAME, i2.COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT
ON PT.TABLE_NAME = PK.TABLE_NAMEAh! I found it alreay myself!
I was able to put the ORDINAL_POSITION in it..
this is the changed query that seems to work fine...
SELECT
FK_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
ON (C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME)
INNER JOIN
(
SELECT
i1.TABLE_NAME, i2.COLUMN_NAME, i2.ORDINAL_POSITION
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT
ON (PT.TABLE_NAME = PK.TABLE_NAME) AND (CU.ORDINAL_POSITION =PT.ORDINAL_POSITION)
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:%23uZde2woFHA.708@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a table with a Foreign Key. I need to know which Fields of that
table
> are in that Foreign Key, to which other Table (that contains the Primary
> Key) they are linked, and to which Fields in that primary Key they are
> Linked...
> I found a query on the internet that did this job almost fine, but it
> doesn't work anymore when the Primary Key consist of more than one
Field...
> As you can see in the result I can't see if ClientID is linked to ClientID
> (record 1) or to CodeArticle (record 2).
> does anybody knows how I can achieve this? This info is in the SQL Server,
> so there should be a way to get it back I guess'
> Thanks a lot in advance,
> Pieter
> The records:
> tblArticleClientSodimex | ClientID | tblArticleClient | ClientID |
> FK_tblArticleClientSodimex_tblArticleClient
> tblArticleClientSodimex | CodeArticle | tblArticleClient | ClientID |
> FK_tblArticleClientSodimex_tblArticleClient
> tblArticleClientSodimex | ClientID | tblArticleClient | CodeArticle |
> FK_tblArticleClientSodimex_tblArticleClient
> tblArticleClientSodimex | CodeArticle | tblArticleClient | CodeArticle
|
> FK_tblArticleClientSodimex_tblArticleClient
> The query:
> SELECT
> FK_Table = FK.TABLE_NAME,
> FK_Column = CU.COLUMN_NAME,
> PK_Table = PK.TABLE_NAME,
> PK_Column = PT.COLUMN_NAME,
> Constraint_Name = C.CONSTRAINT_NAME
> FROM
> INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
> INNER JOIN
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
> ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
> INNER JOIN
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
> ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
> INNER JOIN
> INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
> ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
> INNER JOIN
> (
> SELECT
> i1.TABLE_NAME, i2.COLUMN_NAME
> FROM
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
> INNER JOIN
> INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
> ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
> WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
> ) PT
> ON PT.TABLE_NAME = PK.TABLE_NAME
>

all the columns of a Foreign Key

Hi,
I have a table with a Foreign Key. I need to know which Fields of that table
are in that Foreign Key, to which other Table (that contains the Primary
Key) they are linked, and to which Fields in that primary Key they are
Linked...
I found a query on the internet that did this job almost fine, but it
doesn't work anymore when the Primary Key consist of more than one Field...
As you can see in the result I can't see if ClientID is linked to ClientID
(record 1) or to CodeArticle (record 2).
does anybody knows how I can achieve this? This info is in the SQL Server,
so there should be a way to get it back I guess?
Thanks a lot in advance,
Pieter
The records:
tblArticleClientSodimex | ClientID | tblArticleClient | ClientID |
FK_tblArticleClientSodimex_tblArticleClient
tblArticleClientSodimex | CodeArticle | tblArticleClient | ClientID |
FK_tblArticleClientSodimex_tblArticleClient
tblArticleClientSodimex | ClientID | tblArticleClient | CodeArticle |
FK_tblArticleClientSodimex_tblArticleClient
tblArticleClientSodimex | CodeArticle | tblArticleClient | CodeArticle |
FK_tblArticleClientSodimex_tblArticleClient
The query:
SELECT
FK_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN
(
SELECT
i1.TABLE_NAME, i2.COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT
ON PT.TABLE_NAME = PK.TABLE_NAME
Ah! I found it alreay myself!
I was able to put the ORDINAL_POSITION in it..
this is the changed query that seems to work fine...
SELECT
FK_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
ON (C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME)
INNER JOIN
(
SELECT
i1.TABLE_NAME, i2.COLUMN_NAME, i2.ORDINAL_POSITION
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT
ON (PT.TABLE_NAME = PK.TABLE_NAME) AND (CU.ORDINAL_POSITION =
PT.ORDINAL_POSITION)
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:%23uZde2woFHA.708@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a table with a Foreign Key. I need to know which Fields of that
table
> are in that Foreign Key, to which other Table (that contains the Primary
> Key) they are linked, and to which Fields in that primary Key they are
> Linked...
> I found a query on the internet that did this job almost fine, but it
> doesn't work anymore when the Primary Key consist of more than one
Field...
> As you can see in the result I can't see if ClientID is linked to ClientID
> (record 1) or to CodeArticle (record 2).
> does anybody knows how I can achieve this? This info is in the SQL Server,
> so there should be a way to get it back I guess?
> Thanks a lot in advance,
> Pieter
> The records:
> tblArticleClientSodimex | ClientID | tblArticleClient | ClientID |
> FK_tblArticleClientSodimex_tblArticleClient
> tblArticleClientSodimex | CodeArticle | tblArticleClient | ClientID |
> FK_tblArticleClientSodimex_tblArticleClient
> tblArticleClientSodimex | ClientID | tblArticleClient | CodeArticle |
> FK_tblArticleClientSodimex_tblArticleClient
> tblArticleClientSodimex | CodeArticle | tblArticleClient | CodeArticle
|
> FK_tblArticleClientSodimex_tblArticleClient
> The query:
> SELECT
> FK_Table = FK.TABLE_NAME,
> FK_Column = CU.COLUMN_NAME,
> PK_Table = PK.TABLE_NAME,
> PK_Column = PT.COLUMN_NAME,
> Constraint_Name = C.CONSTRAINT_NAME
> FROM
> INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
> INNER JOIN
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
> ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
> INNER JOIN
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
> ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
> INNER JOIN
> INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
> ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
> INNER JOIN
> (
> SELECT
> i1.TABLE_NAME, i2.COLUMN_NAME
> FROM
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
> INNER JOIN
> INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
> ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
> WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
> ) PT
> ON PT.TABLE_NAME = PK.TABLE_NAME
>

all the columns of a Foreign Key

Hi,
I have a table with a Foreign Key. I need to know which Fields of that table
are in that Foreign Key, to which other Table (that contains the Primary
Key) they are linked, and to which Fields in that primary Key they are
Linked...
I found a query on the internet that did this job almost fine, but it
doesn't work anymore when the Primary Key consist of more than one Field...
As you can see in the result I can't see if ClientID is linked to ClientID
(record 1) or to CodeArticle (record 2).
does anybody knows how I can achieve this? This info is in the SQL Server,
so there should be a way to get it back I guess'
Thanks a lot in advance,
Pieter
The records:
tblArticleClientSodimex | ClientID | tblArticleClient | ClientID |
FK_tblArticleClientSodimex_tblArticleCli
ent
tblArticleClientSodimex | CodeArticle | tblArticleClient | ClientID |
FK_tblArticleClientSodimex_tblArticleCli
ent
tblArticleClientSodimex | ClientID | tblArticleClient | CodeArticle |
FK_tblArticleClientSodimex_tblArticleCli
ent
tblArticleClientSodimex | CodeArticle | tblArticleClient | CodeArticle |
FK_tblArticleClientSodimex_tblArticleCli
ent
The query:
SELECT
FK_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN
(
SELECT
i1.TABLE_NAME, i2.COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT
ON PT.TABLE_NAME = PK.TABLE_NAMEAh! I found it alreay myself!
I was able to put the ORDINAL_POSITION in it..
this is the changed query that seems to work fine...
SELECT
FK_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
ON (C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME)
INNER JOIN
(
SELECT
i1.TABLE_NAME, i2.COLUMN_NAME, i2.ORDINAL_POSITION
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT
ON (PT.TABLE_NAME = PK.TABLE_NAME) AND (CU.ORDINAL_POSITION =
PT.ORDINAL_POSITION)
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:%23uZde2woFHA.708@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a table with a Foreign Key. I need to know which Fields of that
table
> are in that Foreign Key, to which other Table (that contains the Primary
> Key) they are linked, and to which Fields in that primary Key they are
> Linked...
> I found a query on the internet that did this job almost fine, but it
> doesn't work anymore when the Primary Key consist of more than one
Field...
> As you can see in the result I can't see if ClientID is linked to ClientID
> (record 1) or to CodeArticle (record 2).
> does anybody knows how I can achieve this? This info is in the SQL Server,
> so there should be a way to get it back I guess'
> Thanks a lot in advance,
> Pieter
> The records:
> tblArticleClientSodimex | ClientID | tblArticleClient | ClientID |
> FK_tblArticleClientSodimex_tblArticleCli
ent
> tblArticleClientSodimex | CodeArticle | tblArticleClient | ClientID |
> FK_tblArticleClientSodimex_tblArticleCli
ent
> tblArticleClientSodimex | ClientID | tblArticleClient | CodeArticle |
> FK_tblArticleClientSodimex_tblArticleCli
ent
> tblArticleClientSodimex | CodeArticle | tblArticleClient | CodeArticle
|
> FK_tblArticleClientSodimex_tblArticleCli
ent
> The query:
> SELECT
> FK_Table = FK.TABLE_NAME,
> FK_Column = CU.COLUMN_NAME,
> PK_Table = PK.TABLE_NAME,
> PK_Column = PT.COLUMN_NAME,
> Constraint_Name = C.CONSTRAINT_NAME
> FROM
> INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
> INNER JOIN
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
> ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
> INNER JOIN
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
> ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
> INNER JOIN
> INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
> ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
> INNER JOIN
> (
> SELECT
> i1.TABLE_NAME, i2.COLUMN_NAME
> FROM
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
> INNER JOIN
> INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
> ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
> WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
> ) PT
> ON PT.TABLE_NAME = PK.TABLE_NAME
>