how to count table used in a query

hi all
this is my error

Could not allocate ancillary table for view or function resolution. The maximum number of tables in a query (260) was exceeded.

in future i want avoid this error.
i want count how many tables in the view

any program available in market

Thanks
S. Ramesh

HI,

You can just pass this query to find out which column or tables are using in a perticular DB.

SELECT * FROM INFORMATION_SCHEMA.VIEWS

Regards

Shekhar Mishra

"No passion in the world is equal to the passion to alter someone else's draft."

Was partitioned view involved there?

Perhaps over-normalization? I've seen systems like that, where all FK columns are on a substitute key, and you need to add the RK table to your queries to read the 'natural' key.

Some people seem to think identity columns are a performance boost in all thinkable situations. I think not.

I don't think that is the matter of using surragate keys. Either partitioned view with too much partitions is involved or it is bad design, specifically intention to create one or a couple over-general views that will cover every possible query.

... and perhaps it's both: over-general views on top of over-normalized tables.

It may be, but I am afraid I don't agree with you about the use of surogate keys. However, I don't have time right now to discuss such "religious" topic. I guess once we both have enough time, we can start a thread about it.

I'm not religuous about anything.[<img src='/community/emoticons/emotion-2.gif' alt='' />]

<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br /><br />I'm not religuous about anything.[<img src='/community/emoticons/emotion-2.gif' alt='' />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />I know you are not, but that topic is, believe me. [<img src='/community/emoticons/emotion-1.gif' alt='' />]<br />Just ask Frank. Besides the newsgroup he mentioned I saw a couple of his posts in related threads on different forums. I have seen he gave up immediately after figuring out that will be never ending flame with both sides shouting at each other. [<img src='/community/emoticons/emotion-6.gif' alt='' />]

Flame wars on other forums should not imply that <i>we</i> cannot have a civil discussion on the same subject.[<img src='/community/emoticons/emotion-5.gif' alt='' />]

I believe it wouldn't be flame war here, but I am sure it would be much more time consuming then I can afford right now. However, you are free to start one if you really want, I may join later when I have enough time. [<img src='/community/emoticons/emotion-1.gif' alt='' />]

Frank: Yes, I know them but I resisted to contribute [<img src='/community/emoticons/emotion-1.gif' alt='' />] I also remember discussion about how it is bad to use stored procedures in sql server and another discussion(s) about how great/bad are orm tools.

I really don't frequent other database forums. The same for other forums - you stick to the civil and polite ones.

Mirko, I don't think I'll go into even a civil discussion on surrogate keys and their uses. We are probably not working in systems of similar sizes - with me obviously on the 'small scale' end of the spectrum.

These topics were about articles I read. Besides, you see how many responses we produced after just one touch of "surogate against natural key" topic. [<img src='/community/emoticons/emotion-5.gif' alt='' />]

Hi all
no one give correct solution. why i asked this question?
if i get the answer, i will reduce the table links for the particular view.
select * from viewProd
viewprod -- viewa ---- fnout1
viewb ---- fnout3
viewc ---- view10
viewd ---- view8

this is my query. if i get the answer, i will solve the problem.
please dont fight


Thanks
S. Ramesh

Apologies for the thread hijack!

You can check the dependencies for each view in Enterprise Manager. Right-click the view, then select All Tasks > Display Dependencies.

You can also run a query against the appropriate INFORMATION_SCHEMA view - insert the name of the query that you're looking for:

SELECT i.TABLE_NAME
FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE i
WHERE i.VIEW_NAME = '..................'

Note that this query will also return UDFs called from the view, and underlying views. If I understand correctly, the tables in underlying views also count.

This may already give you an indication of the 'heaviest' views:

SELECT i.VIEW_NAME, COUNT(*)
FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE i
GROUP BY i.VIEW_NAME
ORDER BY 2 DESC, 1 ASC

FIX: A Transact-SQL query that uses views may fail unexpectedly in SQL Server 2000 SP3

http://support.microsoft.com/kb/828269/en-us