研究旧项目, 常用 sql 语句

1. select all table

select TABLE_NAME
from CodingSystem.INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = 'BASE TABLE'

2. select all column name from table

select COLUMN_NAME, TABLE_NAME, DATA_TYPE
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'APInvoiceDTL';

3. get column type

declare @table nvarchar(max) = 'yourTableName';
declare @column nvarchar(max) = 'yourColumnName';
select DATA_TYPE
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = @table AND COLUMN_NAME = @column;

4. select table that have some column

select t.TABLE_NAME
from INFORMATION_SCHEMA.TABLES t inner join INFORMATION_SCHEMA.COLUMNS c on t.TABLE_NAME = c.TABLE_NAME
where t.TABLE_TYPE = 'BASE table' and c.COLUMN_NAME = 'column';

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

go

use 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;
end
select * from #Result;
drop table #Result;
drop table #AllTable;

go

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

go
use 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_TYPE
into #ColumnTable
from INFORMATION_SCHEMA.COLUMNS
where 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) > 0
begin
  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;
end

select * from #Result;
drop table #ColumnTable;
drop table #Result;


go