Changing Primary Key from nonclustered to clustered

Is it possible to modify a PrimaryKey Constraint from nonclustered to
clustered without dropping and redefining the Primary Key?
I'm 99% sure that I will need to drop and redefine but just thought that I
would ask since it is a major hassle to drop and redefine the Foreign Keys
that reference this Primary Key
Thanks in advance.
Nope, you have to drop and redefine it. You can use Enterprise Manager to
generate a script though. Make the change in the table designer and the Save
Script button is the third from the left.
Jacco Schalkwijk
SQL Server MVP
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:ex%23E5FkUEHA.3540@.TK2MSFTNGP11.phx.gbl...
> Is it possible to modify a PrimaryKey Constraint from nonclustered to
> clustered without dropping and redefining the Primary Key?
> I'm 99% sure that I will need to drop and redefine but just thought that I
> would ask since it is a major hassle to drop and redefine the Foreign Keys
> that reference this Primary Key
> Thanks in advance.
>

Changing Primary Key from nonclustered to clustered

Is it possible to modify a PrimaryKey Constraint from nonclustered to
clustered without dropping and redefining the Primary Key?
I'm 99% sure that I will need to drop and redefine but just thought that I
would ask since it is a major hassle to drop and redefine the Foreign Keys
that reference this Primary Key
Thanks in advance.Nope, you have to drop and redefine it. You can use Enterprise Manager to
generate a script though. Make the change in the table designer and the Save
Script button is the third from the left.
Jacco Schalkwijk
SQL Server MVP
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:ex%23E5FkUEHA.3540@.TK2MSFTNGP11.phx.gbl...
> Is it possible to modify a PrimaryKey Constraint from nonclustered to
> clustered without dropping and redefining the Primary Key?
> I'm 99% sure that I will need to drop and redefine but just thought that I
> would ask since it is a major hassle to drop and redefine the Foreign Keys
> that reference this Primary Key
> Thanks in advance.
>

Changing PK Names

Hi,
I need to change the primary key names from
the 'PK_tablename' format to 'tablename_pk' format. Is
there any other place where the constraint name stored
than sysobjects and sysindexes table '
Is there an easy way to do this for 100 user tables ?
Thanks.Hi,
A PRIMARY KEY has a references in sysobjects as well as sysindexes. You can
use the below script to rename the Primary Key
sp_rename 'PK_tablename' , 'tablename_pk' , 'object'
Thanks
Hari
MCDBA
"Frank" <anonymous@.discussions.microsoft.com> wrote in message
news:15b3501c41678$2b9d1d20$a501280a@.phx
.gbl...
> Hi,
> I need to change the primary key names from
> the 'PK_tablename' format to 'tablename_pk' format. Is
> there any other place where the constraint name stored
> than sysobjects and sysindexes table '
> Is there an easy way to do this for 100 user tables ?
> Thanks.|||This may help:
DECLARE @.vTableName varchar(128)
DECLARE @.vObjectID int -- the object id of the table
DECLARE @.vIndexID smallint -- the index id of an index
DECLARE @.vIndexName sysname
DECLARE @.vNewName sysname
/*
Open Cursor over Tables which have pk indexes
*/
DECLARE table_cursor CURSOR LOCAL STATIC FOR
SELECT '[dbo].['+[name]+']' AS table_name, [id]
FROM sysobjects
WHERE xtype = 'U' AND (status & 64)=0
AND status > 0
AND [id] IN (
SELECT [id]
FROM sysindexes
WHERE indid > 0
AND indid < 255
AND (status & 64)=0
AND [name] LIKE 'PK_%')
OPEN table_cursor
FETCH table_cursor INTO @.vTableName, @.vObjectID
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
DECLARE index_cursor CURSOR LOCAL STATIC FOR
SELECT indid, [name]
FROM sysindexes
WHERE id = @.vObjectID
AND indid > 0
AND indid < 255
AND (status & 64)=0
AND [name] LIKE 'PK_%'
ORDER BY indid
OPEN index_cursor
FETCH index_cursor INTO @.vIndexID, @.vIndexName
-- IF NO INDEX, QUIT
IF @.@.FETCH_STATUS < 0
BEGIN
DEALLOCATE index_cursor
RAISERROR(15472,-1,-1) --'Object does not have any indexes.'
RETURN (0)
END
WHILE @.@.fetch_status >= 0
BEGIN
SET @.vNewname = REPLACE(LOWER(@.vIndexName), 'pk_', '') + '_pk'
/*
TODO: Change print to EXEC .. EXEC('EXEC sp_rename...)
*/
PRINT 'EXEC sp_rename ' + @.vIndexName + ', ' + @.vNewName
FETCH index_cursor INTO @.vIndexID, @.vIndexName
END
CLOSE index_cursor
DEALLOCATE index_cursor
END
FETCH NEXT FROM table_cursor INTO @.vTableName, @.vObjectID
END
CLOSE table_cursor
DEALLOCATE table_cursor
Christopher Winn
Business Intelligence Engineer
Edugration
"Frank" <anonymous@.discussions.microsoft.com> wrote in message
news:15b3501c41678$2b9d1d20$a501280a@.phx
.gbl...
> Hi,
> I need to change the primary key names from
> the 'PK_tablename' format to 'tablename_pk' format. Is
> there any other place where the constraint name stored
> than sysobjects and sysindexes table '
> Is there an easy way to do this for 100 user tables ?
> Thanks.|||Thanks.

>--Original Message--
>This may help:
>DECLARE @.vTableName varchar(128)
>DECLARE @.vObjectID int -- the object id of the table
>DECLARE @.vIndexID smallint -- the index id of an index
>DECLARE @.vIndexName sysname
>DECLARE @.vNewName sysname
>/*
> Open Cursor over Tables which have pk indexes
>*/
>DECLARE table_cursor CURSOR LOCAL STATIC FOR
> SELECT '[dbo].['+[name]+']' AS table_name, [id]
> FROM sysobjects
> WHERE xtype = 'U' AND (status & 64)=0
> AND status > 0
> AND [id] IN (
> SELECT [id]
> FROM sysindexes
> WHERE indid > 0
> AND indid < 255
> AND (status & 64)=0
> AND [name] LIKE 'PK_%')
>OPEN table_cursor
>FETCH table_cursor INTO @.vTableName, @.vObjectID
>WHILE (@.@.fetch_status <> -1)
>BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
>
> DECLARE index_cursor CURSOR LOCAL STATIC FOR
> SELECT indid, [name]
> FROM sysindexes
> WHERE id = @.vObjectID
> AND indid > 0
> AND indid < 255
> AND (status & 64)=0
> AND [name] LIKE 'PK_%'
> ORDER BY indid
> OPEN index_cursor
> FETCH index_cursor INTO @.vIndexID, @.vIndexName
> -- IF NO INDEX, QUIT
> IF @.@.FETCH_STATUS < 0
> BEGIN
> DEALLOCATE index_cursor
> RAISERROR(15472,-1,-1) --'Object does not have any
indexes.'
> RETURN (0)
> END
> WHILE @.@.fetch_status >= 0
> BEGIN
> SET @.vNewname = REPLACE(LOWER(@.vIndexName), 'pk_', '')
+ '_pk'
> /*
> TODO: Change print to EXEC .. EXEC('EXEC
sp_rename...)
>*/
>PRINT 'EXEC sp_rename ' + @.vIndexName + ', ' + @.vNewName
> FETCH index_cursor INTO @.vIndexID, @.vIndexName
> END
> CLOSE index_cursor
> DEALLOCATE index_cursor
>
> END
> FETCH NEXT FROM table_cursor INTO @.vTableName, @.vObjectID
>END
>CLOSE table_cursor
>DEALLOCATE table_cursor
>Christopher Winn
>Business Intelligence Engineer
>Edugration
>"Frank" <anonymous@.discussions.microsoft.com> wrote in
message
> news:15b3501c41678$2b9d1d20$a501280a@.phx
.gbl...
>
>.
>

Changing Ordering of a Dimension Attribute

I have a dimension attribute called Month. It is sorting alphabetically instead of based on it's underlying key value. How do I change this?

Thanks,

ChrisChange the order by from "Name" to "Key". No idea what "Attribute Name" and "Attribute Key" do however.|||If you choose attribute name, you can define the order by another field.
I have in each dimension an order field and I have configured each dimension to sort by this field. By default I fill in this field with name. But if a user request another order I can change values in this field without changing dimension structure.

Changing of @@IDENTITY item in a trigger

Hello!

I have a instead of insert trigger that after doing some validation, inserts a record into a specific table. Another table requires the key generated by this insert in order to add a record, which is done at the end of the trigger. When called from a client (via a recordset.update call) the newly created key from the first insert is overridden with the key from the second insert. Any ideas on how I can save the first key, then pass it back to the client? The client currently receives the second key created.

Thanks in advance,

EverettI have exactly the same problem and although I don't have a solution (yet), I have found out that this appears to be by design. ADO does a "Select @.@.IDENTITY" after an insert to retrieve the key of the record inserted (See Chapter 11 of "Programming ADO" by David Sceppa at http://www.microsoft.com/mspress/books/sampchap/3445a.asp). If there was some way to modify ADO's behaviour to use instead "Select IDENT_CURRENT('<tablename>')" then problem solved. The article describes a "Update Resync" dynamic record set property but it doesn't appear to be flexible enough to tell ADO to use IDENT_CURRENT.

The hunt continues...

Changing data type

Hi all,

I have to extend the length of a field, which appears in hundred of tables in a database, and maybe involved in constraints/index/primary key. It is difficult for me to drop all the constraints/index/primary key before altering each table one by one....Is there any easier way to drop all constraints and keys (instead of dropping them one by one)? Or any better (faster) way to extend the length of this field?

Thanks for any help here!OK, here's one method:

Script out your entire database. Then use search and replace to modify all instances of the field length (character, I assume?). Then use this script to create a new, corrected database, and use DTS to transfer data from the old database.

I hope you are aware, though, that you may likely render much of your SQL code (procedures, functions, triggers...) obsolete if they reference your field and attempt to assign it or concatenate it to variables that are of insufficient length. You may have quite a debugging job ahead of you. A 3rd party package such as ERWIN might be of assistance.

blindman|||Just generate ALTER TABLE statements:

SELECT 'ALTER TABLE ' + T.Name + ' ALTER COLUMN ' + C.Name + ' nVarChar(<YourNewFieldLength>);'
FROM SysColumns C INNER JOIN
SysObjects T ON T.id = C.id
WHERE T.XType = 'U' AND T.Name <> 'dtproperties' AND
C.Name = < YourColumnName >

You can consider to open a cursor, and to execute your statement dynamically. Alternatively, you can also generate a script.

I didn't check it for constraints, but indices are automatically updated by this statement.

Changing Data Format On Select

Hey all,
I have a basic table that looks something like this.

CREATE TABLE MyTable
(
ID INT IDENTITY PRIMARY KEY,
Company_ID INT NOT NULL,
Round VARCHAR(50) NOT NULL,
Details VARCHAR(250) NOT NULL
)

It has a few rows of data that look like this:

Identity Company_ID Round Details
--------------
1 5 A Blah, blah.
2 5 B Generic data, blah blah.
3 5 WERT More generic blah blah.

Now what i'm trying to do during my select statement is select all the rows
that belong to company_id 5 but if any of the rows round value contains the
text "WERT" convert that text into just a "--" for presentation purposes,
but still select that row. I can't seem to figure out how i would transform
the text in the select statement? My immediate thought was substring /
replace but i would need to combine it with an if else statement which i've
no idea how to make work in a select (sub-query maybe?) statement. Is this
possible? Perhaps i'm stuck iterating through the returned data within the
client application before presenting?

Any help, as always, would be greatly appreciated.

MuhdMuhd,

SELECT ID, Company_ID,
[Round] = CASE [Round] WHEN 'WERT' THEN '--' ELSE [Round] END,
Details
FROM MyTable
WHERE Company_ID = 5

-Andy

"Muhd" <eat@.joes.com> wrote in message news:7MZ4d.91084$%S.84951@.pd7tw2no...
> Hey all,
> I have a basic table that looks something like this.
> CREATE TABLE MyTable
> (
> ID INT IDENTITY PRIMARY KEY,
> Company_ID INT NOT NULL,
> Round VARCHAR(50) NOT NULL,
> Details VARCHAR(250) NOT NULL
> )
> It has a few rows of data that look like this:
> Identity Company_ID Round Details
> --------------
> 1 5 A Blah, blah.
> 2 5 B Generic data, blah blah.
> 3 5 WERT More generic blah blah.
>
> Now what i'm trying to do during my select statement is select all the
> rows that belong to company_id 5 but if any of the rows round value
> contains the text "WERT" convert that text into just a "--" for
> presentation purposes, but still select that row. I can't seem to figure
> out how i would transform the text in the select statement? My immediate
> thought was substring / replace but i would need to combine it with an if
> else statement which i've no idea how to make work in a select (sub-query
> maybe?) statement. Is this possible? Perhaps i'm stuck iterating through
> the returned data within the client application before presenting?
> Any help, as always, would be greatly appreciated.
> Muhd|||Although Andy has shown you how you can accomplish the task using
Transact-SQL, formatting data for presentation purposes is generally best
handled on the client side, IMHO. Most programming languages provide a
number of methods to format data.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Muhd" <eat@.joes.com> wrote in message news:7MZ4d.91084$%S.84951@.pd7tw2no...
> Hey all,
> I have a basic table that looks something like this.
> CREATE TABLE MyTable
> (
> ID INT IDENTITY PRIMARY KEY,
> Company_ID INT NOT NULL,
> Round VARCHAR(50) NOT NULL,
> Details VARCHAR(250) NOT NULL
> )
> It has a few rows of data that look like this:
> Identity Company_ID Round Details
> --------------
> 1 5 A Blah, blah.
> 2 5 B Generic data, blah blah.
> 3 5 WERT More generic blah blah.
>
> Now what i'm trying to do during my select statement is select all the
> rows that belong to company_id 5 but if any of the rows round value
> contains the text "WERT" convert that text into just a "--" for
> presentation purposes, but still select that row. I can't seem to figure
> out how i would transform the text in the select statement? My immediate
> thought was substring / replace but i would need to combine it with an if
> else statement which i've no idea how to make work in a select (sub-query
> maybe?) statement. Is this possible? Perhaps i'm stuck iterating through
> the returned data within the client application before presenting?
> Any help, as always, would be greatly appreciated.
> Muhd|||While the front end is a very good place for presentation or value decoding
...
having a record transformation in the database allows the logic to be
centralized and recorded somewhere.

what if in addition to WERT you need to ignore SPAM, just add a row to the
table. no front end changes needed.

create a decoder table and join to that returning --

create view round_decoder
(
Round VARCHAR(50) NOT NULL,
DisplayName VARCHAR(50) NOT NULL
)

insert into round_decoder ( 'WERT' , '--' )

SELECT
Identity,
Company_ID ,
IsNull( d.DisplayName , x.Round ) as Round ,
Details
FROM MyTable x
LEFT JOIN round_decoder d on x.Round = d.Round

you can query this decoder table for drop down lists or other data
selection / presentation stuff in the front end.
i would recommend a reusable object that get's this data and does the
decoding in the front end.

"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:pfd6d.1939$zc1.1654@.newssvr12.news.prodigy.co m...
> Although Andy has shown you how you can accomplish the task using
> Transact-SQL, formatting data for presentation purposes is generally best
> handled on the client side, IMHO. Most programming languages provide a
> number of methods to format data.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Muhd" <eat@.joes.com> wrote in message
news:7MZ4d.91084$%S.84951@.pd7tw2no...
> > Hey all,
> > I have a basic table that looks something like this.
> > CREATE TABLE MyTable
> > (
> > ID INT IDENTITY PRIMARY KEY,
> > Company_ID INT NOT NULL,
> > Round VARCHAR(50) NOT NULL,
> > Details VARCHAR(250) NOT NULL
> > )
> > It has a few rows of data that look like this:
> > Identity Company_ID Round Details
> > --------------
> > 1 5 A Blah, blah.
> > 2 5 B Generic data, blah
blah.
> > 3 5 WERT More generic blah blah.
> > Now what i'm trying to do during my select statement is select all the
> > rows that belong to company_id 5 but if any of the rows round value
> > contains the text "WERT" convert that text into just a "--" for
> > presentation purposes, but still select that row. I can't seem to
figure
> > out how i would transform the text in the select statement? My
immediate
> > thought was substring / replace but i would need to combine it with an
if
> > else statement which i've no idea how to make work in a select
(sub-query
> > maybe?) statement. Is this possible? Perhaps i'm stuck iterating
through
> > the returned data within the client application before presenting?
> > Any help, as always, would be greatly appreciated.
> > Muhd|||Thanks for that, David... Great idea!

-Andy

"David Rawheiser" <rawhide58@.hotmail.com> wrote in message
news:4Cx6d.645799$Gx4.11917@.bgtnsc04-news.ops.worldnet.att.net...
> While the front end is a very good place for presentation or value
> decoding
> ...
> having a record transformation in the database allows the logic to be
> centralized and recorded somewhere.
> what if in addition to WERT you need to ignore SPAM, just add a row to the
> table. no front end changes needed.
> create a decoder table and join to that returning --
> create view round_decoder
> (
> Round VARCHAR(50) NOT NULL,
> DisplayName VARCHAR(50) NOT NULL
> )
> insert into round_decoder ( 'WERT' , '--' )
> SELECT
> Identity,
> Company_ID ,
> IsNull( d.DisplayName , x.Round ) as Round ,
> Details
> FROM MyTable x
> LEFT JOIN round_decoder d on x.Round = d.Round
> you can query this decoder table for drop down lists or other data
> selection / presentation stuff in the front end.
> i would recommend a reusable object that get's this data and does the
> decoding in the front end.
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:pfd6d.1939$zc1.1654@.newssvr12.news.prodigy.co m...
>> Although Andy has shown you how you can accomplish the task using
>> Transact-SQL, formatting data for presentation purposes is generally best
>> handled on the client side, IMHO. Most programming languages provide a
>> number of methods to format data.
>>
>> --
>> Hope this helps.
>>
>> Dan Guzman
>> SQL Server MVP
>>
>> "Muhd" <eat@.joes.com> wrote in message
> news:7MZ4d.91084$%S.84951@.pd7tw2no...
>> > Hey all,
>> > I have a basic table that looks something like this.
>>> > CREATE TABLE MyTable
>> > (
>> > ID INT IDENTITY PRIMARY KEY,
>> > Company_ID INT NOT NULL,
>> > Round VARCHAR(50) NOT NULL,
>> > Details VARCHAR(250) NOT NULL
>> > )
>>> > It has a few rows of data that look like this:
>>> > Identity Company_ID Round Details
>> > --------------
>> > 1 5 A Blah, blah.
>> > 2 5 B Generic data, blah
> blah.
>> > 3 5 WERT More generic blah blah.
>>>> > Now what i'm trying to do during my select statement is select all the
>> > rows that belong to company_id 5 but if any of the rows round value
>> > contains the text "WERT" convert that text into just a "--" for
>> > presentation purposes, but still select that row. I can't seem to
> figure
>> > out how i would transform the text in the select statement? My
> immediate
>> > thought was substring / replace but i would need to combine it with an
> if
>> > else statement which i've no idea how to make work in a select
> (sub-query
>> > maybe?) statement. Is this possible? Perhaps i'm stuck iterating
> through
>> > the returned data within the client application before presenting?
>>> > Any help, as always, would be greatly appreciated.
>>> > Muhd
>>>
>>|||Thanks all !!!

I've decided to keep the presentation logic on the presentation tier but i
did rig up a "decoder" table to give it a try and it worked for me really
well. It's something i'll have to add to my toolkit of tricks in case i
need it in the future.

Thanks,
Muhd.

"Andy Williams" <f_u_b_a_r_1_1_1_9@.y_a_h_o_o_._c_o_m> wrote in message
news:c1z6d.11644$Qv5.5768@.newssvr33.news.prodigy.c om...
> Thanks for that, David... Great idea!
> -Andy
> "David Rawheiser" <rawhide58@.hotmail.com> wrote in message
> news:4Cx6d.645799$Gx4.11917@.bgtnsc04-news.ops.worldnet.att.net...
>> While the front end is a very good place for presentation or value
>> decoding
>> ...
>> having a record transformation in the database allows the logic to be
>> centralized and recorded somewhere.
>>
>> what if in addition to WERT you need to ignore SPAM, just add a row to
>> the
>> table. no front end changes needed.
>>
>> create a decoder table and join to that returning --
>>
>> create view round_decoder
>> (
>> Round VARCHAR(50) NOT NULL,
>> DisplayName VARCHAR(50) NOT NULL
>> )
>>
>> insert into round_decoder ( 'WERT' , '--' )
>>
>> SELECT
>> Identity,
>> Company_ID ,
>> IsNull( d.DisplayName , x.Round ) as Round ,
>> Details
>> FROM MyTable x
>> LEFT JOIN round_decoder d on x.Round = d.Round
>>
>> you can query this decoder table for drop down lists or other data
>> selection / presentation stuff in the front end.
>> i would recommend a reusable object that get's this data and does the
>> decoding in the front end.
>>
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> news:pfd6d.1939$zc1.1654@.newssvr12.news.prodigy.co m...
>>> Although Andy has shown you how you can accomplish the task using
>>> Transact-SQL, formatting data for presentation purposes is generally
>>> best
>>> handled on the client side, IMHO. Most programming languages provide a
>>> number of methods to format data.
>>>
>>> --
>>> Hope this helps.
>>>
>>> Dan Guzman
>>> SQL Server MVP
>>>
>>> "Muhd" <eat@.joes.com> wrote in message
>> news:7MZ4d.91084$%S.84951@.pd7tw2no...
>>> > Hey all,
>>> > I have a basic table that looks something like this.
>>>>> > CREATE TABLE MyTable
>>> > (
>>> > ID INT IDENTITY PRIMARY KEY,
>>> > Company_ID INT NOT NULL,
>>> > Round VARCHAR(50) NOT NULL,
>>> > Details VARCHAR(250) NOT NULL
>>> > )
>>>>> > It has a few rows of data that look like this:
>>>>> > Identity Company_ID Round Details
>>> > --------------
>>> > 1 5 A Blah, blah.
>>> > 2 5 B Generic data, blah
>> blah.
>>> > 3 5 WERT More generic blah blah.
>>>>>>> > Now what i'm trying to do during my select statement is select all the
>>> > rows that belong to company_id 5 but if any of the rows round value
>>> > contains the text "WERT" convert that text into just a "--" for
>>> > presentation purposes, but still select that row. I can't seem to
>> figure
>>> > out how i would transform the text in the select statement? My
>> immediate
>>> > thought was substring / replace but i would need to combine it with an
>> if
>>> > else statement which i've no idea how to make work in a select
>> (sub-query
>>> > maybe?) statement. Is this possible? Perhaps i'm stuck iterating
>> through
>>> > the returned data within the client application before presenting?
>>>>> > Any help, as always, would be greatly appreciated.
>>>>> > Muhd
>>>>>
>>>
>>
>>