SQL Server 便利SQL

全テーブルを出力

select s.name +'.'+t.name
from sys.tables t
join sys.schemas s on t.schema_id = s.schema_id
order by 1

指定したスキーマ内の全テーブルを出力

select s.name +'.'+t.name
from sys.tables t
join sys.schemas s on t.schema_id = s.schema_id
where s.name ='SchemaName'
order by 1

指定したテーブルの列情報を出力

select 
	 s.name+'.'+t.name AS TableName 
	,c.name            AS ColumnName 
	,d.name            AS DataType
	, c.max_length     AS Length
	,c.is_nullable     AS [Nullを許容]
from sys.tables t
join sys.schemas s on t.schema_id = s.schema_id
join sys.columns c on c.object_id = t.object_id
join sys.types   d on d.system_type_id = c.system_type_id
where t.name      = 'tablename'
order by c.column_id

制約名の一覧を取得

select 
  s.name+'.'+t.name tablename 
, c.name
, d.name
from sys.schemas             s with(nolock)
join sys.tables              t with(nolock) 
on   s.schema_id = t.schema_id
join sys.columns             c with(nolock)
on   t.object_id = c.object_id
join sys.default_constraints d with(nolock)
ON  t.object_id = d.parent_object_id
and c.column_id = d.parent_column_id

order by s.name, t.name , c.column_id

列削除のSQLを作成

SQL Serverは削除対象の列に制約があると列削除できずエラーになります。
先行して制約削除のAlter tableを実行する必要があります。SQLは以下

declare @TableMame   varchar(50)
declare @ColumnName varchar(20)
set @TableMame ='dbo.table';
set @ColumnName='column1'
select 
 'alter table '+s.name+'.'+t.name + ' drop CONSTRAINT ' +d.name as '制約削除SQL'
from sys.schemas               s
join sys.tables                t on t.schema_id = s.schema_id 
join sys.columns               c on t.object_id = c.object_id 
left outer join sys.default_constraints   d on d.parent_object_id = c.object_id and d.parent_column_id = c.column_id
where  s.name+'.'+t.name = @TableMame
and    c.name = @columnName

ColumnのPK情報を取得

select
     s.name+'.'+t.name           TableName
    ,c.name                      ColumnName
    ,case when p.key_ordinal is null then 0 else 1 end IsPrimaryKey
from sys.tables  t with(nolock)
join sys.columns c with(nolock) on   t.object_id = c.object_id
join sys.schemas s with(nolock) on   t.schema_id = s.schema_id

left outer join  (
    select
         t2.object_id  
        ,c2.column_id  
		,i.key_ordinal
    from sys.tables          t2 with(nolock)
    join sys.key_constraints d2 with(nolock)
    on   t2.object_id = d2.parent_object_id
    and  d2.type = 'PK'            
			
    join sys.index_columns i with(nolock)
    on  d2.parent_object_id = i.object_id
    and d2.unique_index_id  = i.index_id
    
	join sys.columns c2 with(nolock)
    on  i.object_id = c2.object_id
    and i.column_id = c2.column_id
) p
on  c.object_id = p.object_id
and c.column_id = p.column_id
order by 1,2,c.column_id