+ժ*(11 f3f3̙f3fffff3ff333f3333f3f3f3̙f3fffff3ff333f3333f3f3̙̙̙f̙3̙̙̙f3fffff3ff333f3333f3fffff3fffffff3fff̙ffff3fffffffffff3ffff3f3f3ff3f33f3ffffff3ff333f3333333f33333̙33f3333f3f3f3ff33f3f3333333f3333333333f3333f3f3̙f3fffff3ff333f3333f33f̙3ffffffffff!___www                **U  ****U  **U***U  UU*****U+V  U**U***U+VVV UU****+VVVV   U**U***U+VVVV  UU****+VVVV  U**U***U+VVVV**U UU*****U+VVVV***U U**U*+VVVV***U UU*****+VVVV***U U****+VVVV***U ****V+VVVV** VVV+VV***U VVV+VVVV** VVVV+VV***U  VVV+VVVV***U  VVVV+VVVV*  VVV+++++V****  V++VVV+++***  ++VVVVVVV*  ++VVVVVVVV  ++VVVVVVVV  VVVVVVV              (9Lf3f3̙f3fffff3ff333f3333f3f3f3̙f3fffff3ff333f3333f3f3̙̙̙f̙3̙̙̙f3fffff3ff333f3333f3fffff3fffffff3fff̙ffff3fffffffffff3ffff3f3f3ff3f33f3ffffff3ff333f3333333f33333̙33f3333f3f3f3ff33f3f3333333f3333333333f3333f3f3̙f3fffff3ff333f3333f3+ժՀUU+ի*ժՀՀՀՀUUUUUՀՀՀՀժիի*+UՀժժ+UVժ+VժUՀ+Հժ+Հի+Հ*Uժ+Հ*ՀUիUժ+ from sysindexes where indid in (0, 1, 255)) - data /* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */ update #spt_space set unused = reserved - (select sum(convert(dec(15),used)) from sysindexes where indid in (0, 1, 255)) select reserved = ltrim(str(reserved * d.low / 1024.,15,0) + ' ' + 'KB'), data = ltrim(str(data * d.low / 1024.,15,0) + ' ' + 'KB'), index_size = ltrim(str(indexp * d.low / 1024.,15,0) + ' ' + 'KB'), unused = ltrim(str(unused * d.low / 1024.,15,0) + ' ' + 'KB') from #spt_space, master.dbo.spt_values d where d.number = 1 and d.type = 'E' end /* ** We want a particular object. */ else begin /* ** Now calculate the summary data. ** reserved: sum(reserved) where indid in (0, 1, 255) */ insert into #spt_space (reserved) select sum(reserved) from sysindexes where indid in (0, 1, 255) and id = @id /* ** data: sum(dpages) where indid < 2 ** + sum(used) where indid = 255 (text) */ select @pages = sum(dpages) from sysindexes where indid < 2 and id = @id select @pages = @pages + isnull(sum(used), 0) from sysindexes where indid = 255 and id = @id update #spt_space set data = @pages /* index: sum(used) where indid in (0, 1, 255) - data */ update #spt_space set indexp = (select sum(used) from sysindexes where indid in (0, 1, 255) and id = @id) - data /* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */ update #spt_space set unused = reserved - (select sum(used) from sysindexes where indid in (0, 1, 255) and id = @id) update #spt_space set rows = i.rows from sysindexes i where i.indid < 2 and i.id = @id select name = object_name(@id), rows = convert(char(11), rows), reserved = ltrim(str(reserved * d.low / 1024.,15,0) + ' ' + 'KB'), data = ltrim(str(data * d.low / 1024.,15,0) + ' ' + 'KB'), index_size = ltrim(str(indexp * d.low / 1024.,15,0) + ' ' + 'KB'), unused = ltrim(str(unused * d.low / 1024.,15,0) + ' ' + 'KB') from #spt_space, master.dbo.spt_values d where d.number = 1 and d.type = 'E' end return (0) -- sp_spaceused go checkpoint go raiserror(15339,-1,-1,'sp_sqlexec') -- sp_sqlexec should not be allowed to update system catalog exec sp_configure 'allow updates',0 reconfigure with override go create procedure sp_sqlexec --- 1996/04/08 00:00 @p1 text as exec(@p1) go exec sp_configure 'allow updates',1 reconfigure with override go raiserror(15339,-1,-1,'sp_unbindefault') go create procedure sp_unbindefault --- 1996/08/13 13:34 @objname nvarchar(776), /* table/column or datatype name */ @futureonly varchar(15) = NULL /* flag to indicate extent of binding */ as declare @futurevalue varchar(15) /* the value of @futureonly that causes ** the binding to be limited */ declare @vc1 nvarchar(517) declare @tab_id integer ,@cur_tab_id integer ,@colid integer ,@cdefault integer ,@olddefault integer ,@xusertype smallint ,@UnqualObj sysname ,@QualObj1 sysname ,@QualObj2 sysname ,@QualObj3 sysname set cursor_close_on_commit off select @futurevalue = 'futureonly' /* initialize @futurevalue */ /* ** When a default or rule is bound to a user-defined datatype, it is also ** bound, by default, to any columns of the user datatype that are currently ** using the existing default or rule as their default or rule. This default ** action may be overridden by setting @futureonly = @futurevalue when the ** procedure is invoked. In this case existing columns with the user ** datatype won't have their existing default or rule changed. */ -- get name parts -- select @UnqualObj = parsename(@objname, 1), @QualObj1 = parsename(@objname, 2), @QualObj2 = parsename(@objname, 3), @QualObj3 = parsename(@objname, 4) IF (@UnqualObj is NULL OR @QualObj3 is not null) begin raiserror(15253,-1,-1,@objname) return (1) end ------------------ Verify database. if (@QualObj2 is not null and @QualObj1 is null) begin raiserror(15084,-1,-1) return (1) end /* ** If @objname is of the form tab.col then we are unbinding a column. ** Otherwise its a datatype. In the column case, we need to extract ** and verify the table and column names and make sure the user owns ** the table that is getting the default unbound. */ if @QualObj1 is not null begin if (@QualObj2 is not null) select @vc1 = QuoteName(@QualObj2) + '.' + QuoteName(@QualObj1) else select @vc1 = QuoteName(@QualObj1) /* ** Find it and unbind it. */ select @tab_id = c.id, @colid = c.colid, @cdefault = c.cdefault from syscolumns c, sysobjects o where c.id = o.id and c.name = @UnqualObj and o.id = object_id(@vc1,'local') and o.xtype = N'U ' if @tab_id is null begin raiserror(15104,-1,-1,@QualObj1,@UnqualObj) return(1) end if @cdefault = 0 begin raiserror(15236,-1,-1,@objname) return(1) end if exists (select * from sysobjects o where @cdefault = o.id and @tab_id = o.parent_obj) begin raiserror(15049,-1,-1, @objname) return (1) end BEGIN TRANSACTION txn_unbindefault_1 /* ** Since binding a default is a schema change, ** update schema count ** for the object in the sysobjects table. */ dbcc LockObjectSchema(@vc1) --- Undocu. Locks out other schema changes until commit, and increments sysobjects.schema_ver. update syscolumns set cdefault = 0 from syscolumns where id = @tab_id and colid = @colid COMMIT TRANSACTION txn_bindefault_1 raiserror(15519,-1,-1) return (0) end else begin /* ** We're unbinding to a user type. In this case, the @objname ** is really the name of the user datatype. ** When we unbind to a user type, any existing columns get changed ** to the new binding unless their current binding is not equal ** to the current binding for the usertype or if they set the ** @futureonly parameter to @futurevalue. */ /* ** Get the current default for the datatype. */ select @olddefault = tdefault, @xusertype = xusertype from systypes where name = @UnqualObj and xusertype > 256 AND (is_member('db_owner') = 1 OR is_member('db_ddladmin') = 1 OR is_member(user_name(uid))=1) if @olddefault is null begin raiserror(15036,-1,-1,@UnqualObj) return (1) end if @olddefault = 0 begin raiserror(15237,-1,-1,@UnqualObj) return (1) end update systypes set tdefault = 0 from systypes where xusertype = @xusertype raiserror(15520,-1,-1) /* ** Now see if there are any columns with the usertype that ** need the new binding. */ select @futureonly = lower(@futureonly) if isnull(@futureonly, ' ') <> @futurevalue begin declare ms_crs_unbindefault_1 cursor local static for select distinct c.id ,c.colid from syscolumns c JOIN sysobjects o ON c.id = o.id AND o.xtype = N'U ' where c.xusertype = @xusertype and c.cdefault = @olddefault order by c.id for read only open ms_crs_unbindefault_1 fetch next from ms_crs_unbindefault_1 into @tab_id ,@colid BEGIN TRANSACTION txn_unbindefault_2 while @@fetch_status = 0 begin select @vc1 = quotename(user_name(OBJECTPROPERTY(@tab_id,'OwnerId'))) + '.' + quotename(object_name(@tab_id)) dbcc LockObjectSchema(@vc1) --- Undocu. Locks out other schema changes until commit, and increments sysobjects.schema_ver. select @cur_tab_id = @tab_id while @cur_tab_id = @tab_id and @@fetch_status = 0 begin update syscolumns set cdefault = 0 from syscolumns where id = @tab_id and colid = @colid fetch next from ms_crs_unbindefault_1 into @tab_id ,@colid end end --loop 3 /* COMMIT TRANSACTION txn_unbindefault_2 deallocate ms_crs_unbindefault_1 raiserror(15521,-1,-1) end end return (0) -- sp_unbindefault go raiserror(15339,-1,-1,'sp_unbindrule') go create procedure sp_unbindrule --- 1996/08/13 13:33 @objname nvarchar(776), /* table/column or datatype name */ @futureonly varchar(15) = NULL /* flag to indicate extent of binding */ as declare @oldrule int /* current rule for type */ declare @tabname sysname /* name of table */ declare @colname sysname /* name of column */ declare @futurevalue varchar(15) /* the value of @futureonly that causes ** the binding to be limited */ declare @vc1 nvarchar(517) declare @obj_id integer ,@cur_tab_id integer ,@colid integer ,@domain integer ,@xusertype smallint ,@owner_name sysname ,@obj_name sysname ,@UnqualObj sysname ,@QualObj1 sysname ,@QualObj2 sysname ,@QualObj3 sysname set cursor_close_on_commit off select @futurevalue = 'futureonly' /* initialize @futurevalue */ /* ** When a default or rule is bound to a user-defined datatype, it is also ** bound, by default, to any columns of the user datatype that are currently ** using the existing default or rule as their default or rule. This default ** action may be overridden by setting @futureonly = @futurevalue when the ** procedure is invoked. In this case existing columns with the user ** datatype won't have their existing default or rule changed. */ -- get name parts -- select @UnqualObj = parsename(@objname, 1), @QualObj1 = parsename(@objname, 2), @QualObj2 = parsename(@objname, 3), @QualObj3 = parsename(@objname, 4) IF (@UnqualObj is NULL OR @QualObj3 is not null) begin raiserror(15253,-1,-1,@objname) return (1) end ------------------ Verify database. if (@QualObj2 is not null and @QualObj1 is null) begin raiserror(15084,-1,-1) return (1) end /* ** If @objname is of the form tab.col then we are unbinding a column. ** Otherwise its a datatype. In the column case, we need to extract ** and verify the table and column names and make sure the user owns ** the table that is getting the default unbound. */ if @QualObj1 is not null begin if (@QualObj2 is not null) select @vc1 = QuoteName(@QualObj2) + '.' + QuoteName(@QualObj1) else select @vc1 = QuoteName(@QualObj1) select @obj_id = c.id, @colid = c.colid, @domain = c.domain from syscolumns c, sysobjects o where c.id = o.id and c.name = @UnqualObj and o.id = object_id(@vc1,'local') and o.xtype = N'U ' if @obj_id is null begin raiserror(15104,-1,-1,@QualObj1,@UnqualObj) return (1) end if @domain = 0 begin raiserror(15238,-1,-1,@objname) return (1) end BEGIN TRANSACTION txn_unbindrule_1 /* ** Update schema count ** for the object in the sysobjects table. */ dbcc LockObjectSchema(@vc1) -- Locks Object and increments schema_ver. update syscolumns set domain = 0 from syscolumns c where id = @obj_id and colid = @colid COMMIT TRANSACTION txn_unbindrule_1 raiserror(15522,-1,-1) end else begin select @oldrule = domain, @xusertype = xusertype from systypes where name = @UnqualObj and xusertype > 256 AND (is_member('db_owner') = 1 OR is_member('db_ddladmin') = 1 OR is_member(user_name(uid))=1) if @xusertype is null begin raiserror(15036,-1,-1,@UnqualObj) return (1) end if @oldrule = 0 begin raiserror(15239,-1,-1,@UnqualObj) return (1) end update systypes set domain = 0 from systypes where xusertype = @xusertype raiserror(15523,-1,-1) select @futureonly = lower(@futureonly) if isnull(@futureonly, ' ') <> @futurevalue begin declare ms_crs_unbindrule_1 cursor local static for select distinct o.id ,user_name(o.uid) ,o.name ,c.colid from syscolumns c ,sysobjects o where o.id = c.id and o.xtype = N'U ' and c.xusertype = @xusertype and c.domain = @oldrule order by o.id for read only open ms_crs_unbindrule_1 BEGIN TRANSACTION txn_unbindrule_2 fetch next from ms_crs_unbindrule_1 into @obj_id ,@owner_name ,@obj_name ,@colid while @@fetch_status = 0 begin select @vc1 = quotename(@owner_name) + '.' + quotename(@obj_name) dbcc LockObjectSchema(@vc1) --- Undocu. Locks out other schema changes until commit, and increments sysobjects.schema_ver. select @cur_tab_id = @obj_id while @cur_tab_id = @obj_id and @@fetch_status = 0 begin update syscolumns set domain = 0 from syscolumns where id = @obj_id and colid = @colid fetch next from ms_crs_unbindrule_1 into @obj_id ,@owner_name ,@obj_name ,@colid end end COMMIT TRANSACTION txn_unbindrule_2 deallocate ms_crs_unbindrule_1 raiserror(15524,-1,-1) end end return (0) --sp_unbindrule go checkpoint go -- raiserror(15339,-1,-1,'sp_who') go create procedure sp_who --- 1995/11/28 15:48 @loginame sysname = NULL --or 'active' as declare @spidlow int, @spidhigh int, @spid int, @sid varbinary(85) select @spidlow = 0 ,@spidhigh = 32767 if ( @loginame is not NULL AND upper(@loginame) = 'ACTIVE' ) begin select spid , ecid, status ,loginame=rtrim(loginame) ,hostname ,blk=convert(char(5),blocked) ,dbname = case when dbid = 0 then null when dbid <> 0 then db_name(dbid) end ,cmd from master.dbo.sysprocesses where spid >= @spidlow and spid <= @spidhigh AND upper(cmd) <> 'AWAITING COMMAND' return (0) end if (@loginame is not NULL AND upper(@loginame) <> 'ACTIVE' ) begin if (@loginame like '[0-9]%') -- is a spid. begin select @spid = convert(int, @loginame) select spid, ecid, status, loginame=rtrim(loginame), hostname,blk = convert(char(5),blocked), dbname = case when dbid = 0 then null when dbid <> 0 then db_name(dbid) end ,cmd from master.dbo.sysprocesses where spid = @spid end else begin select @sid = suser_sid(@loginame) if (@sid is null) begin raiserror(15007,-1,-1,@loginame) return (1) end select spid, ecid, status, loginame=rtrim(loginame), hostname ,blk=convert(char(5),blocked), dbname = case when dbid = 0 then null when dbid <> 0 then db_name(dbid) end ,cmd from master.dbo.sysprocesses where sid = @sid end return (0) end /* loginame arg is null */ select spid, ecid, status, loginame=rtrim(loginame), hostname, blk=convert(char(5),blocked), dbname = case when dbid = 0 then null when dbid <> 0 then db_name(dbid) end ,cmd from master.dbo.sysprocesses where spid >= @spidlow and spid <= @spidhigh return (0) -- sp_who go raiserror(15339,-1,-1,'sp_who2') go CREATE PROCEDURE sp_who2 --- 1995/11/03 10:16 @loginame sysname = NULL as set nocount on declare @retcode int declare @sidlow varbinary(85) ,@sidhigh varbinary(85) ,@sid1 varbinary(85) ,@spidlow int ,@spidhigh int declare @charMaxLenLoginName varchar(6) ,@charMaxLenDBName varchar(6) ,@charMaxLenCPUTime varchar(10) ,@charMaxLenDiskIO varchar(10) ,@charMaxLenHostName varchar(10) ,@charMaxLenProgramName varchar(10) ,@charMaxLenLastBatch varchar(10) ,@charMaxLenCommand varchar(10) declare @charsidlow varchar(85) ,@charsidhigh varchar(85) ,@charspidlow varchar(11) ,@charspidhigh varchar(11) -------- select @retcode = 0 -- 0=good ,1=bad. --------defaults select @sidlow = convert(varbinary(85), (replicate(char(0), 85))) select @sidhigh = convert(varbinary(85), (replicate(char(1), 85))) select @spidlow = 0 ,@spidhigh = 32767 -------------------------------------------------------------- IF (@loginame IS NULL) --Simple default to all LoginNames. GOTO LABEL_17PARM1EDITED -------- -- select @sid1 = suser_sid(@loginame) select @sid1 = null if exists(select * from master.dbo.syslogins where loginname = @loginame) select @sid1 = sid from master.dbo.syslogins where loginname = @loginame IF (@sid1 IS NOT NULL) --Parm is a recognized login name. begin select @sidlow = suser_sid(@loginame) ,@sidhigh = suser_sid(@loginame) GOTO LABEL_17PARM1EDITED end -------- IF (lower(@loginame) IN ('active')) --Special action, not sleeping. begin select @loginame = lower(@loginame) GOTO LABEL_17PARM1EDITED end -------- IF (patindex ('%[^0-9]%' , isnull(@loginame,'z')) = 0) --Is a number. begin select @spidlow = convert(int, @loginame) ,@spidhigh = convert(int, @loginame) GOTO LABEL_17PARM1EDITED end -------- RaisError(15007,-1,-1,@loginame) select @retcode = 1 GOTO LABEL_86RETURN LABEL_17PARM1EDITED: -------------------- Capture consistent sysprocesses. ------------------- SELECT spid ,status ,sid ,hostname ,program_name ,cmd ,cpu ,physical_io ,blocked ,dbid ,convert(sysname, rtrim(loginame)) as loginname ,spid as 'spid_sort' , substring( convert(varchar,last_batch,111) ,6 ,5 ) + ' ' + substring( convert(varchar,last_batch,113) ,13 ,8 ) as 'last_batch_char' INTO #tb1_sysprocesses from master.dbo.sysprocesses (nolock) --------Screen out any rows? IF (@loginame IN ('active')) DELETE #tb1_sysprocesses where lower(status) = 'sleeping' and upper(cmd) IN ( 'AWAITING COMMAND' ,'MIRROR HANDLER' ,'LAZY WRITER' ,'CHECKPOINT SLEEP' ,'RA MANAGER' ) and blocked = 0 --------Prepare to dynamically optimize column widths. Select @charsidlow = convert(varchar(85),@sidlow) ,@charsidhigh = convert(varchar(85),@sidhigh) ,@charspidlow = convert(varchar,@spidlow) ,@charspidhigh = convert(varchar,@spidhigh) SELECT @charMaxLenLoginName = convert( varchar ,isnull( max( datalength(loginname)) ,5) ) ,@charMaxLenDBName = convert( varchar ,isnull( max( datalength( rtrim(convert(varchar(128),db_name(dbid))))) ,6) ) ,@charMaxLenCPUTime = convert( varchar ,isnull( max( datalength( rtrim(convert(varchar(128),cpu)))) ,7) ) ,@charMaxLenDiskIO = convert( varchar ,isnull( max( datalength( rtrim(convert(varchar(128),physical_io)))) ,6) ) ,@charMaxLenCommand = convert( varchar ,isnull( max( datalength( rtrim(convert(varchar(128),cmd)))) ,7) ) ,@charMaxLenHostName = convert( varchar ,isnull( max( datalength( rtrim(convert(varchar(128),hostname)))) ,8) ) ,@charMaxLenProgramName = convert( varchar ,isnull( max( datalength( rtrim(convert(varchar(128),program_name)))) ,11) ) ,@charMaxLenLastBatch = convert( varchar ,isnull( max( datalength( rtrim(convert(varchar(128),last_batch_char)))) ,9) ) from #tb1_sysprocesses where -- sid >= @sidlow -- and sid <= @sidhigh -- and spid >= @spidlow and spid <= @spidhigh --------Output the report. EXECUTE( ' SET nocount off SELECT SPID = convert(char(5),spid) ,Status = CASE lower(status) When ''sleeping'' Then lower(status) Else upper(status) END ,Login = substring(loginname,1,' + @charMaxLenLoginName + ') ,HostName = CASE hostname When Null Then '' .'' When '' '' Then '' .'' Else substring(hostname,1,' + @charMaxLenHostName + ') END ,BlkBy = CASE isnull(convert(char(5),blocked),''0'') When ''0'' Then '' .'' Else isnull(convert(char(5),blocked),''0'') END ,DBName = substring(case when dbid = 0 then null when dbid <> 0 then db_name(dbid) end,1,' + @charMaxLenDBName + ') ,Command = substring(cmd,1,' + @charMaxLenCommand + ') ,CPUTime = substring(convert(varchar,cpu),1,' + @charMaxLenCPUTime + ') ,DiskIO = substring(convert(varchar,physical_io),1,' + @charMaxLenDiskIO + ') ,LastBatch = substring(last_batch_char,1,' + @charMaxLenLastBatch + ') ,ProgramName = substring(program_name,1,' + @charMaxLenProgramName + ') ,SPID = convert(char(5),spid) --Handy extra for right-scrolling users. from #tb1_sysprocesses --Usually DB qualification is needed in exec(). where spid >= ' + @charspidlow + ' and spid <= ' + @charspidhigh + ' -- (Seems always auto sorted.) order by spid_sort SET nocount on ' ) /*****AKUNDONE: removed from where-clause in above EXEC sqlstr sid >= ' + @charsidlow + ' and sid <= ' + @charsidhigh + ' and **************/ LABEL_86RETURN: if (object_id('tempdb..#tb1_sysprocesses') is not null) drop table #tb1_sysprocesses return @retcode -- sp_who2 go checkpoint go --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- ---- Create following procs last, since they reference other procedures. raiserror(15339,-1,-1,'sp_check_removable') go create procedure sp_check_removable @autofix varchar(4) as declare @dbosid varbinary (86) declare @dbname sysname declare @exec_stmt nvarchar(540) declare @fgname sysname select @dbname=db_name() /* Verify that SA owns the database. */ select @dbosid = sid from master..sysdatabases where name = @dbname if @dbosid <> 0x01 if @autofix='auto' begin -- changing DBO to SA update sysdatabases set sid = 0x01 where name = @dbname update sysusers set sid = 0x01 where uid = 1 end else begin raiserror(15258,-1,-1, @dbname) return(1) end -- USE CORRECT non-dbo/guest CHECKING declare @ret int exec @ret = sp_check_removable_sysusers @autofix if @ret <> 0 return 1 -- Run UPDATE STATISTICS on all user tables if there are -- no user defined filegroups if @autofix='auto' and (select count(*) from sysfilegroups) = 1 begin select @exec_stmt = N'USE ' + quotename( @dbname , '[') + N' exec sp_updatestats ''RESAMPLE'' ' exec (@exec_stmt) end exec('dump tran '+@dbname+' with no_log') if (select count(*) from sysfilegroups) > 1 begin if @autofix='auto' begin -- Mark any non-primary filegroups as READONLY DECLARE ms_crs_fg CURSOR LOCAL STATIC FOR SELECT groupname FROM sysfilegroups fg WHERE fg.groupid > 1 -- not primary AND fg.status & 0x8 = 0 -- not already readonly AND (SELECT count (*) FROM sysfiles f WHERE f.groupid = fg.groupid) > 0 -- has some files OPEN ms_crs_fg FETCH NEXT FROM ms_crs_fg INTO @fgname WHILE (@@FETCH_STATUS <> -1) BEGIN SELECT @exec_stmt = 'ALTER DATABASE ' + quotename( @dbname , '[') + ' MODIFY FILEGROUP ' + quotename( @fgname , '[') + ' READONLY' EXEC (@exec_stmt) FETCH NEXT FROM ms_crs_fg INTO @fgname END CLOSE ms_crs_fg DEALLOCATE ms_crs_fg end else begin if exists (SELECT groupname FROM sysfilegroups fg WHERE fg.groupid > 1 -- not primary AND fg.status & 0x8 = 0 -- not already readonly AND (SELECT count (*) FROM sysfiles f WHERE f.groupid = fg.groupid) > 0) -- has some files begin raiserror(15358,-1,-1) SELECT groupname FROM sysfilegroups fg WHERE fg.groupid > 1 -- not primary