SQL中OBJECT_ID,OBJECT_NAME,OBJECT_DEFINITION的用法
-
标签:OBJECT_ID,OBJECT_NAME,OBJECT_DEFINITION
在SQL SERVER中数据库中的对象(表,存储过程,函数,视图等)在系统表中保存都用objectid做标识的.执行下面的SQL就明白了:
- SELECT * FROM sys.objects
与对象相关最常用的三个函数:
OBJECT_ID:此方法返回数据库对象标识号。类型为int,表示该对象在系统中的编号。
OBJECT_NAME:根据对象ID得到对象名
OBJECT_DEFINITION:返回对象的源文件.
示例1:
- --新建一个存储过程
- create PROCEDURE [dbo].[usp_TestProcedure]
- AS
- BEGIN
- PRINT 'this is a procedure '
- END
- DECLARE @ObjectID INT
- DECLARE @ObjectName varchar(20)
- DECLARE @ObjectDefine varchar(max)
- SET @ObjectID = OBJECT_ID('usp_TestProcedure')
- set @ObjectName = OBJECT_NAME(@ObjectID)
- SET @ObjectDefine = OBJECT_DEFINITION(@ObjectID)
- select @ObjectID,@ObjectName,@ObjectDefine
其中@ObjectName为:usp_TestProcedure.
@ObjectDefine:
- create PROCEDURE [dbo].[usp_TestProcedure]
- AS
- BEGIN
- PRINT 'this is a procedure '
- END
示例2:
a.表不存在时新建表:
- IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cs_Threads]') AND type in (N'U'))
- BEGIN
- CREATE TABLE [dbo].[cs_Threads](
- [ThreadID] [int] IDENTITY(1,1) NOT NULL,
- [SectionID] [int] NOT NULL,
- [UserID] [int] NOT NULL,
- [PostAuthor] [nvarchar](64) NULL CONSTRAINT [DF_cs_Threads_PostAuthor] DEFAULT (''),
- [PostDate] [datetime] NOT NULL,
- [ThreadDate] [datetime] NOT NULL,
- [LastViewedDate] [datetime] NOT NULL CONSTRAINT [DF_cs_Threads_LastViewedDate] DEFAULT (getdate()),
- [StickyDate] [datetime] NOT NULL,
- [TotalViews] [int] NOT NULL CONSTRAINT [DF_cs_Threads_TotalViews] DEFAULT ((0)),
- [TotalReplies] [int] NOT NULL CONSTRAINT [DF_cs_Threads_TotalReplies] DEFAULT ((0)),
- [MostRecentPostAuthorID] [int] NOT NULL,
- [MostRecentPostAuthor] [nvarchar](64) NULL CONSTRAINT [DF_cs_Threads_MostRecentPostAuthor] DEFAULT (''),
- [MostRecentPostID] [int] NOT NULL,
- [IsLocked] [bit] NOT NULL,
- [IsSticky] [bit] NOT NULL,
- [IsApproved] [bit] NOT NULL CONSTRAINT [DF_cs_Threads_IsApproved] DEFAULT ((1)),
- [RatingSum] [int] NOT NULL CONSTRAINT [DF_cs_Threads_RatingSum] DEFAULT ((0)),
- [TotalRatings] [int] NOT NULL CONSTRAINT [DF_cs_Threads_TotalRating] DEFAULT ((0)),
- [ThreadEmoticonID] [int] NOT NULL CONSTRAINT [DF_cs_Threads_ThreadEmoticon] DEFAULT ((0)),
- [ThreadStatus] [int] NOT NULL CONSTRAINT [DF_cs_Threads_ThreadStatus] DEFAULT ((0)),
- [SettingsID] [int] NULL,
- CONSTRAINT [PK_cs_Threads] PRIMARY KEY CLUSTERED
- (
- [ThreadID] ASC
- )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
- ) ON [PRIMARY]
- END
- GO
b.存储过程不存在时,新建存储过程
- IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cs_PostCategories_Get_ByName]') AND type in (N'P', N'PC'))
- BEGIN
- EXEC dbo.sp_executesql @statement = N'
- CREATE PROCEDURE [dbo].[cs_PostCategories_Get_ByName]
- @Name nvarchar(256),
- @SectionQuerySQL ntext,
- @SettingsID int
- AS
- SET Transaction Isolation Level Read UNCOMMITTED
- CREATE TABLE #Sections
- (
- SectionID int
- )
- INSERT INTO #Sections (SectionID)
- EXEC (@SectionQuerySQL)
- SELECT
- C.CategoryID, C.SectionID, C.Name, C.IsEnabled, C.ParentID, C.Path, C.Description, C.SettingsID,
- C.TotalThreads, C.MostRecentPostDate, C.TotalSubThreads, C.MostRecentSubPostDate, C.DateCreated, C.FeaturedPostID
- FROM cs_Post_Categories C
- WHERE C.SectionID in (select SectionID from #Sections)
- and C.SettingsID = @SettingsID
- and C.[Name] = @Name
- '
- END
c.索引不存在时,新建索引
- IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[cs_Threads]') AND name = N'IX_cs_Threads')
- CREATE NONCLUSTERED INDEX [IX_cs_Threads] ON [dbo].[cs_Threads]
- (
- [SectionID] ASC,
- [ThreadID] DESC
- )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
- GO
--转载:http://blog.csdn.net/diligentcatrich/article/details/6890183