sqlserver 如何找到主键为空值的字段

做为主键，正常情况下不应该有空值，可是最近在做ogg转化时，发现之前的sqlserver主键里居然有空值。

当然，他不是单主键，他是多主键组合。 在这里纪录一下，以便以后寻找方便。

--1.因可能多次操作，先删除之前生成的表aaa
drop table pdaux.dbo.aaa

--2.把找到的表和字段生成到另一个库里
select tablename,colname,xtype into pdaux.dbo.aaa from
(
select o.name as tablename,c.name as colname,k.colid as 字段序号,k.keyno as 索引顺序,c.xtype from sysindexes i  
join sysindexkeys k on i.id = k.id and i.indid = k.indid  
join sysobjects o on i.id = o.id  
join syscolumns c on i.id=c.id and k.colid = c.colid  
where o.xtype = 'U' 
and exists(select 1 from sysobjects where xtype = 'PK' and name = i.name)  
) as A

--3.再插入2个字段，并把字段类型写进去
alter table pdaux.dbo.aaa add typename varchar(10), nullcount int
update pdaux.dbo.aaa set typename = (select top 1 name from systypes where xtype = pdaux.dbo.aaa.xtype)

--4. 字符型的字段更新

Declare @tablename varchar(50)
Declare @colname varchar(50)
declare T_cursor01 cursor for select tablename,colname from pdaux.dbo.aaa where typename in ('varchar','nvarchar') and nullcount is null

--int,varchar,binary,datetime,numeric,tinyint,bigint,nvarchar,bit


open T_cursor01
fetch next from T_cursor01 into @tablename,@colname
while @@fetch_status=0 
begin  
Declare @sql nvarchar(500)
--Set @sql = 'Select count(0) as count from '+ @tablename +' where '+ @colname +' = '''''
Set @sql = 'update pdaux.dbo.aaa set nullcount = (select COUNT(0) from '+ @tablename +' where '+ @colname +' = '''') where tablename = '''+ @tablename +''' and colname = '''+ @colname + ''''
print @sql
execute sp_executesql @sql
fetch next from T_cursor01 into @tablename,@colname
end 
close T_cursor01 
deallocate T_cursor01

--5.非字符的字段更新（好象有一些系统表，导致出错，但会跳过，可执行多次）

Declare @tablename varchar(50)
Declare @colname varchar(50)
declare T_cursor01 cursor for select  tablename,colname from pdaux.dbo.aaa where typename not in ('varchar','nvarchar') and nullcount is null

--int,varchar,binary,datetime,numeric,tinyint,bigint,nvarchar,bit


open T_cursor01
fetch next from T_cursor01 into @tablename,@colname
while @@fetch_status=0 
begin  
Declare @sql nvarchar(500)
--Set @sql = 'Select count(0) as count from '+ @tablename +' where '+ @colname +' = '''''
Set @sql = 'update pdaux.dbo.aaa set nullcount = (select COUNT(0) from '+ @tablename +' where '+ @colname +' is null) where tablename = '''+ @tablename +''' and colname = '''+ @colname + ''''
print @sql
execute sp_executesql @sql
fetch next from T_cursor01 into @tablename,@colname
end 
close T_cursor01 
deallocate T_cursor01

--5.搜索出含有主键为空值的表名

select distinct tablename from PDAUX.dbo.aaa where nullcount > 0

--6.拼接sql

select 'update '+ tablename +' set '+ colname +' = '' '' where '+ colname +' = '''''

from PDAUX.dbo.aaa where nullcount > 0