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 |
|
jej1216
Starting Member
27 Posts |
 Posted - 2008-09-26 : 12:39:35
|
I have a script that finds all tables containing a certain field.Here's my script to find the tables:select 'SELECT * FOM PS_',RECNAME,'WHERE EMPLID = ''123456789'''from PSRECFIELDwhere FIELDNAME = 'DEPENDENT_BENEF'and RECNAME NOT LIKE '%_VW'and RECNAME NOT LIKE '%_VW1'and RECNAME NOT LIKE '%_VW2'and RECNAME NOT LIKE '%WK'and RECNAME NOT LIKE '%_TMP'and RECNAME NOT LIKE '%_TMP1'and RECNAME NOT LIKE '%_TMP2'and RECNAME NOT LIKE '%_UK'and RECNAME NOT LIKE '%_JPN'and RECNAME NOT LIKE '%_CAN'and RECNAME NOT LIKE '%_SRCH'and RECNAME NOT LIKE '%_WRK'and RECNAME NOT LIKE 'WRK_%'and RECNAME NOT LIKE 'DERIVED%'and RECNAME NOT LIKE 'GP%'and RECNAME NOT LIKE 'GVT%'and RECNAME NOT LIKE 'UPG%'and RECNAME NOT LIKE '%TAO' I then clean up the results a little (remove spaces) and have my select statements.What I want to do now is add a qualifier to find only tables that have data in them (rows > 0).I tried using 'and @@ROWCOUNT > 0' as a qualifier but I'm still getting tables with zero rows.I can't find what I need in the Transact SQL Help files - can someone point me in the right direction?TIA,jej1216 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-26 : 12:48:44
|
| is PCRECFIELD holding info about all your tables? what all columns it have? |
 |
|
|
jej1216
Starting Member
27 Posts |
Posted - 2008-09-26 : 15:15:41
|
| The fields in PCRECFIELD are:PSRECFIELD.RECNAME PSRECFIELD.FIELDNAME PSRECFIELD.FIELDNUM PSRECFIELD.DEFRECNAME PSRECFIELD.DEFFIELDNAME PSRECFIELD.CURCTLFIELDNAME PSRECFIELD.EDITTABLE PSRECFIELD.USEEDIT PSRECFIELD.USEEDIT2 PSRECFIELD.SUBRECORD PSRECFIELD.SUBRECVER PSRECFIELD.SETCNTRLFLD PSRECFIELD.DEFGUICONTROL PSRECFIELD.LABEL_ID PSRECFIELD.LASTUPDDTTM PSRECFIELD.LASTUPDOPRID PSRECFIELD.TIMEZONEUSE PSRECFIELD.TIMEZONEFIELDNAME PSRECFIELD.RELTMDTFIELDNAME PSRECFIELD.CURRCTLUSE |
|
|
|
|
|
|
How to select only the tables that have data in th