Sql-server – how to find number of empty tables in any schema

sql serversql-server-2005

I want to query how to find all the empty tables in my schema HR
i already tried with
select *
from dba_tables
where owner='HR' and num-rows is null;
but i dont know if this is the correct logic or not

Best Answer

When you say 'empty' tables, I take that to mean tables with zero rows.

Using the example in this post as a starting point,

WITH TablesAndRowCounts
AS (
    SELECT SCHEMA_NAME(sc.schema_id) SchemaName
        ,sc.NAME + '.' + ta.NAME TableName
        ,SUM(pa.rows) RowCnt
    FROM sys.tables ta
    INNER JOIN sys.partitions pa ON pa.OBJECT_ID = ta.OBJECT_ID
    INNER JOIN sys.schemas sc ON ta.schema_id = sc.schema_id
    WHERE ta.is_ms_shipped = 0
        AND pa.index_id IN (1,0)
    GROUP BY sc.schema_id
        ,sc.NAME
        ,ta.NAME
    )
SELECT *
FROM TablesAndRowCounts
WHERE rowcnt = 0
and SchemaName = 'HR'

Related Solutions

Sql-server – SQL Server: How to call stored procedure without schema name

Best practice is always qualify schema for all object references. See this by Tibor Karaszi or this by Midnight DBA or just trust me or the MS SQL Server Best Practice Analyzer

But you can run this if you choose to ignore best practice:

ALTER SCHEMA dbo TRANSFER John.mySP_Name;

SQL Server – IF NOT NULL Then UPDATE Else Keep Value

I think this will solve the issue:

UPDATE DeviceAttribute
SET Details = CASE Name
          WHEN 'Accessories'   THEN COALESCE(@Accessories, Details)
          WHEN 'Description'   THEN COALESCE(@Description, Details)
          WHEN 'Specification' THEN COALESCE(@Specification, Details)
          ELSE Details
              END
WHERE DeviceID = 10 ;

or this (to avoid redundant updates):

UPDATE DeviceAttribute
SET Details = CASE Name
          WHEN 'Accessories'   THEN @Accessories
          WHEN 'Description'   THEN @Description
          WHEN 'Specification' THEN @Specification
          ELSE Details
              END
WHERE DeviceID = 10 
  AND ( Name = 'Accessories'   AND @Accessories IS NOT NULL
     OR Name = 'Description'   AND @Description IS NOT NULL
     OR Name = 'Specification' AND @Specification IS NOT NULL
      ) ;

or this, using a table value constructor:

UPDATE da
SET da.Details = upd.Details
FROM DeviceAttribute AS da
  JOIN
    ( VALUES
      ('Accessories'   , @Accessories),
      ('Description'   , @Description),
      ('Specification' , @Specification)
    ) AS upd (Name, Details)
    ON upd.Name = da.Name 
WHERE da.DeviceID = 10
  AND upd.Details IS NOT NULL ;

Best Answer

Related Solutions

Sql-server – SQL Server: How to call stored procedure without schema name

SQL Server – IF NOT NULL Then UPDATE Else Keep Value

Related Question