mssql 返回表的建立語句,mssql返回建立語句
if OBJECT_ID('sp_create_table_sql','P') is not nulldrop proc sp_create_table_sqlgocreate proc sp_create_table_sql ( @tablename varchar(255) ) as begin -- exec sp_create_table_sql 'Ad_AdGroup' -- 0. 弘恩 -- 1. 不支援非主鍵類的索引 -- 2. 不支援主分鍵的非預設排序 -- 3. 不支援DEFAULT -- 4. 不支援計算資料行 -- 5. 待完整 declare @sql_create varchar(max) = '';declare @sql_column varchar(max);declare @sql_primary varchar(max);with cte as (select QUOTENAME( c.name )+' '+TYPE_NAME(c.system_type_id)+' '+case when TYPE_NAME( c.system_type_id) in ('char','varchar','decimal') then ' ( ' else '' end +case when TYPE_NAME( c.system_type_id) in ('char','varchar','nvarchar' ) then cast(max_length as varchar) else '' end+case when TYPE_NAME( c.system_type_id) in ('decimal' ) then cast(c.precision as varchar)+','+cast(c.scale AS varchar) else '' end+ case when TYPE_NAME( c.system_type_id) in ('char','varchar','decimal') then ' ) ' else '' end +case when c.is_nullable = 1 then ' null ' else ' not null ' end +case when c.is_identity = 0 then ' ' else ' identity ' end sqlstr , column_id from sys.objects as o join sys.columns as c on o.object_id = c.object_id where o.name = @tablename and o.type = 'U' )select @sql_column = stuff((select ',' + sqlstr + CHAR(10)from cte order by column_id asc for xml path('') ),1,1,'') ;select @sql_primary = stuff(( select ',' + c.name from sys.index_columns as i join sys.indexes as ix on i.object_id = ix.object_id and i.index_id = ix.index_id join sys.columns as c on i.object_id = c.object_id and i.column_id = c.column_id where OBJECT_NAME(i.object_id) = @tablename and ix.is_primary_key = 1 order by i.key_ordinal for xml path('') ),1,1,'') set @sql_create = ' create table ' + @tablename + '( ' + @sql_column + case when len(@sql_primary) >= 1 then (', primary key ( ' + @sql_primary + ')') else '' end + ' ) ' print ' -- @sql_create -- 'print @sql_createend
mssql 匯出建表語句,註:用代碼匯出
首先,這個預存程序的功能是:
輸入一個表名稱,執行這個預存程序,返回這個表裡的每條記錄的一個insert 語句。
(例如表A(a,b,c)有三條記錄(1,2,3;4,5,6;7,8,9),那麼執行這個預存程序後,返回結果為:
INSERT INTO A VALUES('1','2','3')
INSERT INTO A VALUES('4','5','6')
INSERT INTO A VALUES('7','8','9')
-----------------------------------------------------------
大概解釋一下這個過程:
ALTER proc --修改預存程序
DECLARE xCursor CURSOR FOR --定義遊標
OPEN xCursor
FETCH xCursor into @F1,@F2 --開啟遊標,並給變數賦值,迴圈開始
WHILE @@FETCH_STATUS = 0 --通過全域變數 @@FETCH_STATUS 的值來判斷當前遊標是否有效(=0代表遊標取值成功)
CLOSE xCursor --關閉遊標
exec (@sql) --執行動態sql語句,@sql就是通過遊標拼裝的sql語句。
希望可以協助到你
MSSQL資料庫庫存在一個表,裡面有欄位也有資料,怎看建立表的語句
右鍵點表名