Refresh all views, functions, and stored procedures of SQLServer

This script is used to refresh related views when deleting or adding fields, and check the validity of views, functions, and stored procedures. None -- View, stored procedure, function name DECLARE @ NAMENVARCHAR (255); -- local cursor DECLARE @ CURCURSOR -- automatically modify the unfilled status to SET @ CURCURSORSCROLLDYNAMICFORSELECTNAMEFROM

This script is used to refresh related views when deleting or adding fields, and check the validity of views, functions, and stored procedures. None -- View, stored procedure, function name declare @ name nvarchar (255 ); -- local cursor declare @ cur cursor -- automatically changes the unfilled state to absenteeism SET @ CUR = CURSOR SCROLL DYNAMIC FOR SELECT NAME FROM

This script is used to refresh related views when deleting or adding fields, and check the validity of views, functions, and stored procedures.

<无>

-- View, stored procedure, function name declare @ name nvarchar (255 ); -- local cursor declare @ cur cursor -- automatically changes the unfilled state to absenteeism SET @ CUR = cursor scroll dynamic for select name from dbo. sysobjects where name not in ('sysconstraints', 'syssegances') AND (OBJECTPROPERTY (ID, N 'isview') = 1 -- View or objectproperty (ID, n' IsProcedure ') = 1 -- stored procedure or objectproperty (ID, N 'isscalarfunction') = 1 -- scalar function or objectproperty (ID, N 'istablefunction') = 1 -- title function or objectproperty (ID, N 'isinlinefunction ') = 1 -- inline function); OPEN @ CUR; fetch next from @ cur into @ NAMEWHILE (@ FETCH_STATUS = 0) BEGINDECLARE @ OldText NVARCHAR (MAX ); DECLARE @ NewText NVARCHAR (MAX); -- read the creation script. When the script is too long, multiple records are merged. SELECT @ OldText = @ OldText + CHAR (10) + CHAR (13) + RTRIM (TEXT) from syscomments where id = OBJECT_ID (@ NAME); -- REPLACE the creation script with the update script SET @ NewText = REPLACE (@ OldText, n'create view ', N 'alter view'); SET @ NewText = REPLACE (@ NewText, N 'create processure ', N 'alter processed'); SET @ NewText = REPLACE (@ NewText, N 'create function', n'alter function'); begin tryexec (@ NewText); end trybegin catchprint n 'authorization'; PRINT @ NAME + N ': '+ ERROR_MESSAGE (); -- PRINT @ OldText; print n 'handler'; end catchfetch 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' -- all triggers select name from sysobjects where xtype = 'P' -- all stored procedures select name from sysobjects where xtype = 'V' -- all views select name from sysobjects where xtype = 'U' -- All Tables

SELECT Table name = case when. colorder = 1 then d. name else ''end, table description = case when. colorder = 1 then isnull (f. value, '') else'' end, FIELD No. =. colorder, field name =. name, id = case when COLUMNPROPERTY (. id,. name, 'isidentity ') = 1 then' √ 'else' end, primary Key = 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 =. id AND colid =. colid) then '√ 'else' 'end, type = B. name, in bytes =. length, length = COLUMNPROPERTY (. id,. name, 'precision '), decimal point = isnull (COLUMNPROPERTY (. id,. name, 'Scale'), 0), allow null = case when. isnullable = 1 then' √ 'else' end, default value = isnull (e. text, ''), field description = isnull (g. [value], '') FROM syscolumns aleft join policypes B on. xusertype = B. xusertypeinner join sysobjects d on. id = d. id and d. xtype = 'U' and d. name <> 'dtproperties' left join syscomments e on. cdefault = e. idleft join sys. extended_properties g on. id = g. major_id and. colid = g. minor_idleft join sys. extended_properties f on d. id = f. major_id and f. minor_id = 0 -- where d. name = 'Course' -- if only the specified table is queried, add this condition order by. id,. 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