There were no winners for the February Reader Challenge, "Check for an Empty Table." Here's a recap of the problem and the solution to the February Reader Challenge.
Problem:
Steve is a DBA who manages several SQL Server 2000 installations in his company. He needs to create a batch job that exports and imports several tables from the database servers. As part of the batch job, Steve would like to invoke a stored procedure that can determine whether a table is empty. According to the stored procedure result, he will then perform appropriate actions like executing DBCC SHOWCONTIG or DBCC DBREINDEX statements in his batch job.
Help Steve design a stored procedure that does the following: 1. Determines whether a table in a particular database is empty. (The tables can be present in different user schemas.) 2. Decides the appropriate parameters for the stored procedure and table name as a mandatory parameter. 3. Returns the status of the table (empty or not empty) as an output parameter.
Solution:
Steve can use a query like the one below to determine whether a table is empty.
-- Create sample table first:
USE tempdb
GO
CREATE TABLE test_tbl( i INT NOT NULL )
GO
-- Code that determines whether test_tbl is empty:
DECLARE @IsEmpty BIT
SET @IsEmpty = 1
IF EXISTS(SELECT * FROM test_tbl)
SET @IsEmpty = 0
SELECT @IsEmpty AS IsEmpty
GO
Steve can now incorporate the batch into a stored procedure and parameterize the table name by creating the SQL statements dynamically and executing them. The sample code below shows the stored procedure IsTableEmpty, which takes a table name as a parameter and returns 1 if the table is empty or 0 if it’s not. The input parameter @TableName needs to be protected against SQL injection attacks due to use of dynamic SQL. Since the parameter represents a table name or object identifier, the QUOTENAME system function can be used on the @TableName to form a properly quoted identifier. This ensures that if the @TableName parameter value contains a string that can be used to inject invalid SQL it will do no harm because the string will be treated as table name only.
USE tempdb
GO
CREATE PROCEDURE IsTableEmpty
(@TableName nvarchar(128), @IsEmpty BIT OUTPUT)
AS
BEGIN
DECLARE @SqlStr nvarchar(4000)
SET @SqlStr = N'
IF EXISTS(SELECT * FROM ' + quotename(@TableName) + N')
SET @empty = 0
'
EXEC sp_executesql @SqlStr, N'@empty BIT OUTPUT', @empty = @IsEmpty OUTPUT
SET @IsEmpty = COALESCE(@IsEmpty, 1)
END
GO
DECLARE @EMPTY BIT
EXEC IsTableEmpty 'test_tbl', @empty OUTPUT
SELECT @EMPTY
-- Add one row into test_tbl:
INSERT INTO test_tbl (i) VALUES(1)
DECLARE @empty BIT
EXEC IsTableEmpty 'test_tbl', @empty OUTPUT
SELECT @empty
GO
Steve can also modify the stored procedure to handle tables in other user schemas and any database. To pass the database name and schema name as optional parameters, Steve uses the following modified stored procedure:
USE tempdb
GO
ALTER PROCEDURE IsTableEmpty
(@Database nvarchar(128) = NULL, @SchemaName nvarchar(128) = NULL, @TableName
nvarchar(128), @IsEmpty BIT OUTPUT)
AS
BEGIN
DECLARE @SqlStr nvarchar(4000)
DECLARE @FullTableName nvarchar(500)
SET @FullTableName = COALESCE(quotename(@Database) + N'.', N')
+ COALESCE(quotename(@SchemaName) + N'.', N'.')
+ quotename(@TableName)
SET @SqlStr = N'
IF EXISTS(SELECT * FROM ' + @FullTableName + N')
SET @empty = 0
'
EXEC sp_executesql @SqlStr, N'@empty BIT OUTPUT', @empty = @IsEmpty OUTPUT
SET @IsEmpty = COALESCE(@IsEmpty, 1)
END
GO
-- Test the stored procedure:
DECLARE @empty BIT
--EXEC IsTableEmpty @TableName = 'test_tbl', @IsEmpty = @empty OUTPUT
SELECT @empty
DELETE test_tbl
-- EXEC IsTableEmpty 'test_tbl', @empty OUTPUT
Exec IsTableEmpty @TableName = 'test_tbl', @IsEmpty = @empty
GO
-- Drop the test_tbl:
DROP TABLE test_tbl
GO
/* The stored procedure call below shows how to specify a database and
schema-qualified table name. This call below will run without
errors only on a SQL Server 2005 server with the AdventureWorks sample database. */
DECLARE @empty BIT
EXEC IsTableEmpty 'AdventureWorks', 'Person', 'Address', @empty OUTPUT
SELECT @empty
GO
