Name of table memorized into a variable

Question

Post reply

Name of table memorized into a variable

Flavio

Right there with Babe

Points: 793
More actions
November 26, 2003 at 7:26 am

#82869

Help me Please!
I have this problem
I have a variable (for example @Table_Name varchar(50) ) that contain the name of a table.
Now
I want know the name of the field/s that constitute the key of this table!
Because I want to memorize into a variables the name of column/s keys
Please Help me
Thanks !!!
Bye bye
Flavio

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

DavidT SSCommitted Points: 1727 More actions · Answer 1

Try looking at INFORMATION_SCHEMA views in Books On-line:

This select will pull out the column names that make up the (Primary) key.

declare @TableName varchar(255)

set @TableName = 'TABLE NAME'

select COLUMN_NAME

from INFORMATION_SCHEMA.KEY_COLUMN_USAGE

inner join INFORMATION_SCHEMA.TABLE_CONSTRAINTS on INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME = INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME

where INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE= 'PRIMARY KEY'

and INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME = @TableName

Flavio Right there with Babe Points: 793 More actions · Answer 2

Thanks DavidT

But, my principal problem is that the value of the variable @TableName must be of the type:

SET @TableName = '<name_database>.<dbo>.<name_table>'

I have used the system stored procedure sp_pkeys, but, for example, the statement

sp_pkeys '<name_database>.<dbo>.<name_table>'

not return a set of key, if the current database is not <name_database>

DavidT, your solution presents the same problem.

Thanks DavidT

Can you help me?

bye bye

Flavio

Jonathan SSC-Insane Points: 20427 More actions · Answer 3



DECLARE @sql varchar(200)

CREATE TABLE #t(db sysname, own sysname, tab sysname, Col sysname, Seq tinyint, PKName sysname)

SET @sql = PARSENAME(@TableName,3) + '.dbo.sp_pkeys ' + PARSENAME(@TableName,1) + ',' + PARSENAME(@TableName,2)

INSERT #t

EXEC(@sql)

SELECT Col

FROM #t

ORDER BY Seq

--Jonathan

Edited by - Jonathan on 11/26/2003 10:30:51 AM

--Jonathan

Flavio Right there with Babe Points: 793 More actions · Answer 4

Fantastic Jonathan!

Your statement it's OK!

Thank you very much.

I have learned a new thing of T-SQL

Bye bye

Flavio

PS

Thanks DavidT !

DavidT SSCommitted Points: 1727 More actions · Answer 5

Ahh, sp_pkeys, have to remember that one!

beats my method!! =:)

noeld SSC Guru Points: 96590 More actions · Answer 6

quote:
DECLARE @sql varchar(200)
CREATE TABLE #t(db sysname, own sysname, tab sysname, Col sysname, Seq tinyint, PKName sysname)
SET @sql = PARSENAME(@table,3) + '.' + PARSENAME(@Table,2) + '.sp_pkeys ' + PARSENAME(@Table,1)
INSERT #tEXEC(@sql)
SELECT ColFROM #tORDER BY Seq

DECLARE @sql varchar(200)

CREATE TABLE #t(db sysname, own sysname, tab sysname, Col sysname, Seq tinyint, PKName sysname)

SET @sql = PARSENAME(@table,3) + '.' + PARSENAME(@Table,2) + '.sp_pkeys [ ' + PARSENAME(@Table,1) + ']'

INSERT #t EXEC(@sql)

SELECT Col FROM #t ORDER BY Seq

Have you thought about Multiple Words in table names Like Order Details

🙂

* Noel

Jonathan SSC-Insane Points: 20427 More actions · Answer 7

quote:
Have you thought about Multiple Words in table names Like Order Details
🙂

For that matter, databases and owners are also just subject to the same identifier rules, so they could also be delimited identifiers. Not using regular identifiers is, as far as I'm concerned, a bad practice. You could wrap all three parsed pieces in QUOTENAME().

--Jonathan

noeld SSC Guru Points: 96590 More actions · Answer 8

quote:
...
Not using regular identifiers is, as far as I'm concerned, a bad practice ...

you should tell that to MS and Northwind Creators [:))]

* Noel