卡西卡的小寶庫
寶庫寶庫寶庫
Showing posts with label Stored Procedure. Show all posts
Showing posts with label Stored Procedure. Show all posts

[SP]up_Desc

Posted In: , . By 卡西卡

類似Oracle的desc指令

CREATE PROCEDURE up_desc (
    @TABLE_NAME NVARCHAR(128)  -- 資料表名稱
)AS
SELECT COLUMN_NAME,
CASE DATA_TYPE
        WHEN 'char' THEN DATA_TYPE+'('+CONVERT(VARCHAR(10),CHARACTER_MAXIMUM_LENGTH)+')'
        WHEN 'varchar' THEN DATA_TYPE+'('+CONVERT(VARCHAR(10),CHARACTER_MAXIMUM_LENGTH)+')'
        WHEN 'nvarchar' THEN DATA_TYPE+'('+CONVERT(VARCHAR(10),CHARACTER_MAXIMUM_LENGTH)+')'
        WHEN 'numeric' THEN DATA_TYPE+'('+CONVERT(VARCHAR(10),NUMERIC_PRECISION)+','+CONVERT(VARCHAR(10),NUMERIC_SCALE)+')'
        ELSE DATA_TYPE END AS OBJECT_DATA_TYPE,
    B.value as [OBJECT_DESCRIPTION]
FROM [INFORMATION_SCHEMA].[COLUMNS] A
  LEFT JOIN ::fn_listextendedproperty ('MS_Description', 'user', 'dbo', 'TABLE', @TABLE_NAME, 'COLUMN', default) B ON A.COLUMN_NAME=B.objname
WHERE TABLE_NAME=@TABLE_NAME
ORDER BY A.ORDINAL_POSITION

 
Up?
5

[SP]up_Obj

Posted In: , . By 卡西卡

尋找view,proc,func中有某個字串

CREATE PROC up_obj (
  @STR VARCHAR(100)
) AS

SELECT object_name(id) AS OBJNAME
FROM [syscomments]
WHERE [text] like '%'+@STR+'%'
ORDER BY object_name(id)

 
Up?
0

[SP]up_Select

Posted In: , . By 卡西卡

將資料表所有欄位組成SELECT句

CREATE PROCEDURE up_Select (
    @TABLE_NAME NVARCHAR(128) -- 資料表名稱
)AS
DECLARE @BUF NVARCHAR(1000)
DECLARE @COLUMN_NAME NVARCHAR(128)

SET @BUF=''

DECLARE curView CURSOR  FOR
    SELECT COLUMN_NAME
    FROM [INFORMATION_SCHEMA].[COLUMNS]
    WHERE TABLE_NAME=@TABLE_NAME

OPEN curView
FETCH curView
INTO @COLUMN_NAME

WHILE @@FETCH_STATUS = 0
    BEGIN -- WHILE
        SELECT @BUF=@BUF + @COLUMN_NAME + ','
        FETCH curView INTO @COLUMN_NAME
    END -- WHILE

CLOSE curView
DEALLOCATE curView

IF Len(@BUF)>0
BEGIN
    SELECT @BUF = LEFT(@BUF,LEN(@BUF)-1)
    PRINT 'SELECT'
    PRINT @BUF
    PRINT 'FROM ['+@TABLE_NAME+']'
END
ELSE
    PRINT '(none)'

 
Up?
1

[SP]up_RefreshView

Posted In: , . By 卡西卡

重整所有視表。
sp_refreshview [msdn]

CREATE PROCEDURE [up_RefreshView] AS
DECLARE @ViewName sysname

DECLARE curView CURSOR  FOR
    SELECT TABLE_NAME
    FROM [INFORMATION_SCHEMA].[TABLES]
    WHERE TABLE_TYPE='VIEW' AND [TABLE_SCHEMA]<>'INFORMATION_SCHEMA' AND SUBSTRING(TABLE_NAME,1,3)!='sys'

OPEN curView FETCH curView INTO @ViewName

WHILE @@FETCH_STATUS = 0
    BEGIN -- WHILE
        print @ViewName
        exec sp_refreshview @ViewName
        FETCH curView INTO @ViewName
    END -- WHILE

CLOSE curView
DEALLOCATE curView

 
Up?
0
Older Posts