sql工作

select 'ALTER TABLE ',
 sys.tables.name,
 ' ALTER COLUMN ',
 sys.columns.name,
 ' varchar(',
 sys.columns.max_length,
 ') ',
 case is_nullable
  when 0 then 'not null'
  else ''
 end
from sys.columns,sys.tables
where sys.columns.object_id =  sys.tables.object_id and
 user_type_id = 175 and
 max_length <> 1
order by sys.tables.name

 

 

DECLARE @Constraint_Name varchar (200)
select @Constraint_Name = Name from dbo.sysobjects where Xtype = 'PK' and Parent_Obj = (select [ID] from dbo.sysobjects where id = object_id(N'[bank]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
EXEC ('alter table bank drop constraint '+@Constraint_Name )
EXEC ('ALTER Table bank ADD CONSTRAINT '+@Constraint_Name +' PRIMARY KEY (bankid)')

/****** Script for SelectTopNRows command from SSMS  ******/
use temp
SELECT TOP 1000 *
  FROM [temp].[sys].[objects],[temp].[sys].[objects]
  --where [temp].[sys].[tables].name='bank'

 select o.name as 表名,c.name as 字段名,k.colid as 字段序号,k.keyno as 索引顺序
            from sysindexes i
            join sysindexkeys k on i.id = k.id and i.indid = k.indid
            join sysobjects o on i.id = o.id
            join syscolumns c on i.id=c.id and k.colid = c.colid
            where o.xtype = 'U'
            and exists(select 1 from sysobjects where xtype = 'PK' and name =
            i.name) and o.name='bank'--表名称
            order by o.name,k.coli

select    'ALTER TABLE ' ,sys.tables.name,' ALTER COLUMN ', sys.columns.name ,'varchar(' ,sys.columns.max_length , ')'
 from sys.columns,sys.tables   where
sys.columns.object_id =  sys.tables.object_id and
user_type_id = 175
 and max_length <> 1
order by  sys.tables.name