August 3, 2017 at 2:29 am
Hi all!
I have to go through several 100 tables.
Many of them hae not used fields, where all fields are NULL
To find it it one field in one table is pretty uncomplicated, something like:
SELECT COUNT(BES_END_DATO_BESOEG)FROM [bb_kaerhuset].[dbo].[besoeg_opl] WHERE BES_END_DATO_BESOEG IS NOT NULL
If the value > 0, then there is NOT NULL values in this field.
But what i want is a general script.
i.e., I have the table below.
How many of these fields have only NULL entries.
I know, that most of [BES_END_DATO_BESOEG] is NULL, so field is not interesting. the field BES_ACCEPT_1 has only NULL entries, so this one, I want to catch.
Best regards
Edvard Korsbæk
CREATE TABLE [dbo].[besoeg_opl](
[BES_BESOEG_OPL_ID] [INT] NOT NULL,
[BES_BOERNEDATA_ID] [INT] NULL,
[BES_PERSONAL_ID] [INT] NULL,
[BES_DATO] [INT] NULL,
[BES_KLOKKEN] [INT] NULL,
[BES_DATO_BESOEG] [INT] NULL,
[BES_END_DATO_BESOEG] [INT] NULL,
[BES_KL_BESOEG] [INT] NULL,
[BES_SMS] [TINYINT] NULL,
[BES_SMS_TIL_PERSONALE] [TINYINT] NULL,
[BES_MINUTTER_FOER] [INT] NULL,
[BES_SMS_SENDT] [TINYINT] NULL,
[BES_ENDTIME] [INT] NULL,
[BES_DURATION] [INT] NULL,
[BES_AFTALT_MED] [CHAR](100) NULL,
[BES_FAKTISK] [TINYINT] NULL,
[BES_KOMMENTAR] [CHAR](2000) NULL,
[BES_TYPE_OF_VISIT] [TINYINT] NULL,
[BES_ORIGINAL_ID] [INT] NULL,
[BES_FIRST_FOR_VACANT] [INT] NULL,
[BES_ACCEPT_1] [TINYINT] NULL,
[BES_SECOND_FOR_VACANT] [INT] NULL,
[BES_ACCEPT_2] [TINYINT] NULL,
[BES_THIRD_FOR_VACANT] [INT] NULL,
[BES_ACCEPT_3] [TINYINT] NULL,
[BES_DAG_FOER_MIDNAT] [TINYINT] NULL,
[BES_DAG_EFTER_MIDNAT] [CHAR](20) NULL,
[BES_SCHEDULED_ID_CSTRING] [VARCHAR](20) NULL,
[BES_ALLDAY] [TINYINT] NULL,
[BES_SUBJECT] [VARCHAR](100) NULL,
[BES_PRIVATEFLAG] [TINYINT] NULL,
[BES_MEETINGFLAG] [TINYINT] NULL,
[BES_COLOUR] [INT] NULL,
[BES_REMINDER] [TINYINT] NULL,
[BES_REMINDERMINUTES] [INT] NULL,
[BES_RECURTYPE] [TINYINT] NULL,
[BES_RECURENDMETHOD] [TINYINT] NULL,
[BES_RECURENDOFOCCURENCES] [INT] NULL,
[BES_RECURTYPEPARAM1] [SMALLINT] NULL,
[BES_RECURTYPEPARAM2] [SMALLINT] NULL,
[BES_BUSYSTATUS] [TINYINT] NULL,
[BES_institutions_id] [INT] NULL,
[BES_flere_personer] [TINYINT] NULL,
[OUTLOOK_ID] [CHAR](255) NULL,
[UPDATE_OUTLOOK] [TINYINT] NULL,
[BES_resource_id] [INT] NULL,
[BES_afdeling_id] [INT] NULL,
CONSTRAINT [BES_VIS_BESOEGOPL_ID] PRIMARY KEY CLUSTERED
(
[BES_BESOEG_OPL_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
August 3, 2017 at 3:25 am
Hi,
This should work for you. I wrote a cursor, since I think it will not be an issue in this case when your case-scenario looks like a one-time job. The cursor loops through all tables, checks for every column if they have at least a non-null value - if not, it inserts that column and the respective table name into a temp table:
--two variables needed for table name and column name, when looping through all tables
declare @table varchar(255), @col varchar(255), @sql varchar(max)
--this will be used to store the result, to have one result set instead of one row per each cursor cycle
if object_id('tempdb..#nullcolumns') is not null drop table #nullcolumns
create table #nullcolumns (tablename varchar(255), columnname varchar(255))
declare getinfo cursor for
select t.name tablename, c.name from sys.tables t join sys.columns c on t.object_id = c.object_id
open getinfo
fetch next from getinfo into @table, @col
while @@fetch_status = 0
begin
select @sql = 'if not exists (select top 1 * from [' + @table + '] where [' + @col + '] is not null) begin insert into #nullcolumns select ''' + @table + ''' as tablename, ''' + @col + ''' as all_nulls end'
exec(@sql)
fetch next from getinfo into @table, @col
end
close getinfo
deallocate getinfo
--this should be the result you need:
select * from #nullcolumns
August 3, 2017 at 3:36 am
Although more complex, a much faster set-based Cursor that checks each table's columns all in one go, rather than each one individually (took 4 seconds to do 165 tables, instead of 13). Also caters for multiple schemas, rather than just the connected user's default Schema. DECLARE @TableName varchar(250), @SchemaName varchar(250);
CREATE TABLE #NullColumns (SchemaAndTable varchar(250),
ColumnName varchar(250),
NonNullCount int);
DECLARE tables_cursor CURSOR FOR
SELECT s.name AS SchemaName, t.name as TableName
FROM sys.tables t
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.type = 'U';
DECLARE @SQL nvarchar(max);
OPEN tables_cursor;
FETCH NEXT FROM tables_cursor
INTO @SchemaName, @TableName;
WHILE @@FETCH_STATUS = 0 BEGIN
PRINT 'Checking for NULL columns in table [' + @SchemaName + '].[' + @TableName + ']';
SELECT @SQL = 'INSERT INTO #NullColumns' + CHAR(10) +
'SELECT SchemaAndTable, ColumnName, NonNULLCount' + CHAR(10) + 'FROM' + CHAR(10) + '(' +
'SELECT ''[' + s.name + '].[' + t.name + ']'' AS SchemaAndTable, '+
STUFF((SELECT ',' + CHAR(10) + 'COUNT([' + c.name + ']) AS [' + c.name +']'
FROM sys.columns c
WHERE c.object_id = t.object_id
FOR XML PATH('')),1,1,'') + Char(10) + 'FROM [' + s.name + '].[' + t.name + ']) P' + CHAR(10) +
'UNPIVOT' + CHAR(10) +
'(NonNullCount FOR ColumnName IN (' +
STUFF((SELECT ',[' + c.name +']'
FROM sys.columns c
WHERE c.object_id = t.object_id
FOR XML PATH('')),1,1,'') +')) UP' + CHAR(10) +
'WHERE NonNULLCount = 0;'
FROM sys.tables t
JOIN sys.schemas s on t.schema_id = s.schema_id
WHERE t.name = @TableName AND s.name = @SchemaName;
EXEC(@SQL);
FETCH NEXT FROM tables_cursor
INTO @SchemaName, @TableName;
END
CLOSE tables_cursor;
DEALLOCATE tables_cursor;
SELECT *
FROM #NullColumns;
DROP TABLE #NullColumns;
This does, however, have a fall off with very wide tables.
Apologies that the alignment is awful. Blame SSC. 🙁
Edit: Does not work with text type columns.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 3, 2017 at 3:44 am
In my language ' Hold da helt kæft...'
You have saved me from weeks of work. You script found 2834 fields, which i can just forget about.
I do not have words in English to express my gratitude properly.
Thanks !
Best regards
Edvard Korsbæk
August 3, 2017 at 4:46 am
Because it was bugging me about text/ntext columns, a slight amendment:USE [YourDatabase];
GO
DECLARE @TableName varchar(250), @SchemaName varchar(250);
CREATE TABLE #NullColumns (SchemaAndTable varchar(250),
ColumnName varchar(250),
NonNullCount int);
DECLARE tables_cursor CURSOR FOR
SELECT s.name AS SchemaName, t.name as TableName
FROM sys.tables t
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.type = 'U';
DECLARE @SQL nvarchar(max);
OPEN tables_cursor;
FETCH NEXT FROM tables_cursor
INTO @SchemaName, @TableName;
WHILE @@FETCH_STATUS = 0 BEGIN
PRINT 'Checking for NULL columns in table [' + @SchemaName + '].[' + @TableName + ']';
SELECT @SQL = 'INSERT INTO #NullColumns' + CHAR(10) +
'SELECT SchemaAndTable, ColumnName, NonNULLCount' + CHAR(10) + 'FROM' + CHAR(10) + '(' +
'SELECT ''[' + s.name + '].[' + t.name + ']'' AS SchemaAndTable, '+
STUFF((SELECT ',' + CHAR(10) + 'COUNT([' + c.name + ']) AS [' + c.name +']'
FROM sys.columns c
JOIN sys.systypes st on c.system_type_id = st.xtype
WHERE c.object_id = t.object_id
AND st.name NOT IN ('ntext','text','image','sysname') --Ignore certain data types, as cannot be counted/produces dups
FOR XML PATH('')),1,1,'') + Char(10) + 'FROM [' + s.name + '].[' + t.name + ']) P' + CHAR(10) +
'UNPIVOT' + CHAR(10) +
'(NonNullCount FOR ColumnName IN (' +
STUFF((SELECT ',[' + c.name +']'
FROM sys.columns c
JOIN sys.systypes st on c.system_type_id = st.xtype
WHERE c.object_id = t.object_id
AND st.name NOT IN ('ntext','text','image','sysname')
FOR XML PATH('')),1,1,'') +')) UP' + CHAR(10) +
'WHERE NonNULLCount = 0;'
FROM sys.tables t
JOIN sys.schemas s on t.schema_id = s.schema_id
WHERE t.name = @TableName AND s.name = @SchemaName;
EXEC(@SQL);
FETCH NEXT FROM tables_cursor
INTO @SchemaName, @TableName;
END
CLOSE tables_cursor;
DEALLOCATE tables_cursor;
SELECT *
FROM #NullColumns;
DROP TABLE #NullColumns;
Again, not fixing the alignment. 🙁
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply