此脚本用于在删除或添加字段时刷新相关视图，并检查视图、函数、存储过程有效性。
--视图、存储过程、函数名称declare @name nvarchar(255);--局部游标declare @cur cursor --自动修改未上状态为旷课set @cur=cursor scroll dynamic for select name from dbo.sysobjects where name not in ('sysconstraints','syssegments') and ( objectproperty(id, n'isview') = 1 --视图 or objectproperty(id,n'isprocedure') = 1 --存储过程 or objectproperty(id,n'isscalarfunction') = 1 --标量函数 or objectproperty(id,n'istablefunction') = 1 --标题函数 or objectproperty(id,n'isinlinefunction') = 1 --内联函数 );open @cur;fetch next from @cur into @namewhile (@@fetch_status=0)begin declare @oldtext nvarchar(max); declare @newtext nvarchar(max); --读取创建脚本，当脚本超长时分成多条记录时合并 select @oldtext=@oldtext + char(10) + char(13) + rtrim(text) from syscomments where id = object_id(@name); --将创建脚本替换为更新脚本 set @newtext=replace(@oldtext,n'create view',n'alter view'); set @newtext=replace(@newtext,n'create procedure',n'alter procedure'); set @newtext=replace(@newtext,n'create function',n'alter function'); begin try exec(@newtext); end try begin catch print n'---------------------------------------------------------------------------'; print @name + n' : ' + error_message(); --print @oldtext; print n'---------------------------------------------------------------------------'; end catch fetch next from @cur into @nameendclose @cur;deallocate @cur;
create procedure refreshallview asdeclare mycursor cursorfor select name from dbo.sysobjects where objectproperty(id, n'isview') = 1 and (not name in ('sysconstraints','syssegments'))declare @name varchar(40)open mycursorfetch next from mycursor into @namewhile (@@fetch_status -1)begin if (@@fetch_status -2) begin exec sp_refreshview @name end fetch next from mycursor into @nameendclose mycursordeallocate mycursor
select name from sysobjects where xtype='tr' --所有触发器select name from sysobjects where xtype='p' --所有存储过程select name from sysobjects where xtype='v' --所有视图select name from sysobjects where xtype='u' --所有表
select表名=case when a.colorder=1 then d.name else '' end,表说明=case when a.colorder=1 then isnull(f.value,'') else '' end,字段序号=a.colorder,字段名=a.name,标识=case when columnproperty( a.id,a.name,'isidentity')=1 then '√'else '' end,主键=case when exists(select 1 from sysobjects where xtype='pk' and name in (select name from sysindexes where indid in(select indid from sysindexkeys where id = a.id and colid=a.colid))) then '√' else '' end,类型=b.name,占用字节数=a.length,长度=columnproperty(a.id,a.name,'precision'),小数位数=isnull(columnproperty(a.id,a.name,'scale'),0),允许空=case when a.isnullable=1 then '√'else '' end,默认值=isnull(e.text,''),字段说明=isnull(g.[value],'')from syscolumns aleft join systypes b on a.xusertype=b.xusertypeinner join sysobjects d on a.id=d.id and d.xtype='u' and d.name'dtproperties'left join syscomments e on a.cdefault=e.idleft join sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_idleft join sys.extended_properties f on d.id=f.major_id and f.minor_id=0--where d.name='course' --如果只查询指定表,加上此条件order by a.id,a.colorder
select o.name as objectsname , c.name as columnsname , t.name as columnstype , c.length as columnslengthfrom sysobjects as o , syscolumns as c , systypes as twhere o.type in ('u','v') and o.id = c.id and c.xtype = t.xtypeorder by o.name , c.name , t.name , c.length