Table name an View query

Question

Post reply

Table name an View query

peleg

SSCrazy

Points: 2611
More actions
April 6, 2009 at 8:14 am

#130358

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

Viewing 12 posts - 1 through 11 (of 11 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

if you run the command

EXEC sp_depends YourViewName

it will give you two sets of tables: the first is the sources of the tables/views and columns that that view refernces to get it's data;

the second is any other views or functions that depend on THAT view.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

peleg SSCrazy Points: 2611 More actions · Answer 2

first i use it with SQL2005 (mymistake)

and the result i get is empty

the thing is that i need the result for each row in 1 query

thanks

peleg

Lowell SSC Guru Points: 323518 More actions · Answer 3

it's not clear what you want. you've got to show us an example of your expected output, because you can't really mix schema info with datainfo....

what query are you using?

since EACH column in a query potentially has a differnet tablename.columnname, if you select 4 columns, you have 4 Tablename.Columnnames, which is not really related to the data.

you want something like id,name, [tableOfIid.columnOfID],[tableOfIname.columnOfName]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323518 More actions · Answer 4

the easiest thing, thinking about it is to avoid trying to add the "source" as a part of the datarow and rename the columns in your view to identify the source...

ie

Create View VW_Stuff

As

Select sysobjects.id as [sysobjects.id ],

sysobjects.name as [sysobjects.name],

syscolumns.name as [syscolumns.name]

from sysobjects

inner join syscolumns on sysobjects.id = syscolumns.id

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

peleg SSCrazy Points: 2611 More actions · Answer 5

what i want for each row :

col1,col2,tableOfIname

Lowell SSC Guru Points: 323518 More actions · Answer 6

like i said, each column can have a different table...if you have 40 columns in the view, you are asking for 80 total? is that right?

what is the exact query you are using?

what is the text/definition of the view?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

peleg SSCrazy Points: 2611 More actions · Answer 7

the view is like this :

select col1 from tbl1

union all

select col1 from tbl2

......

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 8

peleg k (4/6/2009)
the view is like this :
select col1 from tbl1
union all
select col1 from tbl2
......

If your view is a union all view like the above, then all you need to do is add a source column and identify the table. For example:

CREATE VIEW dbo.YourView AS

SELECT 'Tbl1' AS SOURCE, col1, col2 FROM tbl1

UNION ALL

SELECT 'Tbl2' AS SOURCE, col1, col2 FROM tbl2

GO

Just note, if any of the unioned queries come from more than a single table this won't work.

Please review the article in my signature about how to post - it will help a lot for future posts.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

peleg SSCrazy Points: 2611 More actions · Answer 9

again!

i lnow that solution,but i dont want to use it,

i was lookin to get the same result,but in another way if possible

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 10

peleg k (4/6/2009)
again!
i lnow that solution,but i dont want to use it,
i was lookin to get the same result,but in another way if possible

Sorry - what other way were you thinking of? I don't know of any other way to do what you are looking for.

What I would suggest for you is to review the article in my signature and reconsider posting the problem you are trying to solve and not the solution. If we had a better idea of the problem you are trying to solve - we could definitely come up with several possible solutions.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

peleg SSCrazy Points: 2611 More actions · Answer 11

peleg

SSCrazy

Points: 2611

April 6, 2009 at 3:28 pm

#972946

ok

thanks for the help anyway 🙂