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?? ?
總結
以上是生活随笔為你收集整理的SQL中OBJECT_ID，OBJECT_NAME，OBJECT_DEFINITION的用法的全部內容，希望文章能夠幫你解決所遇到的問題。
如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。
