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.
Author |
Topic |
PeterG
Posting Yak Master
156 Posts |
Posted - 2002-05-02 : 15:46:20
|
I have 12 tables (table1, table2, ... table12) and in my stored proc, I need to check whether each table is populated. I don't need to check them all, but the number of tables that should be checked depend on the number I pass to the stored procedure. If I pass 5, then only the first 5 tables must be checked. What should I return to my asp page? Perhaps return a string of true/false values joined by a character (say a % sign) and then parse the string in my asp page?I need your help. Thanks. |
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-02 : 15:52:37
|
OK, here's the thing: what identifies 5 tables as being the "first" five, or the "last" five? Do the tables have numbers in their names that signify this? |
|
|
PeterG
Posting Yak Master
156 Posts |
Posted - 2002-05-02 : 15:54:55
|
table1 will be the first table and table12 will be the last. |
|
|
PeterG
Posting Yak Master
156 Posts |
Posted - 2002-05-02 : 16:06:59
|
Here's an attempt at it:CREATE PROCEDURE FreeKeyOrNot @numofcats intASDeclare @counter int, @retvalue varchar(50)Set @counter = 1Set @retvalue = ''while @counter < @numofcats begin if exists ( Select lngCodeID from table1 ) Set @retvalue = @retvalue + '1' else Set @retvalue = @retvalue + '0' Set @counter = @counter + 1 return @retvalue endI don't know how to turn the if statement to check table1, table2, etc. as it loops. Any idea? |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-02 : 16:17:16
|
Actually, you don't even need to loop this, since the table names are relatively fixed, you can query the sysindexes table to get the row count:CREATE PROCEDURE ShowRowCounts @maxtblID int ASSELECT O.name, I.rowcnt FROM sysindexes I INNER JOIN sysobjects O on I.id=O.id WHERE I.indid < 2 AND O.xtype='U' AND (o.name Like 'table[1-9]' OR o.name Like 'table1[012]') AND Convert(int,SubString(o.name,6,99)) <= @maxtblIDThis will select the tables from sysobjects and sysindexes and return the number of rows in each table.Edited by - robvolk on 05/02/2002 16:18:52 |
|
|
PeterG
Posting Yak Master
156 Posts |
Posted - 2002-05-02 : 16:29:43
|
Thanks for writing that stored proc for me. I forgot to mention that I also need to pass the server name and database name as the tables may reside on diff servers and DBs. How can I incorporate these info into that stored proc? Thanks again. |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-05-02 : 19:42:37
|
Are you saying that the fully-qualified table names would be something like:Server1.db1.dbo.Table1Server1.db5.dbo.Table2Server4.db2.dbo.Table3and so on? Then Rob's Sysindexes query won't work. You might be able to do a little Dynamic SQL or maybe just hard code some IF @numofcats > X lines.Will the number of tables remain static, or will they also be changing? |
|
|
|
|
|
|
|