August 24, 2006 at 1:44 am
SET NOCOUNT ON
DECLARE @TableName VARCHAR(200),
@TotalRows INT,
@SQL VARCHAR(1000),
@ColIndex INT,
@ColName VARCHAR(200)
SELECT @TableName = 'vwToday'
CREATE TABLE #Columns
(
ID INT IDENTITY(0, 1),
ColName VARCHAR(200),
TotalRows INT,
NoDataRows INT
)
INSERT #Columns
(
ColName
)
SELECT column_name
FROM information_schema.columns
WHERE table_name = @TableName
SELECT @SQL = 'UPDATE #Columns SET TotalRows = (SELECT COUNT(*) FROM ' + QUOTENAME(@TableName) + ')'
EXEC ( @sql )
SELECT @ColIndex = MAX(ID)
FROM #Columns
WHILE @ColIndex >= 0
BEGIN
SELECT @ColName = ColName
FROM #Columns
WHERE ID = @ColIndex
SELECT @SQL = 'UPDATE #Columns SET NoDataRows = (SELECT COUNT(*) FROM ' + QUOTENAME(@TableName) + ' WHERE LEN(ISNULL(' + QUOTENAME(@ColName) + ', '''')) = 0) WHERE ID = ' + CONVERT(VARCHAR, @ColIndex)
EXEC ( @sql )
SELECT @ColIndex = @ColIndex - 1
END
SELECT *
FROM #Columns
DROP TABLE #Columns
N 56°04'39.16"
E 12°55'05.25"
August 24, 2006 at 6:25 pm
Thank you, Peter.
When I try to run the script it is telling me that 'vwToday' is an invalid object name. Is there something else I need to do? I am sorry but I am not that familiar with SQL.
August 24, 2006 at 10:57 pm
Of course you have to replace my table vwExchange with the name of the table on your system you want to investigate...
N 56°04'39.16"
E 12°55'05.25"
Viewing 3 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply