查询在PHP中查找MS SQL Server中未使用的列

Question

I'm working on a bad large database with a lot of unused fields and columns where every data is NULL. Is there any way to retrieve only the populated columns of a table with a query? I have no access to SQL Server Studio but via PHP

Answer 1

Take a peek at Select * From INFORMATION_SCHEMA.COLUMNS

From that, you can create some dynamic SQL to list table names and field names where all values are null

Declare @SQL varchar(max) = '>>>'
Select @SQL=@SQL+'Union All Select TableName=''['+Table_Schema+'].['+Table_Name+']'',FieldName='''+Column_Name+''' From ['+Table_Schema+'].['+Table_Name+'] having max(['+Column_Name+']) is null'+char(13)
  From INFORMATION_SCHEMA.COLUMNS

Set @SQL = Replace(@SQL,'>>>Union All','')
Exec(@SQL)

I should add @scsimon has valid concerns/questions

I didn't have a table where an entire column was null, so I created dbo.temp with 3 records and 3 fields. Only field aaa was completely null.

The above dynamic sql returned

TableName       FieldName
[dbo].[temp]    aaa

One Line

Declare @SQL varchar(max) = '>>>';Select @SQL=@SQL+'Union All Select TableName=''['+Table_Schema+'].['+Table_Name+']'',FieldName='''+Column_Name+''' From ['+Table_Schema+'].['+Table_Name+'] having max(['+Column_Name+']) is null'+char(13) From INFORMATION_SCHEMA.COLUMNS;Set @SQL = Replace(@SQL,'>>>Union All','');Exec(@SQL);