分类
学习笔记

SQLserver字符串分割函数

来源互联网:

一、按指定符号分割字符串,返回分割后的元素个数

CREATE FUNCTION [dbo].[Get_StrArrayLength]
    (
      @str VARCHAR(1024) ,  --要分割的字符串  
      @split VARCHAR(10)  --分隔符号  
    )
RETURNS INT
AS 
    BEGIN  
        DECLARE @location INT  
        DECLARE @start INT  
        DECLARE @length INT  
  
        SET @str = LTRIM(RTRIM(@str))  
        SET @location = CHARINDEX(@split, @str)  
        SET @length = 1  
        WHILE @location <> 0 
            BEGIN  
                SET @start = @location + 1  
                SET @location = CHARINDEX(@split, @str, @start)  
                SET @length = @length + 1  
            END  
        RETURN @length  
    END  

二、按指定符号分割字符串,返回分割后指定索引的第几个元素

CREATE FUNCTION [dbo].[Get_StrArrayStrOfIndex]
    (
      @str VARCHAR(1024) ,  --要分割的字符串  
      @split VARCHAR(10) ,  --分隔符号  
      @index INT --取第几个元素  
    )
RETURNS VARCHAR(1024)
AS 
    BEGIN  
        DECLARE @location INT  
        DECLARE @start INT  
        DECLARE @next INT  
        DECLARE @seed INT  
  
        SET @str = LTRIM(RTRIM(@str))  
        SET @start = 1  
        SET @next = 1  
        SET @seed = LEN(@split)  
    
        SET @location = CHARINDEX(@split, @str)  
        WHILE @location <> 0
            AND @index > @next 
            BEGIN  
                SET @start = @location + @seed  
                SET @location = CHARINDEX(@split, @str, @start)  
                SET @next = @next + 1  
            END  
        IF @location = 0 
            SELECT  @location = LEN(@str) + 1  
 --这儿存在两种情况:1、字符串不存在分隔符号 2、字符串中存在分隔符号,跳出while循环后,@location为0,那默认为字符串后边有一个分隔符号。  
    
        RETURN SUBSTRING(@str,@start,@[email protected]start)  
    END  
分类
日记

Sql Server2000/2005 查询所有表的大小和空间使用信息

sql Server2000:

SELECT  OBJECT_NAME(id) tablename ,
        8 * reserved / 1024 reserved ,
        RTRIM(8 * dpages / 1024) + 'Mb' used ,
        8 * ( reserved - dpages ) / 1024 unused ,
        8 * dpages / 1024 - rows / 1024 * minlen / 1024 free ,
        rows ,
        *
FROM    sysindexes
--where indid=1
ORDER BY reserved DESC

sql Server2005:

IF NOT EXISTS ( SELECT  *
                FROM    dbo.sysobjects
                WHERE   id = OBJECT_ID(N'[dbo].[tablespaceinfo]')
                        AND OBJECTPROPERTY(id, N'IsUserTable') = 1 ) 
    CREATE TABLE tablespaceinfo --创建结果存储表
        (
          nameinfo VARCHAR(50) ,
          rowsinfo INT ,
          reserved VARCHAR(20) ,
          datainfo VARCHAR(20) ,
          index_size VARCHAR(20) ,
          unused VARCHAR(20)
        )

DELETE  FROM tablespaceinfo
 --清空数据表

DECLARE @tablename VARCHAR(255)
 --表名称

DECLARE @cmdsql VARCHAR(500)

DECLARE Info_cursor CURSOR FOR
SELECT o.name
FROM dbo.sysobjects o WHERE OBJECTPROPERTY(o.id, N'IsTable') = 1
AND o.name NOT LIKE N'#%%' ORDER BY o.name

OPEN Info_cursor

FETCH NEXT FROM Info_cursor
INTO @tablename 

WHILE @@FETCH_STATUS = 0 
    BEGIN

        IF EXISTS ( SELECT  *
                    FROM    dbo.sysobjects
                    WHERE   id = OBJECT_ID(@tablename)
                            AND OBJECTPROPERTY(id, N'IsUserTable') = 1 ) 
            EXECUTE sp_executesql N'insert into tablespaceinfo exec sp_spaceused @tbname',
                N'@tbname varchar(255)', @tbname = @tablename

        FETCH NEXT FROM Info_cursor
INTO @tablename
    END

CLOSE Info_cursor
DEALLOCATE Info_cursor
GO

--itlearner注:显示数据库信息
sp_spaceused @updateusage = 'TRUE' 
--itlearner注:显示表信息
SELECT  *
FROM    tablespaceinfo
ORDER BY CAST(LEFT(LTRIM(RTRIM(reserved)), LEN(LTRIM(RTRIM(reserved))) - 2) AS INT) DESC 

数据库运行一段时间,数据增加,有的表大小都过G 的,根据需要,可以适当删除一些垃圾数据信息,另外清理日志,以及收缩数据库,也是处理数据库的必要手段。给数据瘦身吧!