MS-SQL Table, Type, SP 검색.

-- 프로시져 검색

SELECT * FROM information_schema.PARAMETERS

or

SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Name]') AND type in (N'P'))

 

EX)

IF EXISTS(SELECT 1 FROM information_schema.PARAMETERS WHERE SPECIFIC_NAME like 'sp_Item_List')

BEGIN
     DROP PROCEDURE [dbo].[sp_Item_List]
END
GO

 

CREATE PROC [dbo].[usp_Item_List]
     @UserIndex int,
     @ItemIndex int
AS
BEGIN
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

BEGIN TRY
     IF(0 >= @ItemIndex)
          RETURN -1;

 

     IF( EXISTS(SELECT 1 FROM ItemList WHERE UserIndex = @UserIndex) )
     BEGIN
          RETURN -3;
     END

 

     BEGIN TRAN   -- Update & Insert
          INSERT INTO ItemList(UserIndex, ItemIndex, ResultDate) VALUES( @UserIndex, @ItemIndex, GETDATE() );
          IF(0 < @@TRANCOUNT)
               COMMIT TRAN;

     RETURN 0;
END TRY
BEGIN CATCH
     IF(0 < @@TRANCOUNT)
          ROLLBACK TRAN;
     RETURN 100;   -- ERROR
END CATCH

SET NOCOUNT OFF
END

GO

 

 

 

-- 유형 - 사용자 정의 테이블 검색

SELECT * FROM information_schema.DOMAINS

 

EX)

IF EXISTS(SELECT 1 FROM information_schema.DOMAINS WHERE DOMAIN_NAME like 'Goods')
BEGIN
     DROP TYPE [dbo].[Goods]
END
GO

 

CREATE TYPE [dbo].[Goods] AS TABLE(
     [RewardIndex] [int] NULL, 
     [RewardCount] [int] NULL
)
GO

 

 

 

-- 테이블 검색

SELECT * FROM information_schema.tables

or

SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Name]') AND type in (N'U'))

 

EX)

IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'ItemList')
BEGIN
     DROP TABLE [dbo].[ItemList]
END
GO

 

CREATE TABLE [dbo].[ItemList](
     [UserIndex] [int] NOT NULL,
     [ItemIndex] [int] NOT NULL,
     [UpdateDate] [DateTime] NOT NULL
CONSTRAINT [PK__ItemList] PRIMARY KEY CLUSTERED
(
     [UserIndex] ASC,

     [ItemIndex] 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