個人常用 SQL Server 腳本

Database > SQLServer SQLServer

發布時間 : 2022-05-04 02:03

字數:561 觀看數 :

新增 Table
新增 SP
異動 Table Schema
新增 Type
新增 DB User權限

新增 Table

新增資料表 Exam.dbo.TestTable

USE [Exam]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestTable]') AND type in (N'U'))
BEGIN
    EXEC sys.sp_dropextendedproperty @name=N'MS_Description' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'TestTable', @level2type=N'COLUMN',@level2name=N'TestName'

    EXEC sys.sp_dropextendedproperty @name=N'MS_Description' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'TestTable', @level2type=N'COLUMN',@level2name=N'TestID'

    DROP TABLE [dbo].[TestTable]
END
GO

/****** Object:  Table [dbo].[TestTable]    Script Date: 2022/5/3 下午 02:44:15 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TestTable](
    [TestID] [nchar](10) NOT NULL,
    [TestName] [nchar](10) NULL,
 CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED 
(
    [TestID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'測試ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'TestTable', @level2type=N'COLUMN',@level2name=N'TestID'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'測試名稱' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'TestTable', @level2type=N'COLUMN',@level2name=N'TestName'
GO

新增 SP

新增預存程序 Exam.dbo.SP_Query_TestTable

USE [Exam]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_Query_TestTable]') AND type IN (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_Query_TestTable]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/*
    ==================================================================
    Description
         
                
    ==================================================================
    History    
        2020/05/14        誰    Created. 

    =================================================================
    Step
    
    ==================================================================
    Result

    ==================================================================
    Example
    
        EXEC Exam.dbo.SP_Query_TestTable
            @QueryParam = '1'

            
*/


CREATE PROCEDURE [dbo].[SP_Query_TestTable]
     @QueryParam        NCHAR(10)    -- 查詢參數
AS
BEGIN    
    --切換為最大權限使用者
    --EXECUTE AS LOGIN = 'Executer';
    SET NOCOUNT ON;
    
    SELECT * FROM dbo.TestTable
    WHERE TestID = @QueryParam

END

GO

GRANT EXECUTE ON [SP_Query_TestTable] TO [PUBLIC] AS [dbo] ;
GO

異動 Table Schema

新增 Exam.dbo.TestTable CreateDate 欄位

USE [Exam]
GO

IF ( SELECT COUNT(NAME) FROM SYSCOLUMNS
     WHERE ID = ( SELECT ID FROM SYSOBJECTS WHERE NAME = 'TestTable' )
   AND NAME = 'CreateDate' ) = 0
BEGIN
    ALTER TABLE dbo.TestTable ADD CreateDate Datetime;
END

新增 Type

新增 Exam.dbo.UDT_TestType

USE [Exam]
GO

/****** Object:  UserDefinedTableType [dbo].[UDT_TestType]    Script Date: 07/02/2019 17:46:14 ******/
IF  EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'UDT_TestType' AND ss.name = N'dbo')
DROP TYPE [dbo].[UDT_TestType]
GO

USE [Exam]
GO

/****** Object:  UserDefinedTableType [dbo].[UDT_TestType]    Script Date: 07/02/2019 17:46:14 ******/
CREATE TYPE [dbo].[UDT_TestType] AS TABLE(
    [RowNo]            INT
    , [TestName]    NCHAR(10)
)
GO

GRANT EXECUTE ON TYPE::[dbo].[UDT_TestType] TO [public] AS [dbo]
GO

新增 DB User權限

USE [Exam]
GO

IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'WebUser')
BEGIN
    ALTER ROLE [db_datareader] ADD MEMBER [WebUser]
END

轉載請註明來源，若有任何錯誤或表達不清楚的地方，歡迎在下方評論區留言，也可以來信至 leozheng0621@gmail.com
如果文章對您有幫助，歡迎斗內(donate)，請我喝杯咖啡