June 6, 2013 at 2:16 pm
Hi,
Please help me on finding the Null values on indexed columns from all tables in SQL Server.
I need a SQL query to find all the indexed columns which is having null values.
Please do me needful.
June 6, 2013 at 2:20 pm
bslchennai (6/6/2013)
Hi,Please help me on finding the Null values on indexed columns from all tables in SQL Server.
I need a SQL query to find all the indexed columns which is having null values.
Please do me needful.
There is no easy way to look through all columns in all tables that have an index AND contain at least one NULL value. Is this a one time analysis process or is this something you will be running routinely?
_______________________________________________________________
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/
June 6, 2013 at 2:23 pm
Hi Lange,
This a one time analysis process only.
June 6, 2013 at 2:27 pm
bslchennai (6/6/2013)
Hi Lange,This a one time analysis process only.
OK so next question is do you care if there are actually NULL values in the table or is it good enough to know if they are allowed? The reason for this question is because it is FAR simpler to find indexed columns that allow NULL. We can simply use the system catalogs. If on the other hand you want to examine the values, we are looking at a horribly slow and inefficient cursor approach.
_______________________________________________________________
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/
June 6, 2013 at 2:33 pm
Yes, i have to take care of all null values because in our database we should not insert null values on indexed columns. and now my database size is 100GB, so we need to check whether any indexed column is having null values.
so please if you have any script please share with us.
Thank you.
June 6, 2013 at 2:55 pm
bslchennai (6/6/2013)
Yes, i have to take care of all null values because in our database we should not insert null values on indexed columns. and now my database size is 100GB, so we need to check whether any indexed column is having null values.so please if you have any script please share with us.
Thank you.
i doubt very, very much that that assumption is true;
you'll be able to confirm that once you get into the details, but a classic example of a foreign key column that has an index immediately comes to mind;
of course the detail data is nullable, but the index is in place to seed up queries.
MAYBE Clustered indexes might logically not want to allow a null, but it depends on the business requirements , and not just whether an index exists on it.
this query below returns all indexes that have a nullable column;
you can use that to build queries that actually search for existing null values.
this is just a modification from something i use to reverse engineer/script out indexes:
SELECT
sys.schemas.schema_id, sys.schemas.[name] AS schema_name,
sys.objects.[object_id], sys.objects.[name] AS object_name,
sys.indexes.index_id, ISNULL(sys.indexes.[name], '---') AS index_name,
partitions.Rows, partitions.SizeMB, IndexProperty(sys.objects.[object_id], sys.indexes.[name], 'IndexDepth') AS IndexDepth,
sys.indexes.type, sys.indexes.type_desc, sys.indexes.fill_factor,
sys.indexes.is_unique, sys.indexes.is_primary_key, sys.indexes.is_unique_constraint,
ISNULL(Index_Columns.Columns_in_index_that_Are_nullable, '---') AS Columns_in_index_that_Are_nullable,
ISNULL(Index_Columns.Include_Columns_in_index_that_Are_nullable, '---') AS Include_Columns_in_index_that_Are_nullable
FROM
sys.objects
JOIN sys.schemas ON sys.objects.schema_id=sys.schemas.schema_id
JOIN sys.indexes ON sys.objects.[object_id]=sys.indexes.[object_id]
JOIN (
SELECT
[object_id], index_id, SUM(row_count) AS Rows,
CONVERT(numeric(19,3), CONVERT(numeric(19,3), SUM(in_row_reserved_page_count+lob_reserved_page_count+row_overflow_reserved_page_count))/CONVERT(numeric(19,3), 128)) AS SizeMB
FROM sys.dm_db_partition_stats
GROUP BY [object_id], index_id
) AS partitions ON sys.indexes.[object_id]=partitions.[object_id] AND sys.indexes.index_id=partitions.index_id
CROSS APPLY (
SELECT
LEFT(index_columns_key, LEN(index_columns_key)-1) AS Columns_in_index_that_Are_nullable,
LEFT(index_columns_include, LEN(index_columns_include)-1) AS Include_Columns_in_index_that_Are_nullable
FROM
(
SELECT
(
SELECT sys.columns.[name] + ',' + ' '
FROM
sys.index_columns
JOIN sys.columns ON
sys.index_columns.column_id=sys.columns.column_id
AND sys.index_columns.[object_id]=sys.columns.[object_id]
WHERE
sys.index_columns.is_included_column=0
AND sys.indexes.[object_id]=sys.index_columns.[object_id] AND sys.indexes.index_id=sys.index_columns.index_id
and sys.columns.is_nullable=1
ORDER BY key_ordinal
FOR XML PATH('')
) AS index_columns_key,
(
SELECT sys.columns.[name] + ',' + ' '
FROM
sys.index_columns
JOIN sys.columns ON
sys.index_columns.column_id=sys.columns.column_id
AND sys.index_columns.[object_id]=sys.columns.[object_id]
WHERE
sys.index_columns.is_included_column=1
AND sys.indexes.[object_id]=sys.index_columns.[object_id] AND sys.indexes.index_id=sys.index_columns.index_id
and sys.columns.is_nullable=1
ORDER BY index_column_id
FOR XML PATH('')
) AS index_columns_include
) AS Index_Columns
) AS Index_Columns
WHERE sys.schemas.[name] ='dbo'
AND ISNULL(Index_Columns.Columns_in_index_that_Are_nullable, '---') <> '---'
Lowell
June 6, 2013 at 2:59 pm
DO NOT RUN THIS IN PRODUCTION!!!! It is slow and will consume massive amounts of resources. Get a copy to a dev server and run this there.
I used a VERY SLOW approach here because the very nature of this request is painful because we have to look in so many places. This is a slight twist on a script that I have posted many times here on SSC. My query to find indexed columns looks far simpler than Lowell's, which make me wonder if that part of this query is not going to produce the desired results.
--Need a table to hold the results
if OBJECT_ID('IndexedColumnsWithNull') is not null
drop table IndexedColumnsWithNull
create table IndexedColumnsWithNull
(
TableName varchar(255),
ColumnName varchar(255)
)
declare @table_name varchar(2000)
declare @sSQL nvarchar(4000)
declare @result varchar(20)
declare @column_name varchar(2000)
--Need to look at all the indexed columns
declare SearchList cursor for
select t.name as TableName
, c.name as ColumnName
from sys.indexes i
inner join sys.index_columns ic on ic.object_id = i.object_id and ic.index_id = i.index_id
inner join sys.columns c on c.object_id = ic.object_id and c.column_id = ic.column_id
inner join sys.tables t on t.object_id = i.object_id
where i.is_primary_key = 0 --primary key can't be null
and c.is_nullable = 1 --don't look at the column if it is not nullable
group by t.name, c.name
order by t.name, c.name
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 + '] IS NULL) INSERT IndexedColumnsWithNull select ''' + @table_name + ''' as TableName, [' + @column_name + '] from ' + @table_name + ' where [' + @column_name + '] IS NULL'
exec sp_executesql @sSQL
--select @ssql
fetch next from SearchList into @table_name, @column_name
end
close SearchList
deallocate SearchList
--Here is the results
select * from IndexedColumnsWithNull
Now this all looks well good but DO NOT RUN THIS IN PRODUCTION!!!! It is slow and will consume massive amounts of resources. Get a copy to a dev server and run this there.
_______________________________________________________________
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/
June 6, 2013 at 3:06 pm
Sean mine was just a lazy adaptation of something that already existed; it was easier than from- scratch script to find them; this adapted snippet from the middle of your post is probably boatloads easier to understand, and has pretty much the same reuslt si did (qualifying indexes only...not the actual query)
select t.name as TableName
, c.name as ColumnName,
i.name,
c.name
from sys.indexes i
inner join sys.index_columns ic on ic.object_id = i.object_id and ic.index_id = i.index_id
inner join sys.columns c on c.object_id = ic.object_id and c.column_id = ic.column_id
inner join sys.tables t on t.object_id = i.object_id
where i.is_primary_key = 0 --primary key can't be null
and c.is_nullable = 1 --don't look at the column if it is not nullable
Lowell
June 6, 2013 at 3:13 pm
I for one would be super interested in seeing how you reverse engineer your indexes. I just went through this process and I have a feeling yours will be better than mine.
_______________________________________________________________
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/
June 6, 2013 at 3:31 pm
Sean Lange (6/6/2013)
I for one would be super interested in seeing how you reverse engineer your indexes. I just went through this process and I have a feeling yours will be better than mine.
one of those things i keep hammering at now and then; and there was a lot of feedback to get this looking pretty:
take a look at this post, from a 2006! original thread where i posted how to do it SQL 2000 style, but this link is much more updated for 2008:
http://www.sqlservercentral.com/Forums/Topic401795-566-2.aspx#bm1079779
edit: no wait! that's an old cursor style post;
this is using a CTE and nice set based code:
With MyIndexBase
AS
(
SELECT
sys.schemas.schema_id, sys.schemas.[name] AS schema_name,
sys.objects.[object_id], sys.objects.[name] AS object_name,
sys.indexes.index_id, ISNULL(sys.indexes.[name], '---') AS index_name,
partitions.Rows, partitions.SizeMB, IndexProperty(sys.objects.[object_id], sys.indexes.[name], 'IndexDepth') AS IndexDepth,
sys.indexes.type, sys.indexes.type_desc, sys.indexes.fill_factor,
sys.indexes.is_unique, sys.indexes.is_primary_key, sys.indexes.is_unique_constraint,
ISNULL(Index_Columns.index_columns_key, '---') AS index_columns_key,
ISNULL(Index_Columns.index_columns_include, '---') AS index_columns_include
FROM
sys.objects
JOIN sys.schemas ON sys.objects.schema_id=sys.schemas.schema_id
JOIN sys.indexes ON sys.objects.[object_id]=sys.indexes.[object_id]
JOIN (
SELECT
[object_id], index_id, SUM(row_count) AS Rows,
CONVERT(numeric(19,3), CONVERT(numeric(19,3), SUM(in_row_reserved_page_count+lob_reserved_page_count+row_overflow_reserved_page_count))/CONVERT(numeric(19,3), 128)) AS SizeMB
FROM sys.dm_db_partition_stats
GROUP BY [object_id], index_id
) AS partitions ON sys.indexes.[object_id]=partitions.[object_id] AND sys.indexes.index_id=partitions.index_id
CROSS APPLY (
SELECT
LEFT(index_columns_key, LEN(index_columns_key)-1) AS index_columns_key,
LEFT(index_columns_include, LEN(index_columns_include)-1) AS index_columns_include
FROM
(
SELECT
(
SELECT sys.columns.[name] + ',' + ' '
FROM
sys.index_columns
JOIN sys.columns ON
sys.index_columns.column_id=sys.columns.column_id
AND sys.index_columns.[object_id]=sys.columns.[object_id]
WHERE
sys.index_columns.is_included_column=0
AND sys.indexes.[object_id]=sys.index_columns.[object_id] AND sys.indexes.index_id=sys.index_columns.index_id
ORDER BY key_ordinal
FOR XML PATH('')
) AS index_columns_key,
(
SELECT sys.columns.[name] + ',' + ' '
FROM
sys.index_columns
JOIN sys.columns ON
sys.index_columns.column_id=sys.columns.column_id
AND sys.index_columns.[object_id]=sys.columns.[object_id]
WHERE
sys.index_columns.is_included_column=1
AND sys.indexes.[object_id]=sys.index_columns.[object_id] AND sys.indexes.index_id=sys.index_columns.index_id
ORDER BY index_column_id
FOR XML PATH('')
) AS index_columns_include
) AS Index_Columns
) AS Index_Columns
)
SELECT
CASE
WHEN is_primary_key = 0 or is_unique = 0
THEN 'CREATE INDEX [' + index_name + '] '
+ SPACE(128 - LEN(index_name))
+ ' ON [' + [object_name] + ']'
+ ' (' + index_columns_key + ')'
+ CASE WHEN index_columns_include <> '---' THEN ' INCLUDE (' + index_columns_include + ')' ELSE '' END
+ CASE WHEN fill_factor <> 0 THEN ' WITH FILLFACTOR = ' + CONVERT(VARCHAR(30),fill_factor) ELSE '' END
END ,*
from MyIndexBase
where [type_desc] != 'HEAP'
AND is_primary_key = 0 AND is_unique = 0
order by is_primary_key desc,is_unique desc
Lowell
June 7, 2013 at 3:26 am
bslchennai (6/6/2013)
Yes, i have to take care of all null values because in our database we should not insert null values on indexed columns.
Why not? What's the problem with null values in an indexed column?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 7, 2013 at 7:33 am
Thanks for sharing Lowell. This is a great script.
_______________________________________________________________
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/
June 7, 2013 at 2:49 pm
GilaMonster (6/7/2013)
bslchennai (6/6/2013)
Yes, i have to take care of all null values because in our database we should not insert null values on indexed columns.Why not? What's the problem with null values in an indexed column?
Totally agree.
If tomorrow you decide to create new indexes then you won't be able to do it easily because of this ridiculous requirement.
June 16, 2013 at 9:01 am
Finally I have created one script and it is working for me.
DROP TABLE #temp
DROP TABLE #temp1
DROP TABLE #temp2
--CURSOR TO FIND ALL INDEX'S IN ALL TABLES.
DECLARE @TabName varchar(100)
CREATE TABLE #temp ([Sl_No] [int] IDENTITY(1,1) NOT NULL,TabName varchar(200), IndexName varchar(200), IndexDescr varchar(200), IndexKeys varchar(200), IndexSize int)
DECLARE cur CURSOR FAST_FORWARD LOCAL FOR
SELECT name FROM sysobjects WHERE xtype = 'U' order by name
OPEN cur
FETCH NEXT FROM cur INTO @TabName
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #temp (IndexName, IndexDescr, IndexKeys)
EXEC sp_helpindex @TabName
UPDATE #temp SET TabName = @TabName WHERE TabName IS NULL
FETCH NEXT FROM cur INTO @TabName
END
CLOSE cur
DEALLOCATE cur
DECLARE @ValueCoef int
SELECT @ValueCoef = low FROM Master.dbo.spt_values WHERE number = 1 AND type = N'E'
UPDATE #temp SET IndexSize =((CAST(sysindexes.used AS bigint) * @ValueCoef)/1024)/1024
FROM sysobjects INNER JOIN sysindexes ON sysobjects.id = sysindexes.id
INNER JOIN #temp T ON T.TabName = sysobjects.name AND T.IndexName = sysindexes.name
GO
--CURSOR TO FIND ALL INDEXED COLUMNS WHIC IS HAVING NULL VALUES.
DECLARE @TabName1varchar(200)
DECLARE @IndexNamevarchar(200)
DECLARE @IndexDescr varchar(200)
DECLARE @IndexKeysvarchar(200)
DECLARE @IndexSizeint
DECLARE @Row_CountNUMERIC(18,0)
DECLARE @sSQLNVARCHAR(MAX)
DECLARE @Sl_NoNUMERIC(18,0)
DECLARE @identityNUMERIC(18,0)
DECLARE@IntRowCount NUMERIC(18,0)
DECLARE@sql nvarchar(4000)
SET @IntRowCount = 0
CREATE TABLE #temp1 ([Sl_No] [int] IDENTITY(1,1) NOT NULL,TabName varchar(200), IndexName varchar(200),
IndexDescr varchar(200), IndexKeys varchar(200),
IndexSize int,Row_Count NUMERIC(18,0))
CREATE TABLE #temp2 ([Sl_No] [int] IDENTITY(1,1) NOT NULL,TabName varchar(200), IndexName varchar(200),
IndexDescr varchar(200), IndexKeys varchar(200),
IndexSize int,Row_Count NUMERIC(18,0))
DECLARE cur CURSOR FAST_FORWARD LOCAL FOR
SELECTTabName,IndexKeys,IndexName,IndexDescr,IndexSize,Sl_No
FROM#temp
ORDERBY Sl_No
OPEN cur
FETCH
NEXTFROM cur
INTO@TabName1,
@IndexKeys,
@IndexName,
@IndexDescr,
@IndexSize,
@Sl_No
WHILE @@FETCH_STATUS = 0
BEGIN
IF (CHARINDEX(',',@IndexKeys) > 0)
BEGIN
INSERT INTO #temp2(TabName,IndexName,IndexDescr,IndexKeys,IndexSize)
VALUES (@TabName1,@IndexName,@IndexDescr,@IndexKeys,@IndexSize)
END
ELSE
BEGIN
SELECT @sql = N'select @Row_Count = count(*) FROM ' + @TabName1 +
N' WHERE '+@IndexKeys+' is null'
EXEC sp_executesql @sql, N'@Row_Count NUMERIC(18,0) OUTPUT,@IndexKeys varchar(200)', @Row_Count OUTPUT, @IndexKeys
SET @Row_Count = ISNULL(@Row_Count,0)
IF @Row_Count > 0
BEGIN
INSERT INTO #temp1(TabName,IndexName,IndexDescr,IndexKeys,IndexSize,Row_Count)
VALUES (@TabName1,@IndexName,@IndexDescr,@IndexKeys,@IndexSize,@Row_Count)
END
END
FETCH
NEXTFROM cur
INTO@TabName1,
@IndexKeys,
@IndexName,
@IndexDescr,
@IndexSize,
@Sl_No
END
CLOSE cur
DEALLOCATE cur
SELECT Sl_No,TabName,IndexKeys,IndexName,IndexDescr,Row_Count FROM #temp1 ORDER BY TabName,Row_Count
Regards,
BSL
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply