Alter column to Varchar(max) takes to long

Hi,

I need to modify existing table in my database to varchar(max) from varchar(2000)

This table contains 30 million plus rows and has more than 70 columns.

now when i am running alter command for this it take too long(more than 9 mins) which is not acceptable. . Is their any way to reduce this execution time

Following is the query i am using for this

ALTER TABLE Receipt
ALTER COLUMN CUSTOM VARCHAR(MAX) NULL

Please let me know if you have any suggestion to improve this

TAI
Prashant

Try to add a new column with the new type and then try to do something like:

UPADTE Table
SET
NewCol = Col1,
Col1 = NULL

After that drop the old column. I don′t know if that will save you the additional space the second column will need, but it should be worth a try doing this in one step. If it does not work for you, create a column first copy the data over to the new column, then drop the old one and rename the new one. You will have to do that in a maintaince window to not procude dirty write in the new column.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Alter Column as Identity

Hi All,
Is there a way to Alter an existing column and set it as an identity where t
he table contains data? TIA
A small sample code would be nice.No, you cannot alter an existing column to have the identity property. Your
options are limited to recreating the table or create another column as an
identity column.
Anith|||It's funny that you can manually set the column as an identity, but you can'
t programmatically change it.|||Actually, when you do it using EM (manually), a series of steps happen
behind the scenes : a new table is created with the identity column, the
data is copied, old one is dropped & the new table is renamed. You can see
the series of operations, by clicking on the save change script button on
the design table interface.
Anith

Allowing a user to execute SProcs

Hi,
I have a database that I'm using which contains many stored procedures. The
only way I have been able to allow a particular user to execute the
procedure is to select that user, then open that users permissions, find the
table and go through the list of procedures one by one allowing the user the
execute permission.
Can someone let me know how I can just allow a user permission to execute
all stored procedures that exist, as well as any new ones that may be
created. Is their like a "Can Execute" permission and if so where can i find
it.
Thanks everyone
SimonHere's one idea:
http://vyaskn.tripod.com/generate_scripts_repetitive_sql_tasks.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Simon Harvey" <simon.harvey@.the-web-works.co.uk> wrote in message
news:uYiTk9k8DHA.3012@.TK2MSFTNGP09.phx.gbl...
Hi,
I have a database that I'm using which contains many stored procedures. The
only way I have been able to allow a particular user to execute the
procedure is to select that user, then open that users permissions, find the
table and go through the list of procedures one by one allowing the user the
execute permission.
Can someone let me know how I can just allow a user permission to execute
all stored procedures that exist, as well as any new ones that may be
created. Is their like a "Can Execute" permission and if so where can i find
it.
Thanks everyone
Simon

Allow Null values

Hello experts,

I’ve got I little problem how the Null values are stored in the Cube.

I’ve got a Dimension what contains an Attribute e.g. “MyPerfectDate”.

Sometimes this Attribute has a Null value.

So far so long no problems.

Now if I try to build a Report with SSRS and found out, that SSAS stored the NULL values as blank or “empty” and change the datatype.

I tried to work with the DataItem and set NullProcesing to “Preverse” or “UnknownMember” but this doesn’t resolve my Problem.

Have someone another idea?

Thanks a lot

Alex

Hello,

Have nobody any idea?

This problem sounds not so complex to me, I think I forgot an option.

Could it be that I forget some information that you couldn’t help me?

Yours sincerely,

Alex

|||

We had a similar problem with a string value containing nulls and empty values in RS. Our solution was to go back to the cube and change the value to "". Since, yours is a date you might want to set the date to 1/1/1753.

|||

Hello phuhn,

Thanks a lot for the response. Today I thought about this option too.

You never found a better solution? I don’t know why my solution didn’t work, because there are some others threads and there is the solution to change the DataItem….

Best regards,

Alex

Allow Null values

Hello experts,

I’ve got I little problem how the Null values are stored in the Cube.

I’ve got a Dimension what contains an Attribute e.g. “MyPerfectDate”.

Sometimes this Attribute has a Null value.

So far so long no problems.

Now if I try to build a Report with SSRS and found out, that SSAS stored the NULL values as blank or “empty” and change the datatype.

I tried to work with the DataItem and set NullProcesing to “Preverse” or “UnknownMember” but this doesn’t resolve my Problem.

Have someone another idea?

Thanks a lot

Alex

Hello,

Have nobody any idea?

This problem sounds not so complex to me, I think I forgot an option.

Could it be that I forget some information that you couldn’t help me?

Yours sincerely,

Alex

|||

We had a similar problem with a string value containing nulls and empty values in RS. Our solution was to go back to the cube and change the value to "". Since, yours is a date you might want to set the date to 1/1/1753.

|||

Hello phuhn,

Thanks a lot for the response. Today I thought about this option too.

You never found a better solution? I don’t know why my solution didn’t work, because there are some others threads and there is the solution to change the DataItem….

Best regards,

Alex

Allow Null Value

Hi,
A given column of my Report (reporting services 2005) contains clients or
null values. I want the user to be able to chose one or more clients for the
parameter, and than show only those records of this client. I also want to
be able to chose "NULL", which will show the records with the null-values.
Also: When the user selects "(select all)" it should show not only those
with a client, but also those with a null value.
How do I have to do this? I tried with adding a Null-value row in my
parameter DataSet, but that didn't work. I also can't set the "Allow Null
Value" for my parameter ("The properties of the currently selected item are
not valid. Please correct all errors before continuing").
Does anybody know how to do this?
Thanks a lot in advance,
Pieterset your data source for the client list to:
select clientId, clientName (or whatever it is)
from clientTable
union select 0, 'All Clients'
union select -1, 'Blank Client'
order by 1
you query needs to take into account the magic values '0' and '-1'.
-T
"Pieter Coucke" <pietercoucke@.hotmail.com> wrote in message
news:uc7DB%23XfGHA.5104@.TK2MSFTNGP04.phx.gbl...
> Hi,
> A given column of my Report (reporting services 2005) contains clients or
> null values. I want the user to be able to chose one or more clients for
> the parameter, and than show only those records of this client. I also
> want to be able to chose "NULL", which will show the records with the
> null-values. Also: When the user selects "(select all)" it should show not
> only those with a client, but also those with a null value.
> How do I have to do this? I tried with adding a Null-value row in my
> parameter DataSet, but that didn't work. I also can't set the "Allow Null
> Value" for my parameter ("The properties of the currently selected item
> are not valid. Please correct all errors before continuing").
> Does anybody know how to do this?
> Thanks a lot in advance,
> Pieter
>

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
>