在 T-SQL 中,是否可以在给定行中找到包含 NULL 的列名(不知道所有列名)? [英] in T-SQL, is it possible to find names of columns containing NULL in a given row (without knowing all column names)?
问题描述
是否可以在 T-SQL 中编写反映此伪代码的正确查询:
Is it possible in T-SQL to write a proper query reflecting this pseudo-code:
SELECT {primary_key}, {column_name}
FROM {table}
WHERE {any column_name value} is NULL
即无需明确引用每个列名.
i.e. without referencing each column-name explicitly.
听起来很简单,但我进行了广泛的搜索,但一无所获.
Sounds simple enough but I've searched pretty extensively and found nothing.
推荐答案
你必须使用动态 sql 来解决这个问题.我已经演示了如何做到这一点.使用此 sql,您可以选择一个表并检查 id = 1 的行是否为空和主键.我在脚本底部包含了一个测试表.如果没有主键且没有为空的列,代码将不会显示任何内容.
You have to use dynamic sql to solve that problem. I have demonstrated how it could be done. With this sql you can pick a table and check the row with id = 1 for columns being null and primary keys. I included a test table at the bottom of the script. Code will not display anything if there is not primary keys and no columns being null.
DECLARE @table_name VARCHAR(20)
DECLARE @chosencolumn VARCHAR(20)
DECLARE @sqlstring VARCHAR(MAX)
DECLARE @sqlstring2 varchar(100)
DECLARE @text VARCHAR(8000)
DECLARE @t TABLE (col1 VARCHAR(30), dummy INT)
SET @table_name = 'test_table' -- replace with your tablename if you want
SET @chosencolumn = 'ID=1' -- replace with criteria for selected row
SELECT @sqlstring = COALESCE(@sqlstring, '') + 'UNION ALL SELECT '',''''NULL '''' '' + '''+t1.column_name+''', 1000 ordinal_position FROM ['+@table_name+'] WHERE [' +t1.column_name+ '] is null and ' +@chosencolumn+ ' '
FROM INFORMATION_SCHEMA.COLUMNS t1
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE t2
ON t1.column_name = t2.column_name
AND t1.table_name = t2.table_name
AND t1.table_schema = t2.table_schema
WHERE t1.table_name = @table_name
AND t2.column_name is null
SET @sqlstring = stuff('UNION ALL SELECT '',''''PRIMARY KEY'''' ''+ column_name + '' '' col1, ordinal_position
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE table_name = ''' + @table_name+ '''' + @sqlstring, 1, 10, '') + 'order by 2'
INSERT @t
EXEC( @sqlstring)
SELECT @text = COALESCE(@text, '') + col1
FROM @t
SET @sqlstring2 ='select '+stuff(@text,1,1,'')
EXEC( @sqlstring2)
结果:
id host_id date col1
PRIMARY KEY PRIMARY KEY PRIMARY KEY NULL
测试表
CREATE TABLE [dbo].[test_table](
[id] int not null,
[host_id] [int] NOT NULL,
[date] [datetime] NOT NULL,
[col1] [varchar](20) NULL,
[col2] [varchar](20) NULL,
CONSTRAINT [PK_test_table] PRIMARY KEY CLUSTERED
(
[id] ASC,
[host_id] ASC,
[date] ASC
))
测试数据
INSERT test_table VALUES (1, 1, getdate(), null, 'somevalue')
这篇关于在 T-SQL 中,是否可以在给定行中找到包含 NULL 的列名(不知道所有列名)?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!