Tag Archive | "mssql query"

Tags:

SQL queries


Repair a suspect Database

ALTER DATABASE DBNAME SET EMERGENCY;
GO
ALTER DATABASE DBNAME SET SINGLE_USER;
GO
DBCC CHECKDB (DemoSuspect, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
GO

Change all objects in a DB to dbo

— change all onjects in a DB to dbo.

DECLARE objlist CURSOR   
READ_ONLY   
FOR select Owner = user_name(uid),name from sysobjects where type in (‘P’,'U’,'V’) ORDER BY NAME   
  
DECLARE @name sysname   
Declare @owner sysname   
declare @fullName sysname   
  
OPEN objlist   
  
FETCH NEXT FROM objlist INTO @owner, @name   
WHILE (@@fetch_status <> -1)   
BEGIN   
IF (@@fetch_status <> -2)   
BEGIN   
IF @OWNER <> ‘dbo’   
begin   
set @fullName = ‘[' + @owner + '].[' + @name + ']‘   
exec sp_changeobjectowner @objname = @fullName , @newowner = ‘dbo’   
PRINT @fullname   
end   
END   
FETCH NEXT FROM objlist INTO @owner, @name   
END   
  
CLOSE OBJLIST   
DEALLOCATE OBJLIST   
GO

Check the size of a DB

EXEC sp_spaceused @updateusage = N’TRUE’;

Share

Posted in SQL ServerComments (4)