CODEWITHSUNDEEP
sql-servert-sqldatabase-schema
SESHI
SESHI

Reputation: 33

How can we check whether data exists or not in a table thru sys. tables/functions directly

Is there any way to check whether data exists in a table thru sys. tables or functions directly without querying the table.

Any such sys. available?

** Not querying the dynamic sql..:)

Upvotes: 1

Views: 532

Answers (3)

Saneesh kunjunni
Saneesh kunjunni

Reputation: 548

I don't know still anyone searching for this code or not But I tried this code which works for me

SELECT pa.rows RowCnt,ta.name
 FROM sys.tables ta
 cross apply (select top 1 rows from sys.partitions pa
 where pa.OBJECT_ID = ta.OBJECT_ID) pa
   order by 1,2

Upvotes: 0

SESHI
SESHI

Reputation: 33

-- This is how I got the result... 
SELECT  Distinct tbl.name, C.name  , X.rowcnt              
FROM        Sys.Columns          as     c
INNER JOIN  Sys.Tables           as     tbl
ON          tbl.object_id        =             c.object_id
INNER JOIN  Sys.Types            as     t
ON          c.system_type_id     =             t.system_type_id
INNER JOIN  Sys.Indexes     I
ON  I.object_id =       Tbl.object_id
Inner Join  Sys.sysindexes  X
On  I.index_id      =       X.indid
And I.object_id =       X.id
WHERE       X.rowcnt > 0
ORDER BY    tbl.name

Upvotes: 0

valex
valex

Reputation: 24144

Read this article:

Find Row Count in Table – Find Largest Table in Database

Here is a query to find a ROWCOUNT of a table:

SELECT SUM(pa.rows) RowCnt
 FROM sys.tables ta
 INNER JOIN sys.partitions pa
 ON pa.OBJECT_ID = ta.OBJECT_ID

 WHERE ta.is_ms_shipped = 0 
       AND pa.index_id IN (1,0)
       AND ta.name='table1'

SQLFiddle demo

Or if you need only information about empty table or not then something like this:

SELECT 
ISNULL(
(SELECT TOP 1 1 from sys.partitions pa 
                       where pa.OBJECT_ID = ta.OBJECT_ID
                             AND
                             pa.rows>0
                             AND 
                             pa.index_id IN (1,0)                  
                 )
,0) as TableIsNotEmpty
 FROM sys.tables ta
 WHERE ta.is_ms_shipped = 0 
       AND ta.name='table1'

Upvotes: 1

Related Questions