Table name an View query - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Table name an View query

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2009-04-06 : 10:14:44

i have a query on a view, and i want for each row in the result to know from which table it was taken
how can i do this?

Thanks
Peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-06 : 10:18:36

Based on your question I'll guess that your view is several UNION ALLed select statements. If so you can include a constant expression as one of the columns:

select 'Table1' as sourceTable, <otherColumns> from table1
union all
select 'table2' as sourceTable, <otherColumns> from table2

Be One with the Optimizer
TG

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2009-04-06 : 10:39:27

yes i use UNION ALL
an no, i dont want to use that way
i prefer it as a part of the QUERY, and not hardcoded in the view

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-06 : 10:52:39

quote:
Originally posted by pelegk2

yes i use UNION ALL
an no, i dont want to use that way
i prefer it as a part of the QUERY, and not hardcoded in the view

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Then, other possible solution is to have a column added in those tables with default value of table name and use that column to identity the table

Madhivanan

Failing to plan is Planning to fail

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-06 : 11:17:47

I guess what we're saying is that there is no way to know which table a specific row comes from without providing a column to indicate that.

>>i want for each row in the result to know from which table it was taken
What kind of indication in the result set were you envisioning to know this information?

Be One with the Optimizer
TG

svicky9
Posting Yak Master

232 Posts

Posted - 2009-04-06 : 11:34:23

use sp_depends 'ViewName' or sp_depends 'ProcedureName'

http://www.sqlserver007.com/sql-server-views-dependencies

http://sqlserver007.wordpress.com

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2009-04-06 : 15:26:19

can i put the result into a table?
if yes then how?
thnaks
Peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-07 : 01:45:31

quote:
Originally posted by svicky9

use sp_depends 'ViewName' or sp_depends 'ProcedureName'

http://www.sqlserver007.com/sql-server-views-dependencies

http://sqlserver007.wordpress.com

I dont think this will answer to the question

Madhivanan

Failing to plan is Planning to fail

svicky9
Posting Yak Master

232 Posts

Posted - 2009-04-07 : 03:31:51

quote:
Originally posted by pelegk2

can i put the result into a table?
if yes then how?
thnaks
Peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Yes use

create a temp table with the colums

like

create table #tmp
(
Name varchar(100)
,Type varchar(100)
,Updated Varchar(100)
,selected varchar(10)
,[Column] varchar(100)
)

insert into #tmp
exec sp_Depends 'ViewName'

http://www.sqlserver007.com

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2009-04-07 : 04:42:30

10X

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-07 : 05:05:59

sp_helptext sp_Depends

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources