Retriieve table names

Hi friends,
How to retrieve all the tables in the database that is having primary key. I
also want to retrieve the foreign key tables with their parent table.
Thanks
vanithaoops ..take this link :)
http://www.dandyman.net/SQL/downloads.aspx
Dandy Weyn
[MCSE-MCSA-MCDBA-MCDST-MCT]
http://www.dandyman.net
Check my SQL Server Resource Pages at http://www.dandyman.net/sql
"Vanitha" <Vanitha@.discussions.microsoft.com> wrote in message
news:701F4FBD-7537-44B9-9C8D-600744CB013B@.microsoft.com...
> Hi friends,
> How to retrieve all the tables in the database that is having primary key.
> I
> also want to retrieve the foreign key tables with their parent table.
> Thanks
> vanitha|||Sysobjects contains objects stored in every user database.
You can join with sysconstraints and sysforeignkeyconstraints that are
having relationship with the Sysobjects table on the object_id
On Microsoft Website you can look for the SQL System table help file.
I also created a link for it on my website in the download section.
http://www.dandyman.net/sql/downloads.asp
This file might also be VERY useful for future system table querying
--
Dandy Weyn
[MCSE-MCSA-MCDBA-MCDST-MCT]
http://www.dandyman.net
Check my SQL Server Resource Pages at http://www.dandyman.net/sql
"Vanitha" <Vanitha@.discussions.microsoft.com> wrote in message
news:701F4FBD-7537-44B9-9C8D-600744CB013B@.microsoft.com...
> Hi friends,
> How to retrieve all the tables in the database that is having primary key.
> I
> also want to retrieve the foreign key tables with their parent table.
> Thanks
> vanitha|||Vanitha
try this
sp_msforeachtable @.command1 = "sp_helpconstraint '?' "
Regards
R.D
"Vanitha" wrote:

> Hi friends,
> How to retrieve all the tables in the database that is having primary key.
I
> also want to retrieve the foreign key tables with their parent table.
> Thanks
> vanitha|||Vanitha
SELECT Table_Name
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_NAME
),
'IsPrimaryKey') = 1
"Vanitha" <Vanitha@.discussions.microsoft.com> wrote in message
news:701F4FBD-7537-44B9-9C8D-600744CB013B@.microsoft.com...
> Hi friends,
> How to retrieve all the tables in the database that is having primary key.
> I
> also want to retrieve the foreign key tables with their parent table.
> Thanks
> vanitha|||Vanitha
see message pane besides grid pane for foreign key references when you
execute that
Regards
R.D
"R.D" wrote:
> Vanitha
> try this
> sp_msforeachtable @.command1 = "sp_helpconstraint '?' "
> Regards
> R.D
> "Vanitha" wrote:
>

retrieving the BigInt value from the Identity Column after inserting

I have a database that has a tble with a field that autoincrements as a primary key. meanig that the field type is BigInteger and it is set up as my Identity Column. Now when I insert a new record that field gets updated automaticly.

How can I get this value in the same operation as my insert? meaning, in 1 sub, I insert a new record but then need to retieve the Identity Value. All in the same procedure.

Waht is the way to achive this please?

Marc

What I do is issue the two commands (the sql insert, and the sql select scope_identity) in the same execute separated by semi colon.

the trick is to set the parameter direction to output for the identity.

David H. has a good article.

http://davidhayden.com/blog/dave/archive/2006/02/16/2803.aspx

|||

Thank you very much. that did it!

Marc

Retrieving the autoincremented primary key value

I am using several TextBox Controls, instead of a FormView, for inserting data into a Sql Database. The primary key (ID) is an integer which is automatically incremented by one at each insertion.
At each insertion I need to retrieve the ID value of the newly inserted record.
I have followed a suggestion from a help sample with this code:

Imports System.Data.Common
Imports System.Data.SqlClient
Imports System.Data
Partial Class Insert
Inherits System.Web.UI.Page

Protected Sub LinkButton1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles LinkButton1.Click

SqlDataSource1.Insert()
End Sub

Sub On_Inserting(ByVal sender As Object, ByVal e As SqlDataSourceCommandEventArgs)
Dim InsertedKey As SqlParameter
InsertedKey = New SqlParameter("@.PK_GuestList", SqlDbType.Int)
InsertedKey.Direction = ParameterDirection.Output
e.Command.Parameters.Add(InsertedKey)
End Sub

Sub On_Inserted(ByVal sender As Object, ByVal e As SqlDataSourceStatusEventArgs)
Dim command As DbCommand
command = e.Command
Label1.Text = command.Parameters("@.PK_GuestList").Value.ToString()
End Sub

End Class

No output appears on the Label1.
If in the last code row I replace "@.PK_GuestList" with the name of any TextBox used for inputting data, its content is correctly shown in Label1.

Where is the problem?

You need to use Select Scope_Identity() in your SQL to get the ID that was just created. Without seeing your SQL, it's impossible to tell if you are doing this correctly.|||

Thanks Mike.

This is my Sql:

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"DeleteCommand="DELETE FROM [GuestList] WHERE [ID] = @.ID"InsertCommand="INSERT INTO [GuestList] ( [IDD], [UserName],, [Title], [Message], [Reason],

[Accento], [Views], [Posts], [Quando], [LastPost]) VALUES ( @.IDD, @.UserName, @.Email, @.Title,

@.Message, @.Reason, @.Accento, @.Views, @.Posts, @.Quando, @.LastPost)"

OnInserting="On_Inserting"OnInserted="On_Inserted"SelectCommand="SELECT * FROM [GuestList]"UpdateCommand="UPDATE [GuestList] SET [IDD] = @.IDD, [UserName] = @.UserName, = @.Email, [Title] = @.Title, [Message] = @.Message, [Reason] = @.Reason, [Accento] = @.Accento, [Views] = @.Views, [Posts] = @.Posts, [Quando] = @.Quando, [LastPost] = @.LastPost WHERE [ID] = @.ID">

<DeleteParameters><asp:ParameterName="ID"Type="Int32"/></DeleteParameters><UpdateParameters><asp:ParameterName="IDD"Type="Int32"/><asp:ParameterName="UserName"Type="String"/><asp:ParameterName="Email"Type="String"/><asp:ParameterName="Title"Type="String"/><asp:ParameterName="Message"Type="String"/><asp:ParameterName="Reason"Type="String"/><asp:ParameterName="Accento"Type="String"/><asp:ParameterName="Views"Type="String"/><asp:ParameterName="Posts"Type="String"/><asp:ParameterName="Quando"Type="DateTime"/><asp:ParameterName="LastPost"Type="String"/><asp:ParameterName="ID"Type="Int32"/></UpdateParameters><InsertParameters><asp:ParameterName="ID"Type="Int32"/><asp:formparametername="IDD"formfield="IDD"/><asp:formparametername="UserName"formfield="UserName"/><asp:formparametername="Email"formfield="Email"/><asp:formparametername="Title"formfield="Title"/><asp:formparametername="Message"formfield="Message"/><asp:formparametername="Reason"formfield="Reason"/><asp:formparametername="Accento"formfield="Accento"/><asp:formparametername="Views"formfield="Views"/><asp:formparametername="Posts"formfield="Posts"/><asp:formparametername="Quando"formfield="Quando"/><asp:formparametername="LastPost"formfield="LastPost"/>

</InsertParameters>|||May be I missunderstood. I have written no SQL code.|||

nodar10:

May be I missunderstood. I have written no SQL code.

Yes you have, on both counts. The InsertCommand, SelectCommand etc are SQL code. Have a look at this to see if you can work out how to get the value of Scope_Identity() in a stored procedure rather than with the SqlDataSource:http://www.eggheadcafe.com/forumpost.aspx?topicid=1&forumpostid=68123

|||

Hi Mike!

You were right with the suggestion for SCOPE_IDENTITY.

I have solved the problem by simply adding ;SELECT @.PK_GuestList = SCOPE_IDENTITY() at the end of the InsertCommand SQL statement , which becomes:

InsertCommand

="INSERT INTO [GuestList] ( [IDD], [UserName],, [Title], [Message], [Reason],

[Accento], [Views], [Posts], [Quando], [LastPost]) VALUES ( @.IDD, @.UserName, @.Email, @.Title,

@.Message, @.Reason, @.Accento, @.Views, @.Posts, @.Quando, @.LastPost);

SELECT @.PK_GuestList= SCOPE_IDENTITY()"

PK_GuestList is an auxiliary variable present in both .aspx and .aspx.vb files, and can be assume any name, but not ID.

Thank you very mauch for your help.

Federico

|||

nodar10:

Hi Mike!

You were right with the suggestion for SCOPE_IDENTITY.

....

Thank you very mauch for your help.

Federico

Then I don't understand why you unmarked my post as answer.

|||

Hi Mike

Sorry for doing some action inadvertely. I just tried to set the flag that my question has been properly answered.

Federico

Retrieving NEWSEQUENTIALID

Hi,
We are thinking of changing the primary key of our table from GUID type to
squential id using the new NEWSEQUENTIALID function. But the issue we are
having is how to efficiently retrieve the generated value because we need to
pass this information back to the client app. With GUID we have a stored
procedure that calls NEWID and then we assign the generated value to the
primary key column. But with NEWSEQUENTIALID it can only be used as DEFAULT
constraint. We came up with a solution to retreive the generated id but the
performance is very slow.
Has anyone come up with a better strategy in doing this?
Thanks.Roy
Read this article in the BOL
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/e06d2cab-f1ff-42f1-8550-
6aaec57be36f.htm
I think , as the BOL suggests to use a scalar UDF to rertieve the value
"Roy" <Roy@.discussions.microsoft.com> wrote in message
news:E88F0D41-C460-4E71-932C-56CB5C5AA24F@.microsoft.com...
> Hi,
> We are thinking of changing the primary key of our table from GUID type to
> squential id using the new NEWSEQUENTIALID function. But the issue we are
> having is how to efficiently retrieve the generated value because we need
> to
> pass this information back to the client app. With GUID we have a stored
> procedure that calls NEWID and then we assign the generated value to the
> primary key column. But with NEWSEQUENTIALID it can only be used as
> DEFAULT
> constraint. We came up with a solution to retreive the generated id but
> the
> performance is very slow.
> Has anyone come up with a better strategy in doing this?
> Thanks.|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O%23wYzjzmGHA.1852@.TK2MSFTNGP03.phx.gbl...
> I think , as the BOL suggests to use a scalar UDF to rertieve the value
Uri,
I'm not sure if we have a different rev of BOL (I'm looking at the
December version), but mine seems to indicate that you CANNOT use a UDF on
NEWSEQUENTIALID... The best option, IMO, is to use an OUTPUT clause:
CREATE TABLE x1
(
col UNIQUEIDENTIFIER DEFAULT(NEWSEQUENTIALID())
)
GO
INSERT x1
OUTPUT inserted.col
DEFAULT VALUES
GO
DROP TABLE x1
GO
--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--

>
> "Roy" <Roy@.discussions.microsoft.com> wrote in message
> news:E88F0D41-C460-4E71-932C-56CB5C5AA24F@.microsoft.com...
>|||DOH!!!! Thanks Adam. I did not read it properly , ( I need to freshen up
myself in the morning)
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OUOE$tzmGHA.3440@.TK2MSFTNGP03.phx.gbl...
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:O%23wYzjzmGHA.1852@.TK2MSFTNGP03.phx.gbl...
> Uri,
> I'm not sure if we have a different rev of BOL (I'm looking at the
> December version), but mine seems to indicate that you CANNOT use a UDF on
> NEWSEQUENTIALID... The best option, IMO, is to use an OUTPUT clause:
> --
> CREATE TABLE x1
> (
> col UNIQUEIDENTIFIER DEFAULT(NEWSEQUENTIALID())
> )
> GO
> INSERT x1
> OUTPUT inserted.col
> DEFAULT VALUES
> GO
> DROP TABLE x1
> GO
> --
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
>|||Another approach is to assign the uniqueidentifier value in application code
rather than in SQL Server. That way, you don't need to retrieve the
assigned value at all because you already know it. To get a sequential GUID
value in application code, call the UuidCreateSequential RPC function.
Hope this helps.
Dan Guzman
SQL Server MVP
"Roy" <Roy@.discussions.microsoft.com> wrote in message
news:E88F0D41-C460-4E71-932C-56CB5C5AA24F@.microsoft.com...
> Hi,
> We are thinking of changing the primary key of our table from GUID type to
> squential id using the new NEWSEQUENTIALID function. But the issue we are
> having is how to efficiently retrieve the generated value because we need
> to
> pass this information back to the client app. With GUID we have a stored
> procedure that calls NEWID and then we assign the generated value to the
> primary key column. But with NEWSEQUENTIALID it can only be used as
> DEFAULT
> constraint. We came up with a solution to retreive the generated id but
> the
> performance is very slow.
> Has anyone come up with a better strategy in doing this?
> Thanks.|||Thanks the response.
However, this is the solution we tried before but we saw a performance
degradation (during lots of inserts) compare to the old way of using GUIDs.
Roy
"Adam Machanic" wrote:

> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:O%23wYzjzmGHA.1852@.TK2MSFTNGP03.phx.gbl...
> Uri,
> I'm not sure if we have a different rev of BOL (I'm looking at the
> December version), but mine seems to indicate that you CANNOT use a UDF on
> NEWSEQUENTIALID... The best option, IMO, is to use an OUTPUT clause:
> --
> CREATE TABLE x1
> (
> col UNIQUEIDENTIFIER DEFAULT(NEWSEQUENTIALID())
> )
> GO
> INSERT x1
> OUTPUT inserted.col
> DEFAULT VALUES
> GO
> DROP TABLE x1
> GO
> --
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
>
>|||Thanks Dan.
We haven't tried this approach yet. We will it give it a try.
Roy
"Dan Guzman" wrote:

> Another approach is to assign the uniqueidentifier value in application co
de
> rather than in SQL Server. That way, you don't need to retrieve the
> assigned value at all because you already know it. To get a sequential GU
ID
> value in application code, call the UuidCreateSequential RPC function.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Roy" <Roy@.discussions.microsoft.com> wrote in message
> news:E88F0D41-C460-4E71-932C-56CB5C5AA24F@.microsoft.com...
>
>

Retrieving Hierarchical Data from a single table

I would like to retrieve a hierarchical list of Product Categories from a single table where the primary key is a ProductCategoryId (int) and there is an index on a ParentProductCategoryId (int) field. In other words, I have a self-referencing table. Categories at the top level of the hierarchy have a ParentProductCategoryId of zero (0). I would like to display the list in a TreeView or similar hierarchical data display control.

Is there a way to retrieve the rows in hierarchical order, sorted by CategoryName within level? I would like to do so from a stored procedure.
Example data:

ProductCategoryID CategoryDescription ParentProductcategoryID ParentCategoryDescription Level
-------------------------------------------
1 Custom Furniture 0 0
2 Boxes 0 0
3 Toys 0 0
4 Bedroom 1 Custom Furniture 1
5 Dining 1 Custom Furniture 1
6 Accessories 1 Custom Furniture 1
7 Picture Frames 6 Accessories 2
8 Serving Trays 6 Accessories 2
9 Entertainment 1 Custom Furniture 1
10 Planes 3 Toys 1
11 Trains 3 Toys 1
12 Boats 3 Toys 1
13 Automobiles 3 Toys 1
14 Jewelry 2 Boxes 1
15 Keepsake 2 Boxes 1
16 Specialty 2 Boxes 1

Desired output:

Custom Furniture
Accessories
Picture Frames
Serving Trays
Bedroom
Dining
Entertainment
Boxes
Jewelry
Keepsake
Specialty
Toys
Automobiles
Boats
Planes
Trains

Hello, if I get you right, here is a very short article that might get you started:http://www.mmkit.com/article.php?sid=345&lang=en_GB

HTH. -LV

|||The answer very much depends on whether you are using Sql Server 2005 or an older version. Assuming 2005 then have a read ofHeirarchical Queries in Sql Server 2005.|||Thanks much. This was exactly what I needed. I don't think I could have come up with this solution on my on at this point.|||

--Copy the code to run on your machine. I named your table as TreeSource with columns needed.

IF(SELECTOBJECT_ID('TreeSource','U'))ISNOTNULL

BEGIN

DROPTABLE TreeSource

END

GO

CREATETABLE [dbo].[TreeSource](

[ProductCategoryID] [int]NULL,

[CatDescription] [nvarchar](255)COLLATE SQL_Latin1_General_CP1_CI_ASNULL,

[ParentProductCategoryID] [int]NULL

)ON [PRIMARY]

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(1,'Custom Furniture',NULL)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(2,'Boxes',NULL)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(3,'Toys',NULL)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(4,'Bedroom',1)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(5,'Dining',1)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(6,'Accessories',1)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(7,'Picture Frames',6)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(8,'Serving Trays',6)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(9,'Entertainment',1)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(10,'Planes',3)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(11,'Trains',3)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(12,'Boats',3)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(13,'Automobiles',3)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(14,'Jewelry',2)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(15,'Keepsake',2)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(16,'Specialty',2)

--step 0

CREATETABLE #Tree(

NodeintNOTNULLIDENTITY(100, 1),

ParentNodeint,

ProductCategoryIDintNOTNULL,

Depthtinyint,

Lineagevarchar(50))

--step 1

INSERTINTO #Tree(ProductCategoryID)SELECT ProductCategoryIDFROM TreeSourceORDERBY ParentProductCategoryID,CatDescription

Go

UPDATE TSET T.ParentNode=P.Node

FROM dbo.#Tree T

INNERJOIN TreeSource EON T.ProductCategoryID=E.ProductCategoryID

INNERJOIN TreeSource BON E.ParentProductCategoryID=B.ProductCategoryID

INNERJOIN dbo.#Tree PON B.ProductCategoryID=P.ProductCategoryID

GO

--step 3

UPDATE #TreeSET Lineage='.', Depth=0WHERE ParentNodeIsNull

--step 4

WHILEEXISTS(SELECT*FROM #TreeWHERE DepthIsNull)

UPDATE TSET T.depth= P.Depth+ 1,

T.Lineage= P.Lineage+Ltrim(Str(T.ParentNode,4,0))+'.'

FROM #TreeAS T

INNERJOIN #TreeAS PON(T.ParentNode=P.Node)

WHERE P.Depth>=0

AND P.LineageIsNotNull

AND T.DepthIsNull

--step 5 final

SELECTSpace(T.Depth*4)+ E.CatDescriptionASName

FROM TreeSource E

INNERJOIN #Tree TON E.ProductCategoryID=T.ProductCategoryID

ORDERBY T.Lineage+Ltrim(Str(T.Node,6,0))

--SELECT * FROM #Tree

DROPTable #Tree

--read more http://www.sqlteam.com/item.asp?ItemID=8866

Retrieving GUID from INSERT query -- HELP

I have tried many code sample but I am very stuck

The primary key of my database (SQL server 2005) table is a uniqueidentifier.

I am using the following code to insert a row into my table:

myCommand.CommandText = sqlEvent.ToString(); //add the sql query to the command
myCommand.Connection = this.dbConnection; //add the database connection to the command
myCommand.ExecuteNonQuery(); //execute the insert query

I need to retrieve the GUID that is automatically generated when the insert command is executed.

Can someone help me? How do I get the GUID that is automatically generated? I have tried lots of things like using

string _id = (string)myCommand.ExecuteScalar();

and I am still stuck. I will really appreciate it if someone can refer me to some code sample.

HELP

Here is a sample:

CREATE TABLE T1(
id uniqueidentifier PRIMARY KEY DEFAULT(NEWID()),
name nvarchar(100)
);

INSERT INTO T1 (name) OUTPUT inserted.id VALUES('name 1');

SELECT * from T1;

So you just need to call T-SQL similar to "INSERT INTO T1 (name) OUTPUT inserted.id VALUES('name 1');" with ExecuteScalar();

Thanks,

Zuomin

Retrieve value within a data flow

hi,

I am doing a data flow to insert data into a dimension table(A) and master surrogate key table(B). Master surrogate key table(B) will be inserted prior to dimension table(A). My dimension table(A)'s key is depend on the last key in master surrogate key table(B). The data flow started from a flat file and checking if the key found in dimension table. If not found, insert a record into B. Then generate the new record to A with max id from B.

My question is how to retrieve the max id in master surrogate key table(B) in a data flow and use it as an input to my dimension table?

Have you tried the LOOKUP component? That's the sort of thing it is designed to do.

-Jamie

Retrieve the next auto-increment number

HI,

I have one master table and multiple detail tables. The primary key of the master table is an auto-increment number, which is a foreign key in those detail tables. I am wondering if SQL Server allows us to get the next available auto-increment number of the master table up front. Thanks a lot.

No, you can only get it after it is created. To do this you would have to lock the table until you used the value or you could get collisions if two users were doing the same thing simultaneously.

Use scope_identity() to get the last inserted identity value.

|||hi ywchen,

You can obtain the next no. in advance by using the ff:
SELECT @.nxtId = MAX(RecId) + 1 FROM MasterTable

However, you will run into problems when multiple users will be inserting in your master table as you might get the same Id.

Since your primary is an auto-increment field, the next identity will only be available upon insert of row. From then on you will be able to get the current Id using the ff :
1.) @.@.IDENTITY - will give you the last identity inserted of any table
2.) IDENT_CURRENT(<table>) - will give you the last identity insert of a specified table, but the scope of insert is of global.i.e. You don't have control where that insert originated.
3.) Scope_Identity() - will give you the identity of a table when there is a row inserted, BUT on its current scope. i.e. Your calling insert procedure scope, regardless of any insert from other insert procedure.

HTH|||

>>1.) @.@.IDENTITY - will give you the last identity inserted of any table

This is not completely true, @.@.IDENTITY will give you the identity of the original table or any tables used for inserts in triggers on the original table

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

yup sorry for not elaborating.. and AFAIK that's the only place(triggers) where @.@.IDENTITY is useful anymore as it is supplanted by the two 2 functions for SQL2K and above...

|||Of course you have to make sure that the trigger doesn't cause another trigger to fire, again invalidating the @.@.identity :)|||Another issue that you need to be aware of is if an insert into the master table fails (eg a transaction rolls back, or an insert violates a constraint), then the identity field will still be incremented, and thus will be two greater than the max value of that column. This is due to the nature of an identity column, there is no way around this, and I would presume this would break any code that is dependent on knowing what the IDENTITY value is before it is generated.

retrieve Primary key value from OLAP Cube

I want to retrieve Primary key value from my OLAP Cube. can you please tell
me how can i do this in below query?
SELECT NON EMPTY { [Measures].[Total Test Count] } ON COLUMNS,
NON EMPTY { ([Dim Station].[Station Name].[Station Name].&&
#91;1ST CHOICE
EMISSIONS & INSPECTIONS] *
[Dim Test Cycle].[Test Cycle].[Test Cycle].ALLMEMBERS *
[Dim OverallResult].[Overall Result].[Overall Result].ALLMEMBERS
) } ON ROWS
FROM [OLAP Test Cube]
Dinesh Patelthere is no primary keys in a cube.
I presume that you want to retrieve the drill through result?
The drill through command return the row from the source database which are
used to fill the targeted cell.
search on the web for samples and syntaxes.
"Dinesh Patel" <DineshPatel@.discussions.microsoft.com> wrote in message
news:1EA5DE29-39D8-4002-BD57-C19998A18832@.microsoft.com...
>I want to retrieve Primary key value from my OLAP Cube. can you please tell
> me how can i do this in below query?
> SELECT NON EMPTY { [Measures].[Total Test Count] } ON COLUMNS
,
> NON EMPTY { ([Dim Station].[Station Name].[Station Name].
&[1ST CHOICE
> EMISSIONS & INSPECTIONS] *
> [Dim Test Cycle].[Test Cycle].[Test Cycle].ALLMEMBERS *
> [Dim OverallResult].[Overall Result].[Overall Result].ALLMEMBE
RS
> ) } ON ROWS
> FROM [OLAP Test Cube]
> Dinesh Patel|||Thanks for info.
"Jeje" wrote:

> there is no primary keys in a cube.
> I presume that you want to retrieve the drill through result?
> The drill through command return the row from the source database which ar
e
> used to fill the targeted cell.
> search on the web for samples and syntaxes.
>
> "Dinesh Patel" <DineshPatel@.discussions.microsoft.com> wrote in message
> news:1EA5DE29-39D8-4002-BD57-C19998A18832@.microsoft.com...
>
>

Retrieve Primary key coloumn by querying system table of Sql server 2000

Hi,
How can i retrieve primary key columns including foreign key ( part of
primary key) by querying system tables of sql server 2000. I want to
know that how many columns are primary key column in table.
Pls help.
Thanks in advance
ShailSee if this helps:
--In the WHERE clause change the table name and table owner to your table
name and owner.
SELECT TC.TABLE_SCHEMA,
TC.TABLE_NAME,
TC.CONSTRAINT_TYPE,
TC.CONSTRAINT_NAME,
KCU.COLUMN_NAME,
KCU.ORDINAL_POSITION
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU
ON TC.TABLE_SCHEMA = KCU.TABLE_SCHEMA
AND TC.TABLE_NAME = KCU.TABLE_NAME
AND TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
WHERE TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
AND TC.TABLE_SCHEMA = 'dbo'
AND TC.TABLE_NAME = 'txsh0hor'
ORDER BY KCU.ORDINAL_POSITION
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<shailbpl@.gmail.com> wrote in message
news:1150279652.629125.32380@.u72g2000cwu.googlegroups.com...
Hi,
How can i retrieve primary key columns including foreign key ( part of
primary key) by querying system tables of sql server 2000. I want to
know that how many columns are primary key column in table.
Pls help.
Thanks in advance
Shail|||Here's an example of getting the columns of a primary key constraint:
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'author
s'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<shailbpl@.gmail.com> wrote in message news:1150279652.629125.32380@.u72g2000cwu.googlegroups
.com...
> Hi,
> How can i retrieve primary key columns including foreign key ( part of
> primary key) by querying system tables of sql server 2000. I want to
> know that how many columns are primary key column in table.
> Pls help.
> Thanks in advance
> Shail
>|||sp_help 'tableName'
OR
select * from information_schema.table_constraints
where constraInt_type IN ('PRIMARY KEY','FOREIGN KEY')
Madhivanan
shailbpl@.gmail.com wrote:
> Hi,
> How can i retrieve primary key columns including foreign key ( part of
> primary key) by querying system tables of sql server 2000. I want to
> know that how many columns are primary key column in table.
> Pls help.
> Thanks in advance
> Shail|||See whether this helps
SELECT a.pktable,A.fktable,a.fkcol,a.[name]
FROM
(SELECT a.*,b.[name]
FROM
(SELECT object_name(fkeyid) as FkTable ,
object_name(rkeyid) as PkTable,
b.name as fkCol ,rkey,rkeyid
FROM sysforeignkeys a
INNER JOIN syscolumns b
ON a.fkey=b.colid and a.fkeyid=b.id ) a
LEFT OUTER JOIN syscolumns b
ON a.rkey=b.colid where a.rkeyid=b.id ) a
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
<shailbpl@.gmail.com> wrote in message
news:1150279652.629125.32380@.u72g2000cwu.googlegroups.com...
> Hi,
> How can i retrieve primary key columns including foreign key ( part of
> primary key) by querying system tables of sql server 2000. I want to
> know that how many columns are primary key column in table.
> Pls help.
> Thanks in advance
> Shail
>