目录：
字符串分拆函数（F_SplitStr，返回一个表）
查询所有表的记录数及硬盘大小（sp_ShowTableSpace）
暂停(启用)所有触发器及外键约束（P_set_trigger）
检查临时表是否存在
用游标UPDATE的当前位置的行资料
从系统表中读取视图的字段列表
同步新旧数据库的表（sp_insert ）
返来某个表的字段说明（fn_listextendedproperty）
刷新数据库视图（P_RefreshView ）
--------------------------------------------------------------------------------
字符串分拆函数（返回一个表）
ALTER FUNCTION F_SplitStr
(@S VARCHAR(8000), -- 待分拆的字符串
@Split VARCHAR(10)) -- 数据分隔符
RETURNS @ret TABLE (col varchar(100))
AS
BEGIN
DECLARE @SplitLen INT
SET @SplitLen = LEN(@Split + 'a') - 2
WHILE CHARINDEX(@Split, @S) > 0
BEGIN
INSERT @ret VALUES(LEFT(@S, CHARINDEX(@Split, @S) - 1))
SET @S = STUFF(@S, 1, CHARINDEX(@Split, @S) + @SplitLen, '')
END
INSERT @ret VALUES(@S)
RETURN
END
--------------------------------------------------------------------------------
查询所有表的记录数及硬盘大小
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
-- SQL2000中显示各表空间使用情况的存储过程分类:SQL专栏
-- 显示各表在数据库中的空间使用情况
ALTER procedure sp_ShowTableSpace
as
begin
declare @id int -- The object id of @objname.
declare @type character(2) -- The object type.
declare @pages int -- Working variable for size calc.
declare @dbname sysname
declare @dbsize dec(15,0)
declare @logsize dec(15)
declare @bytesperpage dec(15,0)
declare @pagesperMB dec(15,0)
declare @objname nvarchar(776) -- The object we want size on.
declare @updateusage varchar(5) -- Param. for specifying that
create table #temp1
(
表名 varchar(200) null,
行数 char(11) null,
保留空间 varchar(15) null,
数据使用空间 varchar(15) null,
索引使用空间 varchar(15) null,
未用空间 varchar(15) null
)
--select @objname=''N_dep'' -- usage info. should be updated.
select @updateusage='false'
/*Create temp tables before any DML to ensure dynamic
** We need to create a temp table to do the calculation.
** reserved: sum(reserved) where indid in (0, 1, 255)
** data: sum(dpages) where indid < 2 + sum(used) where indid = 255 (text)
** indexp: sum(used) where indid in (0, 1, 255) - data
** unused: sum(reserved) - sum(used) where indid in (0, 1, 255)
*/
declare cur_table cursor for
select name from sysobjects where type='u'
Open cur_table
fetch next from cur_table into @objname
While @@FETCH_STATUS=0
begin
create table #spt_space
(
rows int null,
reserved dec(15) null,
data dec(15) null,
indexp dec(15) null,
unused dec(15) null
)
/*
** Check to see if user wants usages updated.
*/
if @updateusage is not null
begin
select @updateusage=lower(@updateusage)
if @updateusage not in ('true','false')
begin
raiserror(15143,-1,-1,@updateusage)
return(1)
end
end
/*
** Check to see that the objname is local.
*/
if @objname IS NOT NULL
begin
select @dbname = parsename(@objname, 3)
if @dbname is not null and @dbname <> db_name()
begin
raiserror(15250,-1,-1)
return (1)
end
if @dbname is null
select @dbname = db_name()
/*
** Try to find the object.
*/
select @id = null
select @id = id, @type = xtype
from sysobjects
where id = object_id(@objname)
/*
** Does the object exist?
*/
if @id is null
begin
raiserror(15009,-1,-1,@objname,@dbname)
return (1)
end
if not exists (select * from sysindexes
where @id = id and indid < 2)
if @type in ('P ','D ','R ','TR','C ','RF') --data stored in sysprocedures
begin
raiserror(15234,-1,-1)
return (1)
end
else if @type = 'V ' -- View => no physical data storage.
begin
raiserror(15235,-1,-1)
return (1)
end
else if @type in ('PK','UQ') -- no physical data storage. --?!?! too many similar messages
begin
raiserror(15064,-1,-1)
return (1)
end
else if @type = 'F ' -- FK => no physical data storage.
begin
raiserror(15275,-1,-1)
return (1)
end
end
/*
** Update usages if user specified to do so.
*/
if @updateusage = 'true'
begin
if @objname is null
dbcc updateusage(0) with no_infomsgs
else
dbcc updateusage(0,@objname) with no_infomsgs
print ' '
end
set nocount on
/*
** If @id is null, then we want summary data.
*/
/* Space used calculated in the following way
** @dbsize = Pages used
** @bytesperpage = d.low (where d = master.dbo.spt_values) is
** the # of bytes per page when d.type = ''E'' and
** d.number = 1.
** Size = @dbsize * d.low / (1048576 (OR 1 MB))
*/
if @id is null
begin
select @dbsize = sum(convert(dec(15),size))
from dbo.sysfiles
where (status & 64 = 0)
select @logsize = sum(convert(dec(15),size))
from dbo.sysfiles
where (status & 64 <> 0)
select @bytesperpage = low
from master.dbo.spt_values
where number = 1
and type = 'E'
select @pagesperMB = 1048576 / @bytesperpage
select database_name = db_name(),
database_size =
ltrim(str((@dbsize + @logsize) / @pagesperMB,15,2) + ' MB'),
'unallocated space' =
ltrim(str((@dbsize -
(select sum(convert(dec(15),reserved))
from sysindexes
where indid in (0, 1, 255)
)) / @pagesperMB,15,2)+ ' MB')
print ' '
/*
** Now calculate the summary data.
** reserved: sum(reserved) where indid in (0, 1, 255)
*/
insert into #spt_space (reserved)
select sum(convert(dec(15),reserved))
from sysindexes
where indid in (0, 1, 255)
/*
** data: sum(dpages) where indid < 2
** + sum(used) where indid = 255 (text)
*/
select @pages = sum(convert(dec(15),dpages))
from sysindexes
where indid < 2
select @pages = @pages + isnull(sum(convert(dec(15),used)), 0)
from sysindexes
where indid = 255
update #spt_space
set data = @pages
/* index: sum(used) where indid in (0, 1, 255) - data */
update #spt_space
set indexp = (select sum(convert(dec(15),used))
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
insert into #temp1
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'
Drop table #spt_space
end
fetch next from cur_table into @objname
end
Close cur_table
DEALLOCATE cur_table
Select * from #temp1 order by len(保留空间) desc,保留空间 desc
Drop table #temp1
return (0)
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--------------------------------------------------------------------------------
暂停(启用)所有触发器及外键约束
/*
说明： 暂停/启用所有触发器及外键约束
*/
ALTER PROCEDURE P_set_trigger @flag bit AS
SET NOCOUNT ON
DECLARE @TableName sysname, @ConstraintName sysname, @Action nvarchar(7), @SQLString nvarchar(500)
-- TRIGGER
SET @Action = case when @flag = 1 then 'ENABLE' else 'DISABLE' end
DECLARE cTmp CURSOR FOR SELECT name FROM sysobjects WHERE xtype = N'U'
OPEN cTmp
FETCH cTmp INTO @TableName
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @SQLString = 'ALTER TABLE ' + @TableName + ' ' + @Action + ' TRIGGER all'
EXEC sp_executesql @SQLString
-- Next
FETCH cTmp INTO @TableName
END
CLOSE cTmp
DEALLOCATE cTmp
if @flag = 1
PRINT 'Set all Trigger to ENABLE'
else
PRINT 'Set all Trigger to DISABLE'
-- CONSTRAINT
SET @Action = case when @flag = 1 then 'CHECK' else 'NOCHECK' end
DECLARE cTmp CURSOR FOR SELECT object_name(parent_obj), name FROM sysobjects WHERE xtype = N'F'
OPEN cTmp
FETCH cTmp INTO @TableName, @ConstraintName
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @SQLString = 'ALTER TABLE ' + @TableName + ' ' + @Action + ' CONSTRAINT ' + @ConstraintName
EXEC sp_executesql @SQLString
-- Next
FETCH cTmp INTO @TableName, @ConstraintName
END
CLOSE cTmp
DEALLOCATE cTmp
if @flag = 1
PRINT 'Set all Constraint to ENABLE'
else
PRINT 'Set all Constraint to DISABLE'
--------------------------------------------------------------------------------
检查临时表是否存在
IF exists(SELECT * FROM tempdb..SYSOBJECTS WHERE ID = object_id('tempdb..#Table'))
DROP TABLE #Table
--------------------------------------------------------------------------------
用游标UPDATE的当前位置的行资料
-- 计算结余数
DECLARE @ZSA NUMERIC(19, 6), @JEA NUMERIC(19, 6),
@ZSB NUMERIC(19, 6), @JEB NUMERIC(19, 6),
@SYZS NUMERIC(19, 6), @SYJE NUMERIC(19, 6)
SELECT @SYZS = SUM(ZS), @SYJE = SUM(JE)
FROM V_YSKLSZ
WHERE KHID = @KHID AND RQ < @RQA
DECLARE Cur CURSOR FOR SELECT ZSA, JEA, ZSB, JEB FROM #TMP FOR UPDATE OF SYZS, SYJE
OPEN Cur
FETCH NEXT FROM Cur INTO @ZSA, @JEA, @ZSB, @JEB
WHILE (@@fetch_status = 0)
BEGIN
SET @SYZS = @SYZS + @ZSA - @ZSB
SET @SYJE = @SYJE + @JEA - @JEB
UPDATE #TMP SET SYZS = @SYZS, SYJE = @SYJE
WHERE CURRENT OF Cur
FETCH NEXT FROM Cur INTO @ZSA, @JEA, @ZSB, @JEB
END
CLOSE Cur
DEALLOCATE Cur
--------------------------------------------------------------------------------
SW Regsvr By T-SQL
DECLARE @strREG varchar(50)
SELECT @strREG =UPPER(REVERSE(RIGHT(RTRIM(HOSTNAME),5)+LTRIM(NET_ADDRESS)))
FROM MASTER.DBO.SYSPROCESSES
WHERE SPID = @@SPID
UPDATE appoption SET oVar = @strREG where osubkey = '斯玛软件服务器'
--------------------------------------------------------------------------------
从系统表中读取视图的字段列表
SELECT colid, name
FROM SYSCOLUMNS
WHERE ID = OBJECT_ID('View_Name')
--------------------------------------------------------------------------------
同步新旧数据库的表（sp_insert ）
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/*
说明: 从另一个数据库导入资料
备注: 只导入两边都有的字段
历史： 2008-10-25 Steven 创建
*/
ALTER PROC sp_insert
@TableName sysname, -- 表名
@SourceDatabase sysname, -- 源数据库
@DestDatabase sysname = '', -- 目标数据库 (默认为空,表示导入到当前数据库)
@OnlyGetSql bit = 0 -- 只返回SQL语句, 但不执行 (默认为False)
AS
SET NOCOUNT ON
-- TEST CODE
-- DECLARE @TableName sysname, @SourceDatabase sysname, @DestDatabase sysname, @OnlyGetSql bit
-- SELECT @TableName = 'YC_YC', @SourceDatabase = 'TH', @DestDatabase = 'DH_TMP'
SET @SourceDatabase = ISNULL(@SourceDatabase, '') + '..'
SET @DestDatabase = ISNULL(@DestDatabase, '')
IF @DestDatabase <> ''
SET @DestDatabase = @DestDatabase + '..'
IF exists(SELECT * FROM tempdb..SYSOBJECTS WHERE ID = object_id('tempdb..#Columns'))
DROP TABLE #Columns
CREATE TABLE #Columns(
RowType char(1),
ColumnName sysname,
DataType int)
EXEC('INSERT INTO #Columns SELECT ''S'', name, xtype FROM ' + @SourceDatabase + 'syscolumns WHERE id = object_id(''' + @SourceDatabase + @TableName + ''') AND autoval is null')
EXEC('INSERT INTO #Columns SELECT ''T'', name, xtype FROM ' + @DestDatabase + 'syscolumns WHERE id = object_id(''' + @DestDatabase + @TableName + ''') AND autoval is null')
DECLARE @ColumnName sysname, @ColumnList varchar(8000), @InsertSql varchar(8000)
DECLARE cTmp CURSOR FOR
SELECT S.ColumnName
FROM
(SELECT * FROM #Columns WHERE RowType = 'S') S,
(SELECT * FROM #Columns WHERE RowType = 'T') T
WHERE S.ColumnName = T.ColumnName
OPEN cTmp
FETCH cTmp INTO @ColumnName
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @ColumnList = CASE WHEN @ColumnList IS NULL THEN '' ELSE @ColumnList + ', ' END + @ColumnName
FETCH cTmp INTO @ColumnName
END
CLOSE cTmp
DEALLOCATE cTmp
SET @InsertSql = ''
SET @InsertSql = @InsertSql +
'DELETE ' + @DestDatabase + @TableName
IF @ColumnList IS NOT NULL
SET @InsertSql = @InsertSql + char(13) + char(10) +
'INSERT INTO ' + @DestDatabase + @TableName + '(' + @ColumnList + ')' + char(13) + char(10) +
' SELECT ' + @ColumnList + ' FROM ' + @SourceDatabase + @TableName
ELSE
SET @InsertSql = @InsertSql + char(13) + char(10) +
'-- 注意：找不到 ' + @DestDatabase + @TableName + '，所以没有执行 insert into 语句'
PRINT @InsertSql
IF ISNULL(@OnlyGetSql, 0) <> 1
BEGIN
SET NOCOUNT OFF
EXEC(@InsertSql)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--------------------------------------------------------------------------------
返来某个表的字段说明
方法1:
SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', '<TableName>', 'column', default)
方法2:
select a.ColID, cast(a.name as varchar(50)) FieldName, cast(b.value as varchar(50)) FieldDesc, cast(c.name as varchar(50)) FieldType
from syscolumns a
left join sysproperties b on a.id = b.id and a.colid = b.smallid
left join systypes c on a.xtype = c.xtype
where a.id = object_id('<TableName>')
--------------------------------------------------------------------------------
刷新数据库视图（P_RefreshView ）
/*
刷新数据库视图.sql
*/
ALTER PROC P_RefreshView
as
DECLARE @vName sysname
DECLARE refresh_cursor CURSOR FOR
SELECT Name from sysobjects WHERE xtype = 'V' order by crdate
FOR READ ONLY
OPEN refresh_cursor
FETCH NEXT FROM refresh_cursor
INTO @vName
WHILE @@FETCH_STATUS = 0
BEGIN
--刷新指定视图的元数据。由于视图所依赖的基础对象的更改，视图的持久元数据会过期。
exec sp_refreshview @vName
PRINT '视图' + @vName + '已更新'
FETCH NEXT FROM refresh_cursor
INTO @vName
END
CLOSE refresh_cursor
DEALLOCATE refresh_cursor
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO