Query columns of table value parameter

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Query columns of table value parameter

Author

Topic

talg
Starting Member

2 Posts

Posted - 2009-11-18 : 10:00:59

How can I query for column name information of table value parameter or of a table type.
sp_columns works only for real tables.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-11-18 : 11:02:07

Why would you ever want to do that?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-11-18 : 11:05:53

Still not sure what you are trying to achieve but you can do this:


DECLARE @foo TABLE (
	[a] INT
	, [b] VARCHAR(100)
	, [my_definite_column_name] VARCHAR(50)
	)

SELECT
	tab.*
FROM
	tempDB.information_Schema.columns col
	JOIN tempDB.information_Schema.columns tab ON tab.[table_name] = col.[table_name]
WHERE
	col.[column_name] = 'my_definite_column_name'

As a table variable gets an entry in the tempdb with a system generated name. If you know the name of a column you can find out.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

talg
Starting Member

2 Posts

Posted - 2009-11-19 : 02:58:44

Thanx, but it is not good enough since I do not have a unique column name.
The reason why I need it is because I'm having store procedure that receives input of table value parameter.
I need to update a real table with values from this parameter, but the fields in this table value parameter may changed during run time.
E.g.
CREATE Procedure [dbo].[spUpdateTbl2](@tbl2 tbl2Type)
As
UPDATE Tbl2
SET Tbl2.???1 = @tbl2.???1 , Tbl2.???2 = @tbl2.???2

After the creation of this store procedure, the tbl2Type may vary and fields might be added/deleted from it (those fields exist in Tbl2), so if i could query for fields name I could create this update command dynamically in the store procedure.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-11-19 : 04:29:49

Surely you'd have to use dynamic sql for that. and I don't think the table variable will exist in the scope of the dynamic call. (they don't in 2005).

I think you'll need someone with more 2008 experience for your question.

Your approach sounds ill thought out to me.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources