数据库十条技巧

上一篇 / 下一篇  2019-09-14 11:03:51 / 个人分类:数据库

1、1=1,1=2的使用,在SQL语句组合时用的较多
“where 1=1” 是表示选择全部  “where 1=2”全部不选,
如:
if @strWhere !=‘
begin
set @strSQL = ‘select count(*) as Total from [‘ + @tblName + ‘] where ‘ + @strWhere
end
else
begin

set @strSQL = ‘select count(*) as Total from [‘ + @tblName + ‘]‘
end
我们可以直接写成
set @strSQL = ‘select count(*) as Total from [‘ + @tblName + ‘] where 1=1 安定 ‘+ @strWhere


2、收缩数据库
--重建索引
DBCC REINDEX
DBCC INDEXDEFRAG
--收缩数据和日志
DBCC SHRINKDB
DBCC SHRINKFILE


3、压缩数据库
dbcc shrinkdatabase(dbname)

4、转移数据库给新用户以已存在用户权限
exec sp_change_users_login ‘update_one‘,‘newname‘,‘oldname‘
go


5、检查备份集
RESTORE VERIFYONLY from disk=‘E:\dvbbs.bak‘

6、修复数据库
Alter DATABASE [dvbbs] SET SINGLE_USER
GO

DBCC CHECKDB(‘dvbbs‘,repair_allow_data_loss) WITH TABLOCK
GO
Alter DATABASE [dvbbs] SET MULTI_USER
GO

7、日志清除
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
        @MaxMinutes INT,
        @NewSize INT

USE    tablename            -- 要操作的数据库名
Select  @LogicalFileName = ‘tablename_log‘,  -- 日志文件名
@MaxMinutes = 10,              -- Limit on time allowed to wrap log.           

        @NewSize = 1                  -- 你想设定的日志文件的大小(M)

-- Setup / initialize
DECLARE @OriginalSize int
Select @OriginalSize = size
  FROM sysfiles

Where name = @LogicalFileName
Select ‘Original Size of ‘ + db_name() + ‘ LOG is ‘ +
        CONVERT(VARCHAR(30),@OriginalSize) + ‘ 8K pages or ‘ +

       CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + ‘MB‘
  FROM sysfiles
  Where name = @LogicalFileName
Create TABLE DummyTrans
  (DummyColumn char (8000) not null)

DECLARE @Counter  INT,
        @StartTime DATETIME,
        @TruncLog  VARCHAR(255)
Select  @StartTime = GETDATE(),
        @TruncLog = ‘BACKUP LOG ‘ + db_name() + ‘ WITH TRUNCATE_ONLY‘


DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
-- Wrap the log if necessary.
WHILE    @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired

      AND @OriginalSize = (Select size FROM sysfiles Where name = @LogicalFileName)
      AND (@OriginalSize * 8 /1024) > @NewSize
  BEGIN -- Outer loop.
    Select @Counter = 0

   WHILE  ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
      BEGIN -- update
        Insert DummyTrans VALUES (‘Fill Log‘)
        Delete DummyTrans

       Select @Counter = @Counter + 1
      END
    EXEC (@TruncLog)
  END
Select ‘Final Size of ‘ + db_name() + ‘ LOG is ‘ +

        CONVERT(VARCHAR(30),size) + ‘ 8K pages or ‘ +
        CONVERT(VARCHAR(30),(size*8/1024)) + ‘MB‘
  FROM sysfiles
  Where name = @LogicalFileName

Drop TABLE DummyTrans
SET NOCOUNT OFF


8、说明:更改某个表
exec sp_changeobjectowner ‘tablename‘,‘dbo‘

9、存储更改全部表


Create PROCEDURE dbo.User_ChangeObjectOwnerBatch
@OldOwner as NVARCHAR(128),
@NewOwner as NVARCHAR(128)
AS


DECLARE @Name  as NVARCHAR(128)
DECLARE @Owner  as NVARCHAR(128)
DECLARE @OwnerName  as NVARCHAR(128)


DECLARE curObject CURSOR FOR
select ‘Name‘  = name,
  ‘Owner‘  = user_name(uid)
from sysobjects
where user_name(uid)=@OldOwner
order by name

OPEN  curObject

FETCH NEXT FROM curObject INTO @Name, @Owner
WHILE(@@FETCH_STATUS=0)
BEGIN
if @Owner=@OldOwner

begin
  set @OwnerName = @OldOwner + ‘.‘ + rtrim(@Name)
  exec sp_changeobjectowner @OwnerName, @NewOwner
end
-- select @name,@NewOwner,@OldOwner


FETCH NEXT FROM curObject INTO @Name, @Owner
END

close curObject
deallocate curObject
GO


10、SQL SERVER中直接循环写入数据
declare @i int
set @i=1
while @i<30

begin
  insert into test (userid) values(@i)
  set @i=@i+1
end  

TAG:

 

评分:0

我来说两句

显示全部

:loveliness: :handshake :victory: :funk: :time: :kiss: :call: :hug: :lol :'( :Q :L ;P :$ :P :o :@ :D :( :)

日历

« 2019-11-09  
     12
3456789
10111213141516
17181920212223
24252627282930

我的存档

数据统计

  • 访问量: 2182
  • 日志数: 13
  • 建立时间: 2019-09-13
  • 更新时间: 2019-09-23

RSS订阅

Open Toolbar