1. select all table

select TABLE_NAMEfrom CodingSystem.INFORMATION_SCHEMA.TABLESwhere TABLE_TYPE = ‘BASE TABLE‘

2. select all column name from table

select COLUMN_NAME, TABLE_NAME, DATA_TYPEfrom INFORMATION_SCHEMA.COLUMNSwhere TABLE_NAME = ‘APInvoiceDTL‘;

3. get column type

declare @table nvarchar(max) = ‘yourTableName‘;declare @column nvarchar(max) = ‘yourColumnName‘;select DATA_TYPEfrom INFORMATION_SCHEMA.COLUMNSwhere TABLE_NAME = @table AND COLUMN_NAME = @column;

4. select table that have some column

select t.TABLE_NAMEfrom INFORMATION_SCHEMA.TABLES t inner join INFORMATION_SCHEMA.COLUMNS c on t.TABLE_NAME = c.TABLE_NAMEwhere t.TABLE_TYPE = ‘BASE table‘ and c.COLUMN_NAME = ‘column‘;

5. 寻找一个 值 在任何 table column 出现过

gouse CodingSystem;declare @value nvarchar(max) = ‘Discount 5 %‘; declare @dataType nvarchar(max) = ‘nvarchar‘;CREATE TABLE #Result( tableName nvarchar(max), columnName nvarchar(max))select * into #AllTable from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = ‘BASE TABLE‘ order by TABLE_NAME;declare @TABLE_NAME nvarchar(max);declare @COLUMN_NAME nvarchar(max);declare @query nvarchar(max);declare @count int;while((select count(*) from #AllTable) > 0)begin select top 1 @TABLE_NAME = TABLE_NAME from #AllTable; select * into #AllColumn from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TABLE_NAME and DATA_TYPE = @dataType; set @COLUMN_NAME = ‘‘; while((select count(*) from #AllColumn) > 0) begin select top 1 @COLUMN_NAME = COLUMN_NAME from #AllColumn; set @query = N‘select @count = count(*) from ‘ + @TABLE_NAME + ‘ where ‘ + @COLUMN_NAME + ‘ = @value‘; exec sp_executesql @query, N‘@count int out, @value nvarchar(max)‘, @value = @value, @count = @count output; if(@count > 0) begin insert into #Result (tableName, columnName) values (@TABLE_NAME, @COLUMN_NAME); end delete #AllColumn where COLUMN_NAME = @COLUMN_NAME; end delete #AllTable where TABLE_NAME = @TABLE_NAME; drop table #AllColumn;endselect * from #Result;drop table #Result;drop table #AllTable;go

6. 查看一个 table 的 column 有没有用到, (全部 row null 就是没有用啦)

gouse CodingSystem;declare @tableName nvarchar(max) = ‘Item‘;-- 如果要 where 的话, 可以创建一个表, 用完后再删除--select * into Stooges_Item from Item where stooges_status = ‘keep‘;--drop table Stooges_Item;select COLUMN_NAME, DATA_TYPEinto #ColumnTablefrom INFORMATION_SCHEMA.COLUMNSwhere TABLE_NAME = @tableName;CREATE TABLE #Result( columnName nvarchar(max), fill int, noFill nvarchar(max))declare @COLUMN_NAME nvarchar(max);declare @DATA_TYPE nvarchar(max);declare @count int;declare @query nvarchar(max);declare @maxCount int;set @query = N‘select @maxCount = count(*) from ‘ + @tableName;exec sp_executesql @query, N‘@maxCount int out‘, @maxCount = @maxCount output;while (select count(*) From #ColumnTable) > 0begin select top 1 @COLUMN_NAME = COLUMN_NAME, @DATA_TYPE = DATA_TYPE from #ColumnTable; if(@DATA_TYPE = ‘nvarchar‘) begin set @query = N‘select @count = count(*) from ‘ + @tableName + ‘ where ‘+ @COLUMN_NAME +‘ is not null and ‘+ @COLUMN_NAME +‘ != @value‘; exec sp_executesql @query, N‘@count int out, @value nvarchar(max)‘, @value = ‘‘, @count = @count output; end else begin set @query = N‘select @count = count(*) from ‘ + @tableName + ‘ where ‘+ @COLUMN_NAME +‘ is not null‘; exec sp_executesql @query, N‘@count int out‘, @count = @count output; end print(@COLUMN_NAME); insert into #Result (columnName, fill, noFill) values (@COLUMN_NAME, @count, case when @maxCount - @count = 0 then ‘‘ else cast((@count - @maxCount) * -1 as nvarchar(max)) end); delete #ColumnTable where COLUMN_NAME = @COLUMN_NAME;endselect * from #Result;drop table #ColumnTable;drop table #Result;go