|
|
如何使用SQL(选择/存储过程/等)SQL Server生成所有表的数据库DDL(带外键、索引等。)脚本？除了数据，我需要所有的内容。
- [6 O6 Y, Q: D D. \, \3 i1 z+ H我 无法 使用Sql Server Management Studio，因为我想在那里Linux上运行的node.js在脚本中使用！
3 c# R8 ?# K$ Z# @" N1 b - S! u0 y, v2 Y/ a- T1 {' l
解决方案:
5 p% R, s) d# i6 a/ H 表格 可以按以下方式工作)! t5 d9 k# j4 @0 [9 n* [. b
DECLARE @C VARCHAR(200)DECLARE C CURSOR FORSELECT 'EXEC [DBO].[SP_GETDDL] ''' NAME '''''''FROM SYS.TABLESOPEN CFETCH NEXT FROM C INTO @CWHILE @@FETCH_STATUS=0BEGINEXEC (@C)FETCH NEXT FROM C INTO @CENDCLOSE CDEALLOCATE C程序如下：4 f1 u' v5 Z' p+ f0 y2 Y8 a- M4 t* }
CREATE PROCEDURE [dbo].[Sp_getddl] @TBL VARCHAR(255) AS BEGIN SET nocount ON DECLARE @TBLNAME VARCHAR(200) @SCHEMANAME VARCHAR(255) @STRINGLEN INT, @TABLE_ID INT, @FINALSQL VARCHAR(max), @CONSTRAINTSQLS VARCHAR(max), @CHECKCONSTSQLS VARCHAR(max), @RULESCONSTSQLS VARCHAR(max), @FKSQLS VARCHAR(max), @TRIGGERSTATEMENT VARCHAR(max), @EXTENDEDPROPERTIES VARCHAR(max), @INDEXSQLS VARCHAR(max), @vbCrLf CHAR(2) SET @vbCrLf = Char(13) Char(10) SELECT @SCHEMANAME = Isnull(Parsename(@TBL,2),'dbo @TBLNAME = Parsename(@TBL,1) SELECT @TABLE_ID = [object_id] FROM sys.objects WHERE [type] = 'U AND [name] 'dtproperties AND [name] = @TBLNAME AND [schema_id] = Schema_id(@SCHEMANAME); IF Isnull(@TABLE_ID,0) = BEGIN SET @FINALSQL = 'Table object @SCHEMANAME ']. Upper(@TBLNAME) does not exist in Database Db_name() SELECT @FINALSQL; RETURN END SELECT @FINALSQL = 'CREATE TABLE @SCHEMANAME ']. Upper(@TBLNAME) SELECT @TABLE_ID = Object_id(@TBLNAME) SELECT @STRINGLEN = Max(Len(sys.columns.[name])) FROM sys.objects INNER JOIN sys.columns ON sys.objects.[object_id] = sys.columns.[object_id] AND sys.objects.[object_id] = @TABLE_ID; SELECT @FINALSQL = @FINALSQL CASE WHEN sys.columns.[is_computed] = THEN @vbCrLf Upper sys.columns.[name]) Space(@STRINGLEN - Len(sys.columns.[name])) 'AS Isnull(CALC.definition,'') ELSE @vbCrLf [ Upper(sys.columns.[name]) Space @STRINGLEN - Len(sys.columns.[name])) Upper(Type_name(sys.columns.[user_type_id])) CASE WHEN Type_name(sys.columns.[user_type_id]) IN decimal', numeric') THEN CONVERT VARCHAR,sys.columns.[precision]) CONVERT VARCHAR, sys.columns.[scale]) Space(6 - Len CONVERT VARCHAR, sys.columns.[precision]) ',CONVERT(VARCHAR,sys.columns.[scale]))) Space( 2) -- SPACE(16 - LEN(TYPE_NAME(sys.columns.[user_type_id]))) CASE WHEN Columnproperty ( @TABLE_ID, sys.columns.[name], IsIdentity' ) = 0 THEN ELSE ' IDENTITY( CONVERT(VARCHAR,Isnull(Ident_seed(@TBLNAME),1) CONVERT(VARCHAR,Isnull(Ident_incr(@TBLNAME),1) END CASE WHEN sys.columns.[is_nullable] = 0 THEN ' NOT NULL ELSE NULL' END WHEN Type_name(sys.columns.[user_type_id]) IN float', real ) THEN CASE WHEN sys.columns.[precision] = 53 THEN Space(11 - Len(CONVERT(VARCHAR,sys.columns.[precision]))) Space(7)
# J5 o+ l: t) R6 a; F Space(16 - / `3 ]/ p" s: P& E7 k
Len(Type_name(sys.columns.[user_type_id])))
6 {! f* i2 ^% B, H6 w; x CASE
) ]8 [* Y$ y" w WHEN ( z( u* `+ v1 W
sys.columns.[is_nullable] = 0 THEN
Q1 r" Z5 c9 B NOT NULL' ELSE NULL' END ELSE ( d6 |- r5 w" K v' M5 q) c1 P( }) X
CONVERT( 0 X) W* Q! o6 R7 I6 ~+ O. [
VARCHAR,
9 v. ^* Z$ q9 E7 X4 F& a sys.columns.[precision]) Space(6 - Len(
% M( \, ]( c) @" Q/ T6 p CONVERT(
8 _- _# V( o3 D- n VARCHAR, sys.columns.[precision]))) Space(7)
: M: q/ Q; }4 u4 G Space 9 r5 d/ M+ v# f
9 c" H+ K( j5 s' n3 e' l8 }7 s; i - Len(Type_name(sys.columns.[user_type_id]))) : l% V) U0 F- k1 o* w
CASE % L: C' r7 G: d3 e. d
WHEN
+ r, [& p$ g- m3 P sys.columns.[is_nullable] = 0 THEN ' NOT NULL'
1 i9 k* {; b" J) r+ J* i ELSE
3 }* J; Q/ f1 T. n: [' I NULL' END
" A6 v6 U) }0 Y* s; k- G END WHEN Type_name(sys.columns.[user_type_id])
1 s3 q( @& v5 v- U9 _' [: t, H IN " h# Q: i9 s. x; S$ P
+ ^5 D3 a. E% O$ ^# [ D( p" L: H% z char', + X( c. f( A& C! Q' y0 }3 h) P
varchar') THEN CASE WHEN
1 P3 |3 o8 [$ d sys.columns.[max_length] & L; k7 X% M8 T
= . T3 D; s- G ^* ?3 Q
-1 THEN
: ^2 m6 E/ n# x" O% _( ? (max)' Space(6 -
; f: x# l9 r: t3 i; r" O4 J Len(CONVERT(VARCHAR, sys.columns.[max_length])))
6 p2 [* u E5 P) ` Space(7) 9 k0 A l( @9 H0 ^& H3 x7 Z
Space(16 - 4 L0 v/ @! z, L1 C6 k
Len(Type_name(sys.columns.[user_type_id]))) ) o5 `* _! S3 e$ ]
CASE 9 V" L! L9 ^- A9 X
WHEN
; e/ ?6 Q* V' m x0 d# y. W/ B sys.columns.collation_name IS NULL THEN '' 1 m3 A5 v; T+ `. e4 f/ b
ELSE ' COLLATE ' sys.columns.collation_name
- C( ^/ U, a/ d5 @- j END - U9 e6 \, e* t! |. N) ~5 X7 x
CASE WHEN
6 l" t2 _6 h# x, r) f; v sys.columns.[is_nullable] = 0 THEN ' NOT NULL' 9 L7 w9 R7 c9 z* @4 @- p
ELSE
! g L( j" `3 |! N7 e D4 O4 @ NULL' END ELSE
+ h$ y4 S+ l5 Y1 Y; l+ {( J7 R CONVERT(VARCHAR, sys.columns.[max_length]) . h' l) x1 I O/ {2 I5 t# [' C3 I
$ N4 j0 q t; @. f
Space(6 - " B7 k% e0 K5 E* j$ \2 }
Len(CONVERT(VARCHAR, sys.columns.[max_length]))) 5 ~/ s7 @5 B& n
Space(7) 9 R. E# ^. H2 A9 T0 [! ]
Space(16 -
# B& }" C9 A$ M! H6 y Len(Type_name(sys.columns.[user_type_id]))) 6 x8 Y+ a# C" p7 R e
CASE " l# a' H) i. u1 D3 F" I m
WHEN / _8 }. K, P2 ]! n7 p
sys.columns.collation_name IS NULL THEN ''
+ f. Q! F5 f9 `! _/ |! E* l, \ ELSE ' COLLATE ' sys.columns.collation_name
% m0 U8 ]9 W% L6 L3 c END ' r/ m+ i' i. G+ D& d( T* w
CASE WHEN
7 j- w, L; V$ s0 y/ v% y sys.columns.[is_nullable] = 0 THEN ' NOT NULL' ) m' R3 D7 @& J6 m
ELSE ) D, y3 d9 ~! ~5 t2 s
NULL' END END WHEN 7 L0 |2 ]- D' c
Type_name(sys.columns.[user_type_id]) IN ( ; T" a" Z2 g- L" ^: e. J
nchar',
p+ g# E" ]& t& C' p nvarchar' ( e1 R7 r0 q# d9 [) c
THEN CASE WHEN
; e. o7 e( H* h" {* [2 S/ } sys.columns.[max_length] = -1 THEN '(max)'
1 ?# i4 w0 ]6 _0 g1 H/ [' _! @ Space
7 v, L+ i& e" y3 \1 Z5 e# L -
- t U! |$ R0 vLen(CONVERT(VARCHAR, (sys.columns.[max_length]))))
, W* r2 y7 |1 S5 f' E w' BSpace(7)
8 X0 Y& G1 q" V+ q2 p* }, c Space(16 - Len(Type_name(
6 X3 F# B; J# } f" K( B |sys.columns.[user_type_id]))) CASE WHEN
7 C/ @8 t E9 k4 \) Tsys.columns.collation_name IS NULL THEN '' ELSE $ f( z4 u3 L" Z
' COLLATE '
6 y0 N) `6 K0 G; csys.columns.collation_name END CASE WHEN ; B' @, V$ w2 ~: g6 K) U2 q
sys.columns.[is_nullable] = 0 THEN
+ p2 A7 A+ I1 {% g) j' NOT NULL' ELSE NULL' END ELSE
3 k& n- s% P/ a; kCONVERT( ; d! q5 V. s9 b3 W8 p
VARCHAR, 8 `& u# T' `* M$ V5 R) ^$ n% z
(sys.columns.[max_length])) Space(6 - Len
9 p! E- i' t$ W. p e( ( D6 F/ K6 ]. V+ K' J/ p& \
CONVERT( ' A( ?5 @2 H9 t+ e
VARCHAR, (sys.columns.[max_length])))) Space(7)
7 t, E1 n8 h' @ ^% L# ESpace(16 + g7 W* T& |$ V+ v; @! _
- Len(Type_name(sys.columns.[user_type_id]))) 0 I# g( F$ W9 q E
CASE $ U: u- o: Z& Q
WHEN
* [! i3 [6 o5 c0 H' zsys.columns.collation_name IS NOT NULL THEN '' ' m' V& O/ {2 W5 x8 W& y' z9 l
ELSE ' ?: M- R; M! X8 A& T" y1 K
' COLLATE ' ; B; S4 M F2 s( Z) D# B: ?8 B
sys.columns.collation_name END CASE WHEN 4 ~0 w3 q) V- x& F$ Z, x
sys.columns.[is_nullable] $ l, H& h5 h3 Q" _. J) p) e
= 0 THEN ' NOT NULL' ELSE NULL' END END WHEN # Y$ w" ~6 [+ \9 c# |
Type_name( 2 i: V d) S; h/ V9 G
sys.columns.[user_type_id]) IN ('datetime', - v$ d: ^( E0 Z/ e5 [# S
'money',
2 c, U6 }6 K" O5 O: v'text', : F" @2 t6 N" Y z" |: _
'image') THEN Space(18 -
9 {2 B/ a) _/ T( N5 X N1 vLen(Type_name(sys.columns.[user_type_id])))
0 o( j# `) H4 A! c" \ CASE WHEN
0 t$ l u$ U8 c1 J2 w% Lsys.columns.[is_nullable] = 0 THEN ' NOT NULL'
. W. x1 c! N3 q5 h9 Z/ x" jELSE
; Y7 V8 U) i. p# R/ R3 A NULL' END ELSE Space(16
3 W6 B# Z- D4 j+ |- Len(Type_name(sys.columns.[user_type_id]))) : i0 X( m3 b% l" t8 D) I0 c
CASE
[9 D$ U5 W% q- X4 ?WHEN
. p; ^& g0 D6 q4 Q6 t- DColumnproperty ( @TABLE_ID, sys.columns.[name], ; d3 Z& s" R/ Z, s' c
'IsIdentity'
, ` b, _1 \ ?) = 0 THEN ELSE ' IDENTITY('
: T! d8 M7 M/ G) PCONVERT(
) J4 n b. _; |/ lVARCHAR, Isnull(Ident_seed(@TBLNAME), - ^! z8 k# x$ _1 ~+ o- T
CONVERT( 1 `5 j1 i l8 Z) O6 ?
VARCHAR, Isnull(Ident_incr(@TBLNAME),
8 ^3 j/ w* C$ A# T( I. B; mEND ( G3 k/ |$ k0 e
Space(
+ G6 y7 B4 \ O, y, z4 K) h) t, N2) CASE WHEN sys.columns.[is_nullable] = 0 THEN
5 j6 Q# w0 n' n0 R) h% n. t' NOT NULL' / i3 g7 i7 [6 X0 I6 T: {0 w9 l
ELSE NULL' END END CASE WHEN * S6 {% X. K1 T% m
sys.columns.[default_object_id] = 0 THEN '' ! u6 Z O/ ~7 P: L3 \$ W, b
--ELSE ' DEFAULT ISNULL(def.[definition] ，') * z; B1 m) ~1 ?( y: {& p0 `8 z" H
--optional section in case NAMED default cosntraints are needed: 7 _7 c, [( }5 W0 Y: n
E. G7 g, |2 o2 G+ f4 h) L) K
LSE @vbCrLf 'CONSTRAINT def.NAME DEFAULT ' Isnull(def.[definition],'') --i thought it needed to be handled differently! NOT! END --CASE cdefault END --iscomputed FROM sys.columns LEFT OUTER JOIN sys.default_constraints DEF ON sys.columns.[default_object_id] = DEF.[object_id] LEFT OUTER JOIN sys.computed_columns CALC ON sys.columns.[object_id] = CALC.[object_id] AND sys.columns.[column_id] = CALC.[column_id] WHERE sys.columns.[object_id] = @TABLE_ID ORDER BY sys.columns.[column_id]SELECT @STRINGLEN = Max(Len([name])) 1 FROM sys.objectsDECLARE @Results TABLE ( [schema_id] INT,[schema_name] VARCHAR(255),[object_id] INT,[object_name] VARCHAR(255),[index_id] INT,[index_name] VARCHAR(255),[rows] INT,[sizemb] DECIMAL(19,3),[indexdepth] INT,[type] INT,[type_desc] VARCHAR(30),[fill_factor] INT,[is_unique] INT,[is_primary_key] INT,[is_unique_constraint] INT,[index_columns_key] VARCHAR(max),[index_columns_include] VARCHAR(max) )INSERT INTO @Results SELECT sys.schemas.schema_id,sys.schemas.[name] AS schema_name,sys.objects.[object_id],sys.objects.[name] AS object_name,sys.indexes.index_id,Isnull(sys.indexes.[name],'---') AS index_name,partitions.rows,partitions.sizemb,Indexproperty(sys.objects.[object_id],sys.indexes.[name],'IndexDepth') AS IndexDepth,sys.indexes.type,sys.indexes.type_desc,sys.indexes.fill_factor,sys.indexes.is_unique,sys.indexes.is_primary_key,sys.indexes.is_unique_constraint,Isnull(Index_Columns.index_columns_key,'---') AS index_columns_key,Isnull(Index_Columns.index_columns_include,'---') AS index_columns_include FROM sys.objects JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id JOIN sys.indexes ON sys.objects.[object_id] = sys.indexes.[object_id] JOIN (SELECT [object_id],index_id,Sum(row_count) AS Rows,CONVERT(NUMERIC(19,3),CONVERT(NUMERIC(19,3),Sum( in_row_reserved_page_count lob_reserved_page_count row_overflow_reserved_page_count )CONVERT(NUMERIC AS SizeMB FROM sys.dm_db_partition_stats GROUP BY [object_id],index_id) AS partitions ON sys.indexes.[object_id] = partitions.[object_id] AND sys.indexes.index_id = partitions.index_id CROSS apply (SELECT LEFT(index_columns_key, Len(index_columns_key) - 1) AS index_columns_key ,LEFT(index_columns_include,Len(index_columns_include) - 1) AS index_columns_include FROM (SELECT (SELECT sys.columns.[name] FROM sys.index_columns JOIN sys.columns ON sys.index_columns.column_id = sys.columns.column_id AND sys.index_columns.[object_id] = sys.columns.[object_id] WHERE sys.index_columns.is_included_column = 0 AND sys.indexes.[object_id] = sys.index_columns.[object_id] AND sys.indexes.index_id = sys.index_columns.index_id ORDER BY key_ordinal FOR xml path(') AS index_columns_key,(SELECT sys.columns.[name] FROM sys.index_columns JOIN sys.columns ON sys.index_columns.column_id = sys.columns.column_id AND sys.index_columns.[object_id] = sys.columns.[object_id] WHERE sys.index_columns.is_included_column = 1 AND sys.indexes.[object_id] = sys.index_columns.[object_id] AND sys.indexes.index_id = sys.index_columns.index_id ORDER BY index_column_id FOR xml path(') AS index_columns_include) AS Index_Columns) AS Index_Columns WHERE sys.schemas.[name] LIKE CASE WHEN @SCHEMANAME = '' THEN sys.schemas.[name] ELSE @SCHEMANAME END AND sys.objects.[name] LIKE CASE WHEN @TBLNAME = '' THEN sys.objects.[name] ELSE @TBLNAME END ORDER BY sys.schemas.[name],sys.objects.[name],sys.indexes.[name]--@Results table has both PK,s Uniques and indexes in thme...pull them out for adding to funal results: SET @CONSTRAINTSQLS = '' SET @INDEXSQLS = ''SELECT @CONSTRAINTSQLS = @CONSTRAINTSQLS CASE WHEN is_primary_key = 1 OR is_unique = 1 THEN @vbCrLf CONSTRAINT index_name Space ( @STRINGLEN - Len(index_name)) CASE WHEN is_primary_key = 1 THEN PRIMARY KEY ' ELSE CASE WHEN is_unique = 1 THEN UNIQUE ' ELSE '' END END type_desc CASE WHEN type_desc='NONCLUSTERED' THEN '' ELSE END index_columns_key ) CASE WHEN index_columns_include '---' THEN ' INCLUDE index_columns_include ) ELSE '' END CASE WHEN fill_factor 0 THEN ' WITH FILLFACTOR = ' CONVERT( VARCHAR(30),fill_factor) ELSE '' END ELSE '' END FROM @RESULTS WHERE
) \7 j. K0 T# s( V8 r# J5 d5 l[type_desc] != 'HEAP' AND is_primary_key = 1 OR is_unique = 1 ORDER BY is_primary_key DESC,is_unique DESCSELECT @INDEXSQLS = @INDEXSQLS CASE WHEN is_primary_key = 0 OR is_unique = 0 THEN @vbCrLf 'CREATE INDEX index_name Space( @STRINGLEN - Len(index_name)) ' ON [object_name] index_columns_key ) CASE WHEN index_columns_include '---' THEN ' INCLUDE index_columns_include ')' ELSE '' END CASE WHEN fill_factor 0 THEN ' WITH FILLFACTOR = ' CONVERT(VARCHAR(30),fill_factor) ELSE '' END END FROM @RESULTS WHERE [type_desc] != 'HEAP' AND is_primary_key = 0 AND is_unique = 0 ORDER BY is_primary_key DESC,is_unique DESCIF @INDEXSQLS '' SET @INDEXSQLS = @vbCrLf 'GO' @vbCrLf @INDEXSQLSSET @CHECKCONSTSQLS = ''SELECT @CHECKCONSTSQLS = @CHECKCONSTSQLS @vbCrLf Isnull('CONSTRAINT sys.objects.[name] Space( @STRINGLEN - Len(sys.objects.[name])) ' CHECK Isnull(sys.check_constraints.definition,FROM sys.objects INNER JOIN sys.check_constraints ON sys.objects.[object_id] = sys.check_constraints.[object_id] WHERE sys.objects.type = 'C' AND sys.objects.parent_object_id = @TABLE_IDSET @FKSQLS = '';SELECT @FKSQLS = @FKSQLS @vbCrLf 'CONSTRAINT Object_name(constid) Space(@STRINGLEN - Len(Object_name(constid) ) FOREIGN KEY Col_name(fkeyid,fkey) ) REFERENCES ' Object_name(rkeyid) Col_name(rkeyid,rkey) ， FROM sysforeignkeys WHERE fkeyid = @TABLE_IDSET @RULESCONSTSQLS = ''SELECT @RULESCONSTSQLS = @RULESCONSTSQLS Isnull( @vbCrLf 'if not exists(SELECT [name] FROM sys.objects WHERE TYPE=''R'' AND schema_id = ' CONVERT(VARCHAR(30),sys.objects.schema_id) ' AND [name] = '''''' Object_name(sys.columns.[rule_object_id]) @vbCrLf sys.sql_modules.definition @vbCrLf 'GO' @vbCrLf 'EXEC sp_binderule sys.objects.[name] Object_name(sys.columns.[object_id]) '].[' sys.columns.[name] @vbCrLf 'GO，) FROM sys.columns INNER JOIN sys.objects ON sys.objects.[object_id] = sys.columns.[object_id] INNER JOIN sys.sql_modules ON sys.columns.[rule_object_id] = sys.sql_modules.[object_id] WHERE sys.columns.[rule_object_id] 0 AND sys.columns.[object_id] = @TABLE_IDSET @TRIGGERSTATEMENT = ''SELECT @TRIGGERSTATEMENT = @TRIGGERSTATEMENT @vbCrLf sys.sql_modules.[definition] @vbCrLf 'GO' FROM sys.sql_modules WHERE [object_id] IN(SELECT [object_id] FROM sys.objects WHERE type = 'TR' AND [parent_object_id] = @TABLE_ID)IF @TRIGGERSTATEMENT '' SET @TRIGGERSTATEMENT = @vbCrLf 'GO' @vbCrLf @TRIGGERSTATEMENTSET @EXTENDEDPROPERTIES = ''SELECT @EXTENDEDPROPERTIES = @EXTENDEDPROPERTIES @vbCrLf EXEC sys.sp_addextendedproperty @name = N [name] '',@value = N Replace(CONVERT(VARCHAR(max),[value]), ''''''''@level0type = N''SCHEMA'',@level0name = @SCHEMANAME @level1type = N''TABLE'',@level1name = @TBLNAME FROM Fn_listextendedproperty (NULL,'schema',@SCHEMANAME,'table',@TBLNAME,NULL,NULL);IF @EXTENDEDPROPERTIES '' SET @EXTENDEDPROPERTIES = @vbCrLf 'GO' @vbCrLf @EXTENDEDPROPERTIESSELECT @FINALSQL = @FINALSQL @CONSTRAINTSQLS @CHECKCONSTSQLS @FKSQLSSET @FINALSQL = Substring(@FINALSQL,1,Len(@FINALSQL) - 1); SET @FINALSQL = @FINALSQL ) @vbCrLf;SELECT @FINALSQL @INDEXSQLS @RULESCONSTSQLS @TRIGGERSTATEMENT @EXTENDEDPROPERTIES ENDgo |
|
技术问答
486 人阅读
|
0 人回复
|
2023-09-14
