Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How can we find the Null values on indexed columns from all tables in SQL Server. Expand / Collapse
Author
Message
Posted Thursday, June 6, 2013 2:16 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, November 17, 2014 11:11 AM
Points: 2,416, Visits: 90
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.
Post #1460876
Posted Thursday, June 6, 2013 2:19 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 12:10 PM
Points: 13,330, Visits: 12,829
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1460877
Posted Thursday, June 6, 2013 2:23 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, November 17, 2014 11:11 AM
Points: 2,416, Visits: 90
Hi Lange,

This a one time analysis process only.
Post #1460878
Posted Thursday, June 6, 2013 2:27 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 12:10 PM
Points: 13,330, Visits: 12,829
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1460880
Posted Thursday, June 6, 2013 2:33 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, November 17, 2014 11:11 AM
Points: 2,416, Visits: 90
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.
Post #1460882
Posted Thursday, June 6, 2013 2:55 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:35 AM
Points: 12,965, Visits: 32,525
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1460885
Posted Thursday, June 6, 2013 2:59 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 12:10 PM
Points: 13,330, Visits: 12,829
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1460887
Posted Thursday, June 6, 2013 3:06 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:35 AM
Points: 12,965, Visits: 32,525
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1460890
Posted Thursday, June 6, 2013 3:13 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 12:10 PM
Points: 13,330, Visits: 12,829
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1460894
Posted Thursday, June 6, 2013 3:31 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:35 AM
Points: 12,965, Visits: 32,525
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1460899
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse