November 12, 2013 at 8:19 am
Hii,
I am trying to write a stored procedure to check whether all the columns of different tables are filled or not please help resolve problem.
November 12, 2013 at 9:00 am
journey to ssc (11/12/2013)
Hii,I am trying to write a stored procedure to check whether all the columns of different tables are filled or not please help resolve problem.
You will need to explain you problem some more. What exactly are you looking for? Which columns have data?
November 19, 2013 at 6:24 am
Hi,
You can try the below code which will show you the data in each row and will count the non-null values for you.
--Check for the existance of the test table and drop it if it exists
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'NullColumns' AND TABLE_SCHEMA = 'dbo')
BEGIN
DROP TABLE dbo.NullColumns;
END
GO
--Create the test table
CREATE TABLE dbo.NullColumns
(
Id INTEGER,
Column1 VARCHAR(15),
Column2 CHAR(1),
Column3 FLOAT
);
GO
--Insert some test data
INSERT INTO dbo.NullColumns (Id,Column1,Column2,Column3) VALUES
(NULL,NULL,NULL,2.333),
(1,'Hello World','B',2.678),
(NULL,NULL,NULL,NULL),
(2,'Java Coffee','C',7.77);
--Declare variables
DECLARE @Columns VARCHAR(MAX);
DECLARE @Nulls VARCHAR(MAX);
DECLARE @Schema AS VARCHAR(50) = 'dbo'; --Alter this to the schema of the table you want to check for nulls
DECLARE @Table AS VARCHAR(50) = 'NullColumns'; --Alter this to the name of the table you want to check for nulls
DECLARE @sql NVARCHAR(MAX);
--Create the string that sums non null columns
SELECT @Nulls = STUFF(
(
SELECT ' IIF([' + COLUMN_NAME + '] IS NULL,0,1) +' AS [text()]
FROM
(
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Table AND TABLE_SCHEMA = @Schema) Cols
FOR XML PATH ('')
)
, 1, 1, '');
--Get the column list from the table in question
SELECT @Columns = STUFF(
(
SELECT '[' + COLUMN_NAME + '], ' AS [text()]
FROM
(
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Table AND TABLE_SCHEMA = @Schema) Cols
FOR XML PATH ('')
)
, 1, 1, '');
--Create the dynamic SQL
SET @sql = 'SELECT TOP 100 [' + @Columns + LEFT(@Nulls,LEN(@Nulls)-1) + '[NonNullCount] FROM ' + @Schema + '.' + @Table;
--Execute the dynamic SQL
EXEC sp_Executesql @sql;
Cheers,
Jim.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply