---------------------------------------------------------------------
-- SQL Server 2005XgAhvV[WvO~O
-- 15 XgAhvV[W̍xȃvO~O
---------------------------------------------------------------------

--------------------------------------------------------------------
-- 15.1 NG̓I쐬
---------------------------------------------------------------------

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 -- ŃG[


CREATE PROCEDURE dbo.ap_QBF_Contact_List
-- w肳ꂽp[^ɊÂēIɃNG𐶐
-- eXgFEXEC 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

        -- A擾
        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
-- e[u̓eČA
-- INSERTXe[gg쐬
-- ̃Xe[ggł́Aɒ͏Ȃ
@table SYSNAME = 'Inventory',
@debug INT = 0
-- fobOFEXEC 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

        -- 񃊃Xg擾
        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]

    -- 擪̃J}폜
    SET @chvColList = SUBSTRING(@chvColList, 2, LEN(@chvColList))
    SET @chvVal = SUBSTRING(@chvVal, 6, LEN(@chvVal))

    -- Ŝ܂Ƃ߂ăR}h쐬
    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
-- IňSɕp[^g悤ɂ
(
    @chvInput NVARCHAR(max),
    @bitLikeSafe BIT = 0  -- LIKEŎgꍇ1ݒ肷
)
RETURNS NVARCHAR(max)
AS
BEGIN
    DECLARE @chvOutput NVARCHAR(max)
    SET @chvOutput = REPLACE(@chvInput, CHAR(39), CHAR(39) + CHAR(39))
    IF @bitLikeSafe = 1
    BEGIN
        -- pi[]j̕ϊ
        SET @chvOutput = REPLACE(@chvOutput, '[', '[[]')
        -- ChJ[h̕ϊ
        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 TIMESTAMPlgIveB~XeBbNbN
---------------------------------------------------------------------

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
-- Storese[ũR[hXV
-- XVς݃R[h̏㏑h~
(
    @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 teLXg
---------------------------------------------------------------------

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 XgAhvV[W̃lXg
---------------------------------------------------------------------

CREATE PROCEDURE dbo.ap_InventoryProperties_Get_TempTblOuter
/*
ĩvpeBÂ悤ɃZ~Rŋ؂XgԂ
Property=Value Unit; Property=Value Unit; Property=Value Unit; ...

eXgF
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)
    )

    -- iƊ֘AtĂvpeB肷
    INSERT INTO #List(Item)
        SELECT Property + '=' + Value + ' ' + COALESCE(Unit, '') + '; '
        FROM InventoryProperty INNER JOIN Property
        ON InventoryProperty.PropertyId = Property.PropertyId
        WHERE InventoryProperty.InventoryId = @intInventoryId

    -- R[h1VARCHARϐɕϊXgAhvV[WĂяo
    EXEC util.ap_TempTbl2Varchar @chvProperties OUTPUT

    -- ʂ\
    SELECT @chvProperties Properties

    DROP TABLE #List

    RETURN 0


CREATE PROCEDURE util.ap_TempTbl2Varchar
-- Listꎞe[ȕ1VARCHAR^̕ϐɊi[
@chvResult VARCHAR(max) OUTPUT
AS
    SET NOCOUNT ON

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

    -- [vݒ肷
    SELECT @intCountItems = COUNT(*),
           @intCounter = 1,
           @chvResult = ''
    FROM #List

    -- vpeB̃XgŜ[v
    WHILE @intCounter <= @intCountItems
    BEGIN
        -- vpeB1擾
        SELECT @chvItem = Item
        FROM #List
        WHERE Id = @intCounter

        -- Xg쐬
        SET @chvResult = @chvResult + @chvItem

        -- Oɖ߂Ď̃vpeB擾
        SET @intCounter = @intCounter + 1
    END

    RETURN 0


EXEC dbo.ap_InventoryProperties_Get_TempTblOuter 5


CREATE PROCEDURE dbo.ap_InventoryProperties_Get_wNestedCursor
/*
ĩvpeBÂ悤ɃZ~Rŋ؂XgԂ
Property=Value Unit; Property=Value Unit; Property=Value Unit; ...

eXgF
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
-- ÕXgAhvV[WŊJnꂽJ[\̏
-- R[h1VARCHAR^ϐɊi[
(
    @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

        -- Xg쐬
        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
-- IĂȂf[^x[XIvṼXgԂ
-- eXgFEXEC util.ap_NonSelectedDBOption_List 'Asset5'
(
    @chvDBName SYSNAME
)
AS
    SET NOCOUNT ON

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

    -- ׂẴIvVI
    INSERT INTO #setable
        EXEC sp_dboption

    -- ݗLɂȂĂIvVI
    INSERT INTO #current
        EXEC sp_dboption @dbname = @chvDBName

    -- ݗLɂȂĂȂIvVԂ
    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
/*
eXgF
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
    )
    -- ׂẴf[^x[X̃Ö擾
    INSERT INTO #DBLogSpace
        EXEC('DBCC SQLPERF(LogSpace)')

    -- w肳ꂽf[^x[X̃Ogp擾
    SELECT @fltPercentUsed = LogPercentUsed
    FROM #DBLogSpace
    WHERE dbname = @chvDbName

    DROP TABLE #DBLogSpace

    RETURN


CREATE PROCEDURE dbo.ap_InventoryProperties_Get_wCursor
-- w肳ꂽĩvpeB܂ރJ[\Ԃ
(
    @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
/*
ĩvpeBÂ悤ɃZ~Rŋ؂XgԂ
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]

    -- Xg쐬
        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 IDENTITYvpeB̎gp
---------------------------------------------------------------------

CREATE PROCEDURE dbo.ap_Inventory_Insert
-- Inventorye[uɃR[h}AIDԂ
    @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֐gWHILE[v
---------------------------------------------------------------------

DECLARE @Value INT
-- ŏ̒l擾
SELECT @Value = MIN(Value)
FROM aTable

-- [v
WHILE @Value IS NOT NULL
BEGIN
    -- l\ɉ
    SELECT @Value value

    -- ̒l擾
    SELECT @Value = MIN(Value)
    FROM aTable
    WHERE Value > @Value
END


CREATE PROCEDURE util.ap_SpaceUsedByTables_4
-- ݂̃f[^x[Xɑ݂e[u[vɂĒׂ
-- ee[uŎgpĂ̈̏\
AS
    SET NOCOUNT ON
    DECLARE @TableName SYSNAME

    -- ŏ̃e[u擾
    SELECT @TableName = MIN(name)
    FROM sys.sysobjects
    WHERE xtype = 'U'

    WHILE @TableName IS NOT NULL
    BEGIN
         -- gp̈̏\
         EXEC sp_spaceused @TableName

         -- ̃e[u擾
         SELECT @TableName = MIN(name)
         FROM sys.sysobjects
         WHERE xtype = 'U'
         AND name > @TableName
    END

    RETURN 0

---------------------------------------------------------------------
-- 15.8 sp_MSForEachTablesp_MSForEachDbg[v
---------------------------------------------------------------------

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 vpeBǗ
---------------------------------------------------------------------

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

