🧙 ▪️ 💃🏽 किसी मौजूदा तालिका के लिए एक क्रिएट टेबल स्क्रिप्ट कैसे जनरेट करें 🕣 👩🏼 🏹

SQL सर्वर मेटाडेटा के रूप में सभी वस्तुओं और उनके गुणों के बारे में जानकारी संग्रहीत करता है, जिसे सिस्टम दृश्यों के माध्यम से एक्सेस किया जा सकता है। इसके अलावा, सिस्टम के कुछ दृश्य दिलचस्प बारीकियों को छिपाते हैं जो आपको बेहतर तरीके से समझने की अनुमति देते हैं कि डीबीएमएस कैसे काम करता है।

सिस्टम दृश्य, साथ ही किसी अन्य स्क्रिप्ट की गई वस्तु के शरीर को देखने के लिए, फ़ंक्शन का उपयोग करें - OBJECT_DEFITITION :

PRINT OBJECT_DEFINITION(OBJECT_ID('sys.objects'))

हालाँकि, OBJECT_DEFINITION , साथ ही इसके समकक्ष sp_helptext , में एक महत्वपूर्ण दोष है - इनका उपयोग किसी टेबल ऑब्जेक्ट के लिए स्क्रिप्ट विवरण वापस करने के लिए नहीं किया जा सकता है।

 IF OBJECT_ID('dbo.Table1', 'U') IS NOT NULL DROP TABLE dbo.Table1 GO CREATE TABLE dbo.Table1 (ColumnID INT PRIMARY KEY) GO EXEC sys.sp_helptext 'dbo.Table1' SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.Table1', 'U'))

जब sp_helptext निष्पादित करते हैं तो हमें एक त्रुटि मिलती है:

Msg 15197, स्तर 16, राज्य 1, प्रक्रिया sp_helptext, लाइन 107
ऑब्जेक्ट 'dbo.Table1' के लिए कोई टेक्स्ट नहीं है।

समान शर्तों के तहत, OBJECT_DEFINITION सिस्टम फ़ंक्शन NULL को लौटाएगा।

इसके अलावा, sys.sql_modules से लाने से समस्या का समाधान नहीं होगा, क्योंकि इस प्रणाली के अंदर OBJECT_DEFINITION फ़ंक्शन के समान कॉल का उपयोग किया जाता है :

 CREATE VIEW sys.sql_modules AS SELECT object_id = o.id, definition = object_definition(o.id), ... FROM sys.sysschobjs o

यह व्यवहार बहुत दुखद है, क्योंकि कुछ परिदृश्यों के लिए तालिका का स्क्रिप्ट विवरण प्राप्त करना उपयोगी हो सकता है। ठीक है, सिस्टम के विचारों पर एक नज़र डालें और टेबल ऑब्जेक्ट्स के साथ काम करने के लिए OBJECT_DEFINITION फ़ंक्शन का एक एनालॉग बनाएं।

सबसे पहले, एक परीक्षण तालिका बनाएं ताकि स्क्रिप्ट लिखने की प्रक्रिया अधिक दृश्य हो:

 IF OBJECT_ID('dbo.WorkOut', 'U') IS NOT NULL DROP TABLE dbo.WorkOut GO CREATE TABLE dbo.WorkOut ( WorkOutID BIGINT IDENTITY(1,1) NOT NULL, TimeSheetDate AS DATEADD(DAY, -(DAY(DateOut) - 1), DateOut), DateOut DATETIME NOT NULL, EmployeeID INT NOT NULL, IsMainWorkPlace BIT NOT NULL DEFAULT 1, DepartmentUID UNIQUEIDENTIFIER NOT NULL, WorkShiftCD NVARCHAR(10) NULL, WorkHours REAL NULL, AbsenceCode VARCHAR(25) NULL, PaymentType CHAR(2) NULL, CONSTRAINT PK_WorkOut PRIMARY KEY CLUSTERED (WorkOutID) ) GO

और पहले चरण के लिए आगे बढ़ें - कॉलम और उनके गुणों की एक सूची प्राप्त करना:

सिद्धांत रूप में, आप कई सिस्टम दृश्यों में से किसी एक का उपयोग करके कॉलम की एक सूची प्राप्त कर सकते हैं। इसी समय, सबसे हल्के सिस्टम अभ्यावेदन से चयन करना महत्वपूर्ण है ताकि क्वेरी निष्पादन का समय कम से कम हो।

उनके कार्यान्वयन के लिए योजनाओं के साथ कुछ उदाहरण यहां दिए गए हैं:

 --#1 SELECT * FROM INFORMATION_SCHEMA.COLUMNS c WHERE c.TABLE_SCHEMA = 'dbo' AND c.TABLE_NAME = 'WorkOut'

 --#2 SELECT c.* FROM sys.columns c WITH(NOLOCK) JOIN sys.tables t WITH(NOLOCK) ON c.[object_id] = t.[object_id] JOIN sys.schemas s WITH(NOLOCK) ON t.[schema_id] = s.[schema_id] WHERE t.name = 'WorkOut' AND s.name = 'dbo'

 --#3 SELECT * FROM sys.columns c WITH(NOLOCK) WHERE OBJECT_NAME(c.[object_id]) = 'WorkOut' AND OBJECT_SCHEMA_NAME(c.[object_id]) = 'dbo'

 --#4 SELECT * FROM sys.columns c WITH(NOLOCK) WHERE c.[object_id] = OBJECT_ID('dbo.WorkOut', 'U')

यह प्रस्तुत निष्पादन योजनाओं से देखा जा सकता है कि # 1 और # 2 विकल्पों में अत्यधिक संख्या में कनेक्शन होते हैं जो क्वेरी निष्पादन समय को बढ़ाते हैं, जबकि # 3 दृष्टिकोण एक पूर्ण सूचकांक स्कैन की ओर जाता है, जो इसे सभी का सबसे कम कुशल बनाता है।

प्रदर्शन के मामले में, # 4 विकल्प मेरे लिए सबसे आकर्षक बना हुआ है।

हालाँकि, sys.columns (साथ ही INFORMATION_SCHEMA.COLUMNS ) में निहित डेटा पूरी तरह से टेबल संरचना का वर्णन करने के लिए पर्याप्त नहीं है। यह अन्य सिस्टम दृश्यों से जुड़ता है:

 SELECT c.name , [type_name] = tp.name , type_schema_name = s.name , c.max_length , c.[precision] , c.scale , c.collation_name , c.is_nullable , c.is_identity , ic.seed_value , ic.increment_value , computed_definition = cc.[definition] , default_definition = dc.[definition] FROM sys.columns c WITH(NOLOCK) JOIN sys.types tp WITH(NOLOCK) ON c.user_type_id = tp.user_type_id JOIN sys.schemas s WITH(NOLOCK) ON tp.[schema_id] = s.[schema_id] LEFT JOIN sys.computed_columns cc WITH(NOLOCK) ON c.[object_id] = cc.[object_id] AND c.column_id = cc.column_id LEFT JOIN sys.identity_columns ic WITH(NOLOCK) ON c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id LEFT JOIN sys.default_constraints dc WITH(NOLOCK) ON dc.[object_id] = c.default_object_id WHERE c.[object_id] = OBJECT_ID('dbo.WorkOut', 'U')

तदनुसार, कार्यान्वयन योजना पहले की तरह हंसमुख नहीं दिखेगी। यदि आप ध्यान देते हैं, तो हम सामान्यतः कॉलम की सूची को तीन बार घटाते हैं:

Sys.default_constraints के अंदर देखें :

 ALTER VIEW sys.default_constraints AS SELECT name, object_id, parent_object_id, ... object_definition(object_id) AS definition, is_system_named FROM sys.objects$ WHERE type = 'D ' AND parent_object_id > 0

सिस्टम दृश्य के अंदर, आप OBJECT_DEFINITION को कॉल क्रमशः देख सकते हैं, डिफ़ॉल्ट स्थिरांक का विवरण प्राप्त करने के लिए, हमें कनेक्शन बनाने की आवश्यकता नहीं है।

Sys.computed_columns उसी OBJECT_DEFINITION का उपयोग करता है:

 ALTER VIEW sys.computed_columns AS SELECT object_id = id, name = name, column_id = colid, system_type_id = xtype, user_type_id = utype, ... definition = object_definition(id, colid), ... FROM sys.syscolpars WHERE number = 0 AND (status & 16) = 16 -- CPM_COMPUTED AND has_access('CO', id) = 1

यह पता चला है कि हम पहले ही दो यौगिकों से छुटकारा पा चुके हैं। Sys.identity_columns के साथ , स्थिति अधिक दिलचस्प है:

 ALTER VIEW sys.identity_columns AS SELECT object_id = id, name = name, column_id = colid, system_type_id = xtype, user_type_id = utype, ... seed_value = IdentityProperty(id, 'SeedValue'), increment_value = IdentityProperty(id, 'IncrementValue'), last_value = IdentityProperty(id, 'LastValue'), ... FROM sys.syscolpars WHERE number = 0 -- SOC_COLUMN AND (status & 4) = 4 -- CPM_IDENTCOL AND has_access('CO', id) = 1

IDENTITYPROPERTY undocumented फ़ंक्शन का उपयोग IDENTITY गुणों के बारे में जानकारी प्राप्त करने के लिए किया जाता है। चेक के परिणामस्वरूप, इसका अपरिवर्तित व्यवहार SQL सर्वर के 2005 संस्करण और उच्चतर पर स्थापित किया गया था।

इन कार्यों को सीधे कॉल करने के परिणामस्वरूप, स्तंभों की सूची प्राप्त करने का अनुरोध सरल रूप से किया जाता है:

 SELECT c.name , [type_name] = tp.name , type_schema_name = s.name , c.max_length , c.[precision] , c.scale , c.collation_name , c.is_nullable , c.is_identity , seed_value = CASE WHEN c.is_identity = 1 THEN IDENTITYPROPERTY(c.[object_id], 'SeedValue') END , increment_value = CASE WHEN c.is_identity = 1 THEN IDENTITYPROPERTY(c.[object_id], 'IncrementValue') END , computed_definition = OBJECT_DEFINITION(c.[object_id], c.column_id) , default_definition = OBJECT_DEFINITION(c.default_object_id) FROM sys.columns c WITH(NOLOCK) JOIN sys.types tp WITH(NOLOCK) ON c.user_type_id = tp.user_type_id JOIN sys.schemas s WITH(NOLOCK) ON tp.[schema_id] = s.[schema_id] WHERE c.[object_id] = OBJECT_ID('dbo.WorkOut', 'U')

और कार्यान्वयन योजना और अधिक वफादार बन जाएगी:

निष्कर्ष में, sys.schemas से कनेक्ट करने के बजाय , आप सिस्टम फ़ंक्शन SCHEMA_NAME को कॉल कर सकते हैं, जो कनेक्शन की तुलना में बहुत तेज़ काम करता है। यह कथन सत्य है, बशर्ते कि योजनाओं की संख्या उपयोगकर्ता वस्तुओं की संख्या से अधिक न हो। और चूंकि ऐसी स्थिति की संभावना नहीं है, इसलिए इसे उपेक्षित किया जा सकता है।

अगला, हमें प्राथमिक कुंजी में शामिल स्तंभों की एक सूची मिलती है। सबसे स्पष्ट विकल्प sys.key_constraints को कॉल करना है :

 SELECT pk_name = kc.name , column_name = c.name , ic.is_descending_key FROM sys.key_constraints kc WITH(NOLOCK) JOIN sys.index_columns ic WITH(NOLOCK) ON kc.parent_object_id = ic.object_id AND ic.index_id = kc.unique_index_id JOIN sys.columns c WITH(NOLOCK) ON ic.[object_id] = c.[object_id] AND ic.column_id = c.column_id WHERE kc.parent_object_id = OBJECT_ID('dbo.WorkOut', 'U') AND kc.[type] = 'PK'

यदि आप सिद्धांत को याद करते हैं, तो प्राथमिक कुंजी एक क्लस्टर इंडेक्स और एक अद्वितीय बाधा है।

मेटाडेटा स्तर पर, SQL सर्वर सभी अनुक्रमित अनुक्रमित के लिए index_id को 1 पर सेट करता है, इसलिए आप is_primary_key = 1 द्वारा फ़िल्टर करके sys.indexes से चयन कर सकते हैं।

इसके अतिरिक्त, sys.columns के साथ कनेक्शन से छुटकारा पाने के लिए, आप सिस्टम फ़ंक्शन COL_NAME का उपयोग कर सकते हैं:

 SELECT pk_name = i.name , column_name = COL_NAME(ic.[object_id], ic.column_id) , ic.is_descending_key FROM sys.indexes i WITH(NOLOCK) JOIN sys.index_columns ic WITH(NOLOCK) ON i.[object_id] = ic.[object_id] AND i.index_id = ic.index_id WHERE i.is_primary_key = 1 AND i.[object_id] = object_id('dbo.WorkOut', 'U')

अब हम प्राप्त नमूनों को एक में जोड़ देंगे और निम्नलिखित क्वेरी प्राप्त करेंगे:

 DECLARE @object_name SYSNAME , @object_id INT , @SQL NVARCHAR(MAX) SELECT @object_name = '[' + OBJECT_SCHEMA_NAME(o.[object_id]) + '].[' + OBJECT_NAME([object_id]) + ']' , @object_id = [object_id] FROM (SELECT [object_id] = OBJECT_ID('dbo.WorkOut', 'U')) o SELECT @SQL = 'CREATE TABLE ' + @object_name + CHAR(13) + '(' + CHAR(13) + STUFF(( SELECT CHAR(13) + ' , [' + c.name + '] ' + CASE WHEN c.is_computed = 1 THEN 'AS ' + OBJECT_DEFINITION(c.[object_id], c.column_id) ELSE CASE WHEN c.system_type_id != c.user_type_id THEN '[' + SCHEMA_NAME(tp.[schema_id]) + '].[' + tp.name + ']' ELSE '[' + UPPER(tp.name) + ']' END + CASE WHEN tp.name IN ('varchar', 'char', 'varbinary', 'binary') THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS VARCHAR(5)) END + ')' WHEN tp.name IN ('nvarchar', 'nchar') THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length / 2 AS VARCHAR(5)) END + ')' WHEN tp.name IN ('datetime2', 'time2', 'datetimeoffset') THEN '(' + CAST(c.scale AS VARCHAR(5)) + ')' WHEN tp.name = 'decimal' THEN '(' + CAST(c.[precision] AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')' ELSE '' END + CASE WHEN c.collation_name IS NOT NULL AND c.system_type_id = c.user_type_id THEN ' COLLATE ' + c.collation_name ELSE '' END + CASE WHEN c.is_nullable = 1 THEN ' NULL' ELSE ' NOT NULL' END + CASE WHEN c.default_object_id != 0 THEN ' CONSTRAINT [' + OBJECT_NAME(c.default_object_id) + ']' + ' DEFAULT ' + OBJECT_DEFINITION(c.default_object_id) ELSE '' END + CASE WHEN cc.[object_id] IS NOT NULL THEN ' CONSTRAINT [' + cc.name + '] CHECK ' + cc.[definition] ELSE '' END + CASE WHEN c.is_identity = 1 THEN ' IDENTITY(' + CAST(IDENTITYPROPERTY(c.[object_id], 'SeedValue') AS VARCHAR(5)) + ',' + CAST(IDENTITYPROPERTY(c.[object_id], 'IncrementValue') AS VARCHAR(5)) + ')' ELSE '' END END FROM sys.columns c WITH(NOLOCK) JOIN sys.types tp WITH(NOLOCK) ON c.user_type_id = tp.user_type_id LEFT JOIN sys.check_constraints cc WITH(NOLOCK) ON c.[object_id] = cc.parent_object_id AND cc.parent_column_id = c.column_id WHERE c.[object_id] = @object_id ORDER BY c.column_id FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 7, ' ') + ISNULL((SELECT ' , CONSTRAINT [' + i.name + '] PRIMARY KEY ' + CASE WHEN i.index_id = 1 THEN 'CLUSTERED' ELSE 'NONCLUSTERED' END +' (' + ( SELECT STUFF(CAST(( SELECT ', [' + COL_NAME(ic.[object_id], ic.column_id) + ']' + CASE WHEN ic.is_descending_key = 1 THEN ' DESC' ELSE '' END FROM sys.index_columns ic WITH(NOLOCK) WHERE i.[object_id] = ic.[object_id] AND i.index_id = ic.index_id FOR XML PATH(N''), TYPE) AS NVARCHAR(MAX)), 1, 2, '')) + ')' FROM sys.indexes i WITH(NOLOCK) WHERE i.[object_id] = @object_id AND i.is_primary_key = 1), '') + CHAR(13) + ');' PRINT @SQL

जिसे निष्पादित करते समय, हमारी तालिका के लिए निम्न स्क्रिप्ट उत्पन्न होगी:

 CREATE TABLE [dbo].[WorkOut] ( [WorkOutID] [BIGINT] NOT NULL IDENTITY(1,1) , [TimeSheetDate] AS (dateadd(day, -(datepart(day,[DateOut])-(1)),[DateOut])) , [DateOut] [DATETIME] NOT NULL , [EmployeeID] [INT] NOT NULL , [IsMainWorkPlace] [BIT] NOT NULL DEFAULT ((1)) , [DepartmentUID] [UNIQUEIDENTIFIER] NOT NULL , [WorkShiftCD] [NVARCHAR](10) COLLATE Cyrillic_General_CI_AI NULL , [WorkHours] [REAL] NULL , [AbsenceCode] [VARCHAR](25) COLLATE Cyrillic_General_CI_AI NULL , [PaymentType] [CHAR](2) COLLATE Cyrillic_General_CI_AI NULL , CONSTRAINT [PK_WorkOut] PRIMARY KEY CLUSTERED ([WorkOutID]) );

पुनश्च : तालिका के स्क्रिप्ट विवरण की पीढ़ी निश्चित रूप से स्तंभों की सूची और प्राथमिक कुंजी तक सीमित नहीं है। यदि यह दिलचस्प है, तो आप इस विषय को जारी रख सकते हैं और अनुक्रमित, विदेशी कुंजी और अन्य संबंधित निर्माणों की पीढ़ी दिखा सकते हैं।

किसी मौजूदा तालिका के लिए एक क्रिएट टेबल स्क्रिप्ट कैसे जनरेट करें

More articles: