Finding unused fields
-
We have a db with 388 tables/5,950 fields. I was just asked if I could find any fields (in the entire freaking db) that have never had any data populated in them. YIKES! Is there a quick and simple way to do this?
-
mcopa (7/2/2013)
We have a db with 388 tables/5,950 fields. I was just asked if I could find any fields (in the entire freaking db) that have never had any data populated in them. YIKES! Is there a quick and simple way to do this?Not really a simple way. The best you could do is to look in every single column of every single table and see if there are any rows right now that have only NULL. That does not take into account any varchar columns with '' or datetime columns with 1/1/1900 or any other default values based on things that can happen from the front end.
The important part of my comment above is RIGHT NOW. At this particular point in time the values may meet the criteria but that does not guarantee "never had any data populated in them".
If you want to proceed with this be warned the process is incredibly resource intensive and SLOW. I can post some code that will search every column of every table if you need it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
As usual Sean's got it covered; about all i can say is null columns or tables with zero rows, is missing / no data often doesn't mean the objects can be dropped;
statistically, in a typical application, 45% of features that are added are never used. So while the features themselves are not used, the application supports the load, business use, and saving of data if those features were actually utilized. the application would fail if the dataset did not match the loads of the data tables, even thought here might be no rows.
removing items requires a thorough code review and QA testing.
http://www.softwareresults.us/2011/07/where-do-unused-features-come-from.html
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
What an awesome graphic and corresponding article Lowell. That closely mimics my gut feeling about nearly everything I have ever written. I guess that means that on average 45% of my actual productive time at work is spent on code that will be used. Kind of depressing when you think of it like that. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
That was my fear. If you already have code that will do this that would be great!!!! FYI-they don't want to delete/drop anything. They are working with a vendor for a complete re-write of the application and they have asked for this information.
-
mcopa (7/2/2013)
That was my fear. If you already have code that will do this that would be great!!!! FYI-they don't want to delete/drop anything. They are working with a vendor for a complete re-write of the application and they have asked for this information.You can use this code at your own risk. It will not do anything bad to your system other than cripple it while it is running, and it might run for a LONG LONG time if you have a lot of tables/data. You should do this on a dev server instead of production.
You should be able to tweak the select statement slightly so that it will return the MAX(FieldName) where FieldName IS NOT NULL. Let me know if you need some help tweaking it to your specific requirements and I will see what I can do.
Did I mention that you should NOT RUN THIS IN PRODUCTION??? You may have to restart the sql service when it completes because it is likely to consume every possible ounce of ram in your system.
declare @table_name varchar(2000)
declare @sSQL nvarchar(4000)
declare @result varchar(20)
declare @column_name varchar(2000)
declare @SearchVal varchar(200)
set @SearchVal = '%your search val here%'
declare @ColName varchar (250)
set @ColName = '%use this if you want to limit to a naming convention on the columns to search (i.e. email)%'
declare SearchList cursor for
select distinct so.name,sc.name from syscolumns sc
inner join sysobjects so on sc.id = so.id
where 1 = 1
and sc.name like @ColName --comment this line if not limiting column names
and so.type = 'U'
open SearchList
fetch next from SearchList into @table_name, @column_name
while(@@fetch_status = 0)
begin
select @sSQL = 'if exists (select ''' + @table_name + ''' as TableName,' + @column_name + ' from ' + @table_name + ' where ' + @column_name + ' like ''' + @SearchVal + ''') select ''' + @table_name + ''' as TableName,' + @column_name + ' from ' + @table_name + ' where ' + @column_name + ' like ''' + @SearchVal + ''''
exec sp_executesql @sSQL
--select @ssql
fetch next from SearchList into @table_name, @column_name
end
close SearchList
deallocate SearchList
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
OUCH this is painful to even acknowledge I built this query, after seeing how dog slow it is!
my thought is the command has to look something like this, for every column:
INSERT INTO #TMP([SCHEMANAME],[TABLENAME] ,[COLUMNNAME])
select TOP 1 'dbo' As SchemaName,'PPEXP' as TableName,'OPRESERVE' as ColumnName
From [PPEXP]
WHERE [OPRESERVE] IS NULL
AND NOT EXISTS(SELECT 1 FROM [PPEXP] WHERE [OPRESERVE] IS NOT NULL);
I also thought you needed to just check for tables that have no data at all, as well.
my own version is brutally slow, and i'm trying to use EXISTS and TOP 1 to speed things up;
my version was running against 18,000 or so columns in my big ol' database on my dev server...instead of a single massive command, i think i'd try this as a cursor instead, but either way, you'll fret for ten,twenty or more minutes as this monster does it's thing.
Quoting from Sean here,
"DO NOT RUN THIS IN PRODUCTION"
---- Columns in a table where every value is null.
CREATE TABLE [dbo].[#TMP] (
[SCHEMANAME] NVARCHAR(256) NULL,
[TABLENAME] SYSNAME NOT NULL,
[COLUMNNAME] SYSNAME NULL)
DECLARE @MassiveCommand varchar(max)
SET @MassiveCommand=''
select @MassiveCommand = @MassiveCommand +
'INSERT INTO #TMP([SCHEMANAME],[TABLENAME] ,[COLUMNNAME]) '
+ 'select '''
+ schema_name(tabz.schema_id) + ''' As SchemaName,'''
+ tabz.name + ''' as TableName,'''
+ colz.name + ''' as ColumnName '
+ ' WHERE EXISTS(SELECT 1 FROM ' + quotename(tabz.name) + ' WHERE ' + quotename(colz.name) + ' IS NULL) '
+ ' AND NOT EXISTS(SELECT 1 FROM ' + quotename(tabz.name) + ' WHERE ' + quotename(colz.name) + ' IS NOT NULL);'
from sys.tables tabz
inner join sys.columns colz
on tabz.object_id = colz.object_id
WHERE colz.is_nullable = 1 --only need to check columns where the value could be null in the first place!
PRINT @MassiveCommand
EXEC(@MassiveCommand)
INSERT INTO #TMP([SCHEMANAME],[TABLENAME] ,[COLUMNNAME])
select 'dbo' As SchemaName,'Sheet1$' as TableName,'1' as ColumnName
WHERE EXISTS(SELECT 1 FROM [Sheet1$] WHERE [1] IS NULL);
AND NOT EXISTS(SELECT 1 FROM [Sheet1$] WHERE [1] IS NOT NULL);
--TABLES WITH NO DATA ROWS AT ALL?
GO
DECLARE @MassiveCommand varchar(max)
SET @MassiveCommand=''
select @MassiveCommand = @MassiveCommand +
'INSERT INTO #TMP([SCHEMANAME],[TABLENAME] ,[COLUMNNAME]) '
+ 'select '''
+ schema_name(tabz.schema_id) + ''' As SchemaName,'''
+ tabz.name + ''' as TableName,'''
+ 'NO ROWS EXIST as ColumnName '
+ ' WHERE NOT EXISTS(SELECT 1 FROM '
+ quotename(tabz.name)
+ ' );'
from sys.tables tabz
inner join sys.columns colz
on tabz.object_id = colz.object_id
PRINT @MassiveCommand
EXEC(@MassiveCommand)
SELECT * FROM [#TMP]
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thanks guys - you are lifesavers! I have a dev box that does nothing so I will definitely run it on there.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply