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:
>
Showing posts with label foreign. Show all posts
Showing posts with label foreign. Show all posts
Monday, March 26, 2012
Monday, March 12, 2012
Retrieving constraint information
Hello
I wrote this query to retrieve all constraints (primary keys, foreign keys, unique keys, checks) on a table.
SELECT
c.name AS name,
CASE
WHEN c.xtype = 'PK' THEN 'primary'
WHEN c.xtype = 'F' THEN 'foreign'
WHEN c.xtype = 'UQ' THEN 'unique'
WHEN c.xtype = 'C' THEN 'check'
END AS type,
tkt.name AS contable,
tkc.name AS confield,
fkt.name AS reftable,
fkc.name AS reffield,
com.text AS expr
FROM
sysobjects c
LEFT JOIN sysconstraints con ON con.constid = c.id
LEFT JOIN sysforeignkeys fks ON fks.constid = con.constid
LEFT JOIN sysobjects tkt ON tkt.id = con.id
LEFT JOIN syscolumns tkc ON tkc.id = tkt.id AND tkc.colid = con.colid
LEFT JOIN sysobjects fkt ON fkt.id = fks.rkeyid
LEFT JOIN syscolumns fkc ON fkc.id = fkt.id AND fkc.colid = fks.rkey
LEFT JOIN syscomments com ON com.id = c.id
WHERE
c.xtype IN ('PK', 'F', 'UQ', 'C')
AND tkt.name = '$table'
AND c.name = '$constraint'
It returns a row for each constraint which can be easilly stored in an associative array.
Array (
[name], //name of the constraint
[type], //(primary|foreign|unique|check)
[contable], //table the constraint is on
[confield], //field the constraint is on
[reftable], //referenced table, null if type!=foreign
[reffield], //referenced field, null if type!=foreign
[expr], //check expression, null if type!=check
)
Everything works as expected except for one issue. For primary keys and unique keys, the sysconstraints.colid field is always '0'. The sysconstraints.id field properly indicates the id of the table that the primary/unique key is on, however I have no way of knowing which column(s) the primary/unique key is on. According to the Transact-SQL reference, the sysconstraints.colid field is the "ID of the column on which the constraint is defined, 0 if a table constraint.". Therefore, it looks like primary/unique constraints are stored as table constraints instead as a primary/unique constraint. However the sysconstraints.status field indicates the type of constraint to be a primary constraint or a unique constraint, not a table constraint.
Pseudo-bit-mask indicating the status. Possible values include:
1 = PRIMARY KEY constraint.
2 = UNIQUE KEY constraint.
3 = FOREIGN KEY constraint.
4 = CHECK constraint.
5 = DEFAULT constraint.
16 = Column-level constraint.
32 = Table-level constraint.
Is there something I am missing? Or maybe there is a better way to find the columns of a primary key or unique key that I can integrate into my above query?
Thanks
-except10nUse INFORMATION_SCHEMA instead. I have done almost the same what you are trying to do and late I understood that this is not the best way to use internal tables. Every SP can change something and you will have a problem.|||Use INFORMATION_SCHEMA instead. I have done almost the same what you are trying to do and late I understood that this is not the best way to use internal tables. Every SP can change something and you will have a problem.
Unfortunately I can't use INFORMATION_SCHEMA for reasons that would take too long to explain at the moment. In your past experiences, were you able to achieve what I am trying to do using the standard system tables?
I wrote this query to retrieve all constraints (primary keys, foreign keys, unique keys, checks) on a table.
SELECT
c.name AS name,
CASE
WHEN c.xtype = 'PK' THEN 'primary'
WHEN c.xtype = 'F' THEN 'foreign'
WHEN c.xtype = 'UQ' THEN 'unique'
WHEN c.xtype = 'C' THEN 'check'
END AS type,
tkt.name AS contable,
tkc.name AS confield,
fkt.name AS reftable,
fkc.name AS reffield,
com.text AS expr
FROM
sysobjects c
LEFT JOIN sysconstraints con ON con.constid = c.id
LEFT JOIN sysforeignkeys fks ON fks.constid = con.constid
LEFT JOIN sysobjects tkt ON tkt.id = con.id
LEFT JOIN syscolumns tkc ON tkc.id = tkt.id AND tkc.colid = con.colid
LEFT JOIN sysobjects fkt ON fkt.id = fks.rkeyid
LEFT JOIN syscolumns fkc ON fkc.id = fkt.id AND fkc.colid = fks.rkey
LEFT JOIN syscomments com ON com.id = c.id
WHERE
c.xtype IN ('PK', 'F', 'UQ', 'C')
AND tkt.name = '$table'
AND c.name = '$constraint'
It returns a row for each constraint which can be easilly stored in an associative array.
Array (
[name], //name of the constraint
[type], //(primary|foreign|unique|check)
[contable], //table the constraint is on
[confield], //field the constraint is on
[reftable], //referenced table, null if type!=foreign
[reffield], //referenced field, null if type!=foreign
[expr], //check expression, null if type!=check
)
Everything works as expected except for one issue. For primary keys and unique keys, the sysconstraints.colid field is always '0'. The sysconstraints.id field properly indicates the id of the table that the primary/unique key is on, however I have no way of knowing which column(s) the primary/unique key is on. According to the Transact-SQL reference, the sysconstraints.colid field is the "ID of the column on which the constraint is defined, 0 if a table constraint.". Therefore, it looks like primary/unique constraints are stored as table constraints instead as a primary/unique constraint. However the sysconstraints.status field indicates the type of constraint to be a primary constraint or a unique constraint, not a table constraint.
Pseudo-bit-mask indicating the status. Possible values include:
1 = PRIMARY KEY constraint.
2 = UNIQUE KEY constraint.
3 = FOREIGN KEY constraint.
4 = CHECK constraint.
5 = DEFAULT constraint.
16 = Column-level constraint.
32 = Table-level constraint.
Is there something I am missing? Or maybe there is a better way to find the columns of a primary key or unique key that I can integrate into my above query?
Thanks
-except10nUse INFORMATION_SCHEMA instead. I have done almost the same what you are trying to do and late I understood that this is not the best way to use internal tables. Every SP can change something and you will have a problem.|||Use INFORMATION_SCHEMA instead. I have done almost the same what you are trying to do and late I understood that this is not the best way to use internal tables. Every SP can change something and you will have a problem.
Unfortunately I can't use INFORMATION_SCHEMA for reasons that would take too long to explain at the moment. In your past experiences, were you able to achieve what I am trying to do using the standard system tables?
Wednesday, March 7, 2012
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...
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
>
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
>
Subscribe to:
Posts (Atom)