---------------------------------------------------------------------
-- SQL Server 2005ストアドプロシージャプログラミング
-- 第15章 ストアドプロシージャの高度なプログラミング
---------------------------------------------------------------------

--------------------------------------------------------------------
-- 15.1 クエリの動的作成
---------------------------------------------------------------------

EXEC ('SELECT * FROM Contact')


DECLARE @chvTable SYSNAME
SET @chvTable = 'Contact'
EXEC ('SELECT * FROM ' + @chvTable)


DECLARE @chvTable SYSNAME
SET @chvTable = 'Contact'
EXEC ('SELECT * FROM @chvTable')


USE Asset5
EXEC ('USE pubs SELECT * FROM employee')
SELECT * FROM employee -- ここでエラーが発生


CREATE PROCEDURE dbo.ap_QBF_Contact_List
-- 指定されたパラメータに基づいて動的にクエリを生成する
-- テスト：EXEC dbo.ap_QBF_Contact_List @chvFirstName = 'Dejan'
(
    @chvFirstName VARCHAR(30) = NULL,
    @chvLastName VARCHAR(30) = NULL,
    @chvPhone VARCHAR(20) = NULL,
    @chvFax VARCHAR(20) = NULL,
    @chvEmail VARCHAR(128) = NULL,
    @insOrgUnitId SMALLINT = NULL,
    @chvUserName VARCHAR(50) = NULL,
    @debug INT = 0
)
AS
    SET NOCOUNT ON

    DECLARE @chvQuery NVARCHAR(max),
            @chvWhere NVARCHAR(max)

    SELECT @chvQuery = 'SET QUOTED_IDENTIFIER OFF SELECT * FROM dbo.Contact',
           @chvWhere = ''

    IF @chvFirstName IS NOT NULL
        SET @chvWhere = @chvWhere + ' FirstName = "'
                      + @chvFirstName + '" AND'

    IF @chvLastName IS NOT NULL
        SET @chvWhere = @chvWhere + ' LastName = "'
                      + @chvLastName + '" AND'

    IF @chvPhone IS NOT NULL
        SET @chvWhere = @chvWhere + ' Phone = "'
                      + @chvPhone + '" AND'

    IF @chvFax IS NOT NULL
        SET @chvWhere = @chvWhere + ' Fax = "'
                      + @chvFax + '" AND'

    IF @chvEmail IS NOT NULL
        SET @chvWhere = @chvWhere + ' Email = "'
                      + @chvEmail + '" AND'

    IF @insOrgUnitId IS NOT NULL
        SET @chvWhere = @chvWhere + ' OrgUnitId = '
                      + @insOrgUnitId + ' AND'

    IF @chvUserName IS NOT NULL
        SET @chvWhere = @chvWhere + ' UserName = "'
                      + @chvUserName + '"'

    IF @debug <> 0
        SELECT @chvWhere chvWhere

    -- 文字列の末尾の'AND'を削除する
    BEGIN TRY
        IF SUBSTRING(@chvWhere, LEN(@chvWhere) - 3, 4) = ' AND'
            SET @chvWhere = SUBSTRING(@chvWhere, 1, LEN(@chvWhere) - 3)
    END TRY

    BEGIN CATCH
        RAISERROR('Unable to remove last AND operator.', 16, 1)
        RETURN
    END CATCH

    IF @debug <> 0
        SELECT @chvWhere chvWhere

    BEGIN TRY
        IF LEN(@chvWhere) > 0
            SET @chvQuery = @chvQuery + ' WHERE ' + @chvWhere

        IF @debug <> 0
            SELECT @chvQuery Query

        -- 連絡先を取得する
        EXEC (@chvQuery)
    END TRY

    BEGIN CATCH
        DECLARE @s VARCHAR(max)
        SET @s = 'Unable to execute new query: ' + @chvQuery
        RAISERROR(@s, 16, 2)
        RETURN
    END CATCH

    RETURN


SELECT 'INSERT dbo.AcquisitionType VALUES('
       + CONVERT(VARCHAR, AcquisitionTypeId)
       + ', ''' + AcquisitionType
       + ''')' FROM dbo.AcquisitionType


CREATE PROC util.ap_DataGenerator
-- テーブルの内容を再現する一連の
-- INSERTステートメントを作成する
-- このステートメントでは、非常に長い列は処理されない
@table SYSNAME = 'Inventory',
@debug INT = 0
-- デバッグ：EXEC util.ap_DataGenerator @table = 'Location', @debug = 1
AS
    DECLARE @chvVal VARCHAR(max)
    DECLARE @chvSQL VARCHAR(max)
    DECLARE @chvColList VARCHAR(max)
    DECLARE @intColCount SMALLINT
    DECLARE @i SMALLINT

    SET @chvColList = ''
    SET @chvVal = ''

    SELECT @intColCount = MAX(ORDINAL_POSITION),
           @i = 1
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = @table

    WHILE @i <= @intColCount
    BEGIN
        SELECT @chvVal = @chvVal
            + '+'',''+case when ' + COLUMN_NAME
            + ' is null then ''null'' else ' +
            CASE
                WHEN DATA_TYPE IN('varchar', 'nvarchar', 'datetime',
                    'smalldatetime', 'char', 'nchar')
                THEN '''''''''+convert(varchar(max),'
                ELSE '+ convert(varchar(max),'
            END
            + CONVERT(VARCHAR(max), COLUMN_NAME) +
            CASE
                WHEN DATA_TYPE IN('varchar', 'nvarchar', 'datetime',
                    'smalldatetime','char', 'nchar')
                THEN ')+'''''''''
                ELSE ')'
            END
            + ' end '
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = @table
        AND ORDINAL_POSITION = @i

        -- 列リストを取得する
        SELECT @chvColList = @chvColList
            + ',' + CONVERT(VARCHAR(max), COLUMN_NAME)
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = @table
        AND ORDINAL_POSITION = @i

        SET @i = @i + 1
    END

    IF @debug <> 0 SELECT @chvVal [@chvVal]
    IF @debug <> 0 SELECT @chvColList [@chvColList]

    -- 先頭のカンマを削除
    SET @chvColList = SUBSTRING(@chvColList, 2, LEN(@chvColList))
    SET @chvVal = SUBSTRING(@chvVal, 6, LEN(@chvVal))

    -- 全体をまとめてコマンドを作成する
    SET @chvSQL = 'select ''Insert dbo.' + @table
        + '(' + @chvColList + ') values ('' + '
        + @chvVal + ' + '')'' from ' + @table

    -- 結果を得る
    IF @debug <> 0 SELECT @chvSQL chvSQL
    EXEC(@chvSQL)

    RETURN


EXECUTE sp_executesql
    @stmt = N'SELECT * FROM Asset5.dbo.Contact WHERE ContactId = @Id',
    @parms = N'@Id INT',
    @Id = 11
EXECUTE sp_executesql
    @stmt = N'SELECT * FROM Asset5.dbo.Contact WHERE ContactId = @Id',
    @parms = N'@Id INT',
    @Id = 313


DECLARE @chvQuery NVARCHAR(200)
SET @chvQuery = N'SELECT * FROM ' + DB_NAME()
              + N'.dbo.Contact WHERE ContactId = @Id'
EXECUTE sp_executesql @stmt = @chvQuery,
                      @parms = N'@Id INT',
                      @Id = 1
EXECUTE sp_executesql @stmt = @chvQuery,
                      @parms = N'@Id INT',
                      @Id = 313


Acme' DELETE INVENTORY --


SELECT * FROM vInventory
WHERE Make = 'Acme' DELETE INVENTORY --'


Acme' SELECT * FROM CUSTOMERS --


SELECT * FROM vInventory
WHERE Make = 'Acme' SELECT * FROM CUSTOMERS --'


122121 SELECT * FROM CUSTOMERS


SELECT * FROM vInventory
WHERE InventoryId = 122121 SELECT * FROM CUSTOMERS


SET @chvMake = REPLACE(@chvMake, CHAR(39), CHAR(39) + CHAR(39))


SELECT * FROM vInventory
WHERE Make = 'Dejan''s Computers Inc.'


SELECT * FROM vInventory
WHERE Make = 'Dejan'' SELECT * FROM CUSTOMERS --'


CREATE FUNCTION util.fnSafeDynamicString
-- 動的文字列で安全に文字列パラメータを使えるようにする
(
    @chvInput NVARCHAR(max),
    @bitLikeSafe BIT = 0  -- 文字列がLIKEで使われる場合は1を設定する
)
RETURNS NVARCHAR(max)
AS
BEGIN
    DECLARE @chvOutput NVARCHAR(max)
    SET @chvOutput = REPLACE(@chvInput, CHAR(39), CHAR(39) + CHAR(39))
    IF @bitLikeSafe = 1
    BEGIN
        -- 角かっこ（[]）の変換
        SET @chvOutput = REPLACE(@chvOutput, '[', '[[]')
        -- ワイルドカードの変換
        SET @chvOutput = REPLACE(@chvOutput, '%', '[%]')
        SET @chvOutput = REPLACE(@chvOutput, '_', '[_]')
    END
    RETURN (@chvOutput)
END


SELECT 'SELECT * FROM vInventory WHERE Make = '''
    + util.fnSafeDynamicString('Dejan' + CHAR(39) + 's Computers Inc.', 0)
    + ''''


SELECT 'SELECT * FROM vInventory WHERE Make LIKE '''
    + util.fnSafeDynamicString('%a', 1)
    + '%'''


---------------------------------------------------------------------
-- 15.2 TIMESTAMP値を使ったオプティミスティックロック
---------------------------------------------------------------------

UPDATE #aTable
SET description = 'test3'
WHERE id = 1
AND ts = 0x00000000000007D2


CREATE TABLE Stores(
    stor_id INT IDENTITY,
    stor_name VARCHAR(40),
    city VARCHAR(20),
    state CHAR(2),
    ts TIMESTAMP,
    CONSTRAINT PK_storeid PRIMARY KEY(stor_id)
)

INSERT INTO Stores(stor_name, city, state)
VALUES('Dejan''s Computers Inc.', 'Seattle', 'WA')

INSERT INTO Stores(stor_name, city, state)
VALUES('News & Brews', 'Los Gatos', 'CA')

GO

CREATE PROCEDURE dbo.ap_Stores_Update
-- Storesテーブルのレコードを更新する
-- 更新済みレコードの上書きを防止する
(
    @intStorId INT,
    @chvStoreName VARCHAR(40),
    @chvCity VARCHAR(20),
    @chrState CHAR(2),
    @tsOriginal TIMESTAMP
)
AS
    SET NOCOUNT ON

    UPDATE Stores
    SET stor_name= @chvStoreName,
        city = @chvCity,
        state = @chrState
    WHERE stor_id = @intStorId
    AND ts = @tsOriginal

    RETURN @@ROWCOUNT

---------------------------------------------------------------------
-- 15.3 フルテキスト検索
---------------------------------------------------------------------

SELECT LogId, UserName, Note FROM dbo.ActivityLog
WHERE FREETEXT(Note, 'ch5 validation 2.')

SELECT LogId, UserName, Note FROM dbo.ActivityLog
WHERE CONTAINS(Note, '"ch5" AND "validation"')

SELECT LogId, UserName, Note FROM dbo.ActivityLog
WHERE CONTAINS(Note, '"ch5" AND "validation" AND "2"')

---------------------------------------------------------------------
-- 15.4 ストアドプロシージャのネスト
---------------------------------------------------------------------

CREATE PROCEDURE dbo.ap_InventoryProperties_Get_TempTblOuter
/*
備品のプロパティを、次のようにセミコロンで区切ったリストを返す
Property=Value Unit; Property=Value Unit; Property=Value Unit; ...

テスト：
EXEC dbo.ap_InventoryProperties_Get_TempTblOuter 5
*/
@intInventoryId INT
AS
    SET NOCOUNT ON
    DECLARE @chvProperties VARCHAR(max)

    CREATE TABLE #List(
        Id INT IDENTITY(1, 1),
        Item VARCHAR(255)
    )

    -- 備品と関連付けられているプロパティを特定する
    INSERT INTO #List(Item)
        SELECT Property + '=' + Value + ' ' + COALESCE(Unit, '') + '; '
        FROM InventoryProperty INNER JOIN Property
        ON InventoryProperty.PropertyId = Property.PropertyId
        WHERE InventoryProperty.InventoryId = @intInventoryId

    -- レコードを1つのVARCHAR変数に変換するストアドプロシージャを呼び出す
    EXEC util.ap_TempTbl2Varchar @chvProperties OUTPUT

    -- 結果を表示する
    SELECT @chvProperties Properties

    DROP TABLE #List

    RETURN 0


CREATE PROCEDURE util.ap_TempTbl2Varchar
-- ＃List一時テーブルの情報を1つのVARCHAR型の変数に格納する
@chvResult VARCHAR(max) OUTPUT
AS
    SET NOCOUNT ON

    DECLARE @intCountItems INT,
            @intCounter INT,
            @chvItem VARCHAR(255)

    -- ループを設定する
    SELECT @intCountItems = COUNT(*),
           @intCounter = 1,
           @chvResult = ''
    FROM #List

    -- プロパティのリスト全体をループする
    WHILE @intCounter <= @intCountItems
    BEGIN
        -- プロパティを1つ取得する
        SELECT @chvItem = Item
        FROM #List
        WHERE Id = @intCounter

        -- リストを作成する
        SET @chvResult = @chvResult + @chvItem

        -- 前に戻って次のプロパティを取得する
        SET @intCounter = @intCounter + 1
    END

    RETURN 0


EXEC dbo.ap_InventoryProperties_Get_TempTblOuter 5


CREATE PROCEDURE dbo.ap_InventoryProperties_Get_wNestedCursor
/*
備品のプロパティを、次のようにセミコロンで区切ったリストを返す
Property=Value Unit; Property=Value Unit; Property=Value Unit; ...

テスト：
DECLARE @chvResult VARCHAR(max)
EXEC dbo.ap_InventoryProperties_Get_wNestedCursor 5, @chvResult OUTPUT, 1
SELECT @chvResult
*/
(
    @intInventoryId INT,
    @chvProperties VARCHAR(max) OUTPUT,
    @debug INT = 0
)
AS
    SELECT @chvProperties = ''

    DECLARE curItems CURSOR FOR
        SELECT Property + '=' + Value + ' '
            + COALESCE(Unit, '') + '; ' Item
        FROM InventoryProperty INNER JOIN Property
        ON InventoryProperty.PropertyId = Property.PropertyId
        WHERE InventoryProperty.InventoryId = @intInventoryId

    OPEN curItems

    EXEC util.ap_Cursor2Varchar @chvProperties OUTPUT, @debug

    CLOSE curItems
    DEALLOCATE curItems

    RETURN 0


CREATE PROCEDURE util.ap_Cursor2Varchar
-- 外部のストアドプロシージャで開始されたカーソルからの情報を処理
-- レコードを1つのVARCHAR型変数に格納する
(
    @chvResult VARCHAR(max) OUTPUT,
    @debug INT = 0
)
AS
    DECLARE @chvItem VARCHAR(255)

    SET @chvResult = ''

    FETCH NEXT FROM curItems
        INTO @chvItem

    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        IF @debug <> 0
            SELECT @chvItem Item

        -- リストを作成する
        SET @chvResult = @chvResult + @chvItem

        IF @debug <> 0
            SELECT @chvResult chvResult

        FETCH NEXT FROM curItems
            INTO @chvItem
    END

    RETURN


DECLARE @chvResult VARCHAR(max)
EXEC dbo.ap_InventoryProperties_Get_wNestedCursor 5, @chvResult OUTPUT, 1
SELECT @chvResult


CREATE PROCEDURE util.ap_NonSelectedDBOption_List
-- 選択されていないデータベースオプションのリストを返す
-- テスト：EXEC util.ap_NonSelectedDBOption_List 'Asset5'
(
    @chvDBName SYSNAME
)
AS
    SET NOCOUNT ON

    CREATE TABLE #setable(name NVARCHAR(35))
    CREATE TABLE #current(name NVARCHAR(35))

    -- すべてのオプションを選択する
    INSERT INTO #setable
        EXEC sp_dboption

    -- 現在有効になっているオプションを選択する
    INSERT INTO #current
        EXEC sp_dboption @dbname = @chvDBName

    -- 現在有効になっていないオプションを返す
    SELECT name non_selected
    FROM #setable
    WHERE name NOT IN(SELECT name FROM #current)

    DROP TABLE #setable
    DROP TABLE #current

    RETURN 0


CREATE PROCEDURE util.ap_LogSpacePercentUsed_Get
/*
テスト：
DECLARE @fltUsed FLOAT
EXEC util.ap_LogSpacePercentUsed_Get 'Asset5', @fltUsed OUTPUT
SELECT @fltUsed Used
*/
(
    @chvDbName SYSNAME,
    @fltPercentUsed FLOAT OUTPUT
)
AS
    SET NOCOUNT ON

    CREATE TABLE #DBLogSpace(
        dbname SYSNAME,
        LogSizeInMB FLOAT,
        LogPercentUsed FLOAT,
        Status INT
    )
    -- すべてのデータベースのログ領域情報を取得する
    INSERT INTO #DBLogSpace
        EXEC('DBCC SQLPERF(LogSpace)')

    -- 指定されたデータベースのログ使用率を取得する
    SELECT @fltPercentUsed = LogPercentUsed
    FROM #DBLogSpace
    WHERE dbname = @chvDbName

    DROP TABLE #DBLogSpace

    RETURN


CREATE PROCEDURE dbo.ap_InventoryProperties_Get_wCursor
-- 指定された備品のプロパティを含むカーソルを返す
(
    @intInventoryId INT,
    @curProperties CURSOR VARYING OUTPUT
)
AS
    SET @curProperties = CURSOR FORWARD_ONLY STATIC FOR
        SELECT Property, Value, Unit
        FROM InventoryProperty INNER JOIN Property
            ON InventoryProperty.PropertyId = Property.PropertyId
        WHERE InventoryProperty.InventoryId = @intInventoryId

    OPEN @curProperties

    RETURN


CREATE PROCEDURE dbo.ap_InventoryProperties_Get_UseNestedCursor
/*
備品のプロパティを、次のようにセミコロンで区切ったリストを返す
Property=Value Unit; Property=Value Unit; Property=Value Unit; ...
*/
(
    @intInventoryId INT,
    @chvProperties VARCHAR(max) OUTPUT,
    @debug INT = 0
)
AS
    DECLARE @intCountProperties INT,
            @intCounter INT,
            @chvProperty VARCHAR(50),
            @chvValue VARCHAR(50),
            @chvUnit VARCHAR(50),
            @insLenProperty SMALLINT,
            @insLenValue SMALLINT,
            @insLenUnit SMALLINT,
            @insLenProperties SMALLINT

    SET @chvProperties = ''

    DECLARE @CrsrVar CURSOR

    EXEC dbo.ap_InventoryProperties_Get_wCursor @intInventoryId,
                                                @CrsrVar OUTPUT

    FETCH NEXT FROM @CrsrVar
    INTO @chvProperty, @chvValue, @chvUnit

    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        SET @chvUnit = COALESCE(@chvUnit, '')

        IF @debug <> 0
            SELECT @chvProperty [Property],
                   @chvValue [Value],
                   @chvUnit [Unit]

    -- リストを作成する
        SET @chvProperties = @chvProperties
                           + @chvProperty + '='
                           + @chvValue + ' '
                           + @chvUnit + '; '

        IF @debug <> 0
            SELECT @chvProperties chvProperties

        FETCH NEXT FROM @CrsrVar
        INTO @chvProperty, @chvValue, @chvUnit
    END

    CLOSE @CrsrVar
    DEALLOCATE @CrsrVar

    RETURN

---------------------------------------------------------------------
-- 15.5 IDENTITYプロパティの使用
---------------------------------------------------------------------

CREATE PROCEDURE dbo.ap_Inventory_Insert
-- Inventoryテーブルにレコードを挿入し、IDを返す
    @intEquipmentId INT,
    @intLocationId INT,
    @inyStatusId TINYINT,
    @intLeaseId INT,
    @intLeaseScheduleId INT,
    @intOwnerId INT,
    @mnsRent SMALLMONEY,
    @mnsLease SMALLMONEY,
    @mnsCost SMALLMONEY,
    @inyAcquisitionTypeID INT,
    @intInventoryId INT OUTPUT
AS
    SET NOCOUNT ON

    INSERT INTO dbo.Inventory
    (
        EqId,
        LocationId,
        StatusId,
        LeaseId,
        LeaseScheduleId,
        OwnerId,
        Rent,
        Lease,
        Cost,
        AcquisitionTypeID
    )
    VALUES
    (
        @intEquipmentId,
        @intLocationId,
        @inyStatusId,
        @intLeaseId,
        @intLeaseScheduleId,
        @intOwnerId,
        @mnsRent,
        @mnsLease,
        @mnsCost,
        @inyAcquisitionTypeID
    )

    SELECT @intInventoryId = SCOPE_IDENTITY()

    RETURN


CREATE TABLE a
(
    a_id INT IDENTITY(1, 1),
    a_desc VARCHAR(20),
    b_desc VARCHAR(20)
)
GO

CREATE TABLE b
(
    b_id INT IDENTITY(1, 1),
    b_desc VARCHAR(20)
)
GO

CREATE TRIGGER tr_a_I
ON dbo.a
AFTER INSERT -- INSERT後の処理
AS
    IF @@ROWCOUNT = 0
        RETURN

    INSERT INTO b(b_desc)
        SELECT b_desc FROM inserted


INSERT INTO b(b_desc)
VALUES('1')

INSERT INTO a(a_desc, b_desc)
VALUES('aaa', 'bbb')

SELECT @@IDENTITY [IdentityValue]


INSERT INTO b(b_desc)
VALUES('1')

INSERT INTO a(a_desc, b_desc)
VALUES('aaa', 'bbb')

SELECT @@IDENTITY [@@IDENTITY], SCOPE_IDENTITY() [SCOPE_IDENTITY()]

---------------------------------------------------------------------
-- 15.6 GUID
---------------------------------------------------------------------

CREATE TABLE LocationGUID
(
    LocationId UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
    Location VARCHAR(50) NOT NULL,
    CompanyId INT NOT NULL,
    PrimaryContactName VARCHAR(60) NOT NULL,
    Address VARCHAR(30) NOT NULL,
    City VARCHAR(30) NOT NULL,
    ProvinceId VARCHAR(3) NULL,
    PostalCode VARCHAR(10) NOT NULL,
    Country VARCHAR(20) NOT NULL,
    Phone VARCHAR(15) NOT NULL,
    Fax VARCHAR(15) NULL
)
GO


CREATE PROCEDURE dbo.ap_Location_Insert
    @Location VARCHAR(50),
    @CompanyId INT,
    @PrimaryContactName VARCHAR(60),
    @Address VARCHAR(30),
    @City VARCHAR(30),
    @ProvinceId VARCHAR(3),
    @PostalCode VARCHAR(10),
    @Country VARCHAR(20),
    @Phone VARCHAR(15),
    @Fax VARCHAR(15),
    @LocationGUID UNIQUEIDENTIFIER OUTPUT
AS
    SET @LocationGUID = NEWID()
    INSERT INTO dbo.LocationGUID
    (
        LocationId,
        Location,
        CompanyId,
        PrimaryContactName,
        Address,
        City,
        ProvinceId,
        PostalCode,
        Country,
        Phone,
        Fax
    )
    VALUES
    (
        @LocationGUID,
        @Location,
        @CompanyId,
        @PrimaryContactName,
        @Address,
        @City,
        @ProvinceId,
        @PostalCode,
        @Country,
        @Phone,
        @Fax
    )
    RETURN @@ERROR

---------------------------------------------------------------------
-- 15.7 MIN／MAX関数を使ったWHILEループ
---------------------------------------------------------------------

DECLARE @Value INT
-- 最初の値を取得する
SELECT @Value = MIN(Value)
FROM aTable

-- ループ
WHILE @Value IS NOT NULL
BEGIN
    -- 値を表示する代わりに何か処理をする
    SELECT @Value value

    -- 次の値を取得する
    SELECT @Value = MIN(Value)
    FROM aTable
    WHERE Value > @Value
END


CREATE PROCEDURE util.ap_SpaceUsedByTables_4
-- 現在のデータベースに存在するテーブルをループによって調べる
-- 各テーブルで使用されている領域の情報を表示する
AS
    SET NOCOUNT ON
    DECLARE @TableName SYSNAME

    -- 最初のテーブル名を取得する
    SELECT @TableName = MIN(name)
    FROM sys.sysobjects
    WHERE xtype = 'U'

    WHILE @TableName IS NOT NULL
    BEGIN
         -- 使用領域の情報を表示する
         EXEC sp_spaceused @TableName

         -- 次のテーブル名を取得する
         SELECT @TableName = MIN(name)
         FROM sys.sysobjects
         WHERE xtype = 'U'
         AND name > @TableName
    END

    RETURN 0

---------------------------------------------------------------------
-- 15.8 sp_MSForEachTableおよびsp_MSForEachDbを使ったループ
---------------------------------------------------------------------

EXEC sp_MSforEachDb
@command1 = "PRINT '?'",
@command2 = "SELECT COUNT(name) FROM ?.dbo.sysobjects WHERE xtype = 'U'"


EXEC sp_MSforeachdb @command1 = "USE ? EXEC sp_spaceused"


EXEC sp_MSforeachtable @command1 = "sp_spaceused '?'"


EXEC sp_MSforeachtable
    @command1 = "PRINT '?'",
    @command2 = "SELECT COUNT(*) FROM ?"


EXEC sp_MSforeachtable
    @command1 = "EXEC sp_spaceused '?'",
    @whereand = "ORDER BY 1"

---------------------------------------------------------------------
-- 15.9 プロパティ管理
---------------------------------------------------------------------

EXEC sp_addextendedproperty N'Format', N'(999)999-9999',
                   N'SCHEMA', dbo,
                           N'TABLE', Contact,
                                    N'COLUMN', Phone


EXEC sp_updateextendedproperty @name = N'MS_Description',
    @value = N'List Eq table records.',
    @level0type = N'SCHEMA', @level0name = N'dbo',
    @level1type = N'PROCEDURE', @level1name = N'ap_Eq_List'


DECLARE @v SYSNAME
DECLARE @table SYSNAME
DECLARE @col SYSNAME

SET @table = N'Contact'
SET @v = N'persons that are in relationships in Asset5'

IF NOT EXISTS
(
    SELECT objtype, objname, name, value
    FROM fn_listextendedproperty(NULL, 'SCHEMA', 'dbo',
                                       'TABLE', 'Contact',
                                       default, default)
)
    EXECUTE sp_addextendedproperty N'MS_Description', @v,
                                   N'SCHEMA', N'dbo',
                                   N'TABLE', @table,
                                   NULL, NULL
ELSE
    EXECUTE sp_updateextendedproperty N'MS_Description', @v,
                                       N'SCHEMA', N'dbo',
                                       N'TABLE', @table,
                                       NULL, NULL

SET @table = N'Contact'
SET @v = N'surrogate identifier'
SET @col = N'ContactID'

IF NOT EXISTS
(
    SELECT objtype, objname, name, value
    FROM fn_listextendedproperty(NULL, 'SCHEMA', 'dbo',
                                       'TABLE', @table,
                                       'COLUMN', @col)
)
    EXECUTE sp_addextendedproperty N'MS_Description', @v,
                                   N'SCHEMA', N'dbo',
                                   N'TABLE', @table,
                                   N'COLUMN', @col
ELSE
    EXECUTE sp_updateextendedproperty N'MS_Description', @v,
                                      N'SCHEMA', N'dbo',
                                      N'TABLE', @table,
                                      N'COLUMN', @col

