SQL数据库备份解决方案 命令备份：BACKUP DATABASE test to DISK ='C/dfasd.bak' 1、查询出指定数据库中的所有表名称 Select TABLE_NAMEFROM数据库名称.INFORMATION_SCHEMA.TABLESWhereTABLE_TYPE='BASE TABLE' 2、查询出指定表中的所有字段及精度 select a.
SQL数据库备份解决方案
命令备份：BACKUP DATABASE test to DISK ='C/dfasd.bak'
1、查询出指定数据库中的所有表名称
Select TABLE_NAMEFROM
数据库名称.INFORMATION_SCHEMA.TABLESWhere
TABLE_TYPE='BASE TABLE'
2、查询出指定表中的所有字段及精度
select a.name,
case a.precision
when 0
then
case
a.is_ansi_padded
when 1
then
convert(nvarchar(15),b.name+'('+convert(nvarchar(10),a.max_length)+')')
when 0
then
b.name
end
else
case
a.scale
when 0
then
b.name
else
b.name+'('+convert(nvarchar(10),a.precision)+','+convert(nvarchar(10),a.scale)+')'
end
end
as typelengthfrom
sys.columnsa
left join
sys.typesb
on a.system_type_id=b.system_type_idand
a.user_type_id=b.user_type_idwhere
a.object_id=(select
object_id from
sys.objectswhere
name ='表名');
3、查询出SQL中数据库中所有表的定义
SET ARITHABORTON
SET CONCAT_NULL_YIELDS_NULLON
SET QUOTED_IDENTIFIERON
SET ANSI_NULLSON
SET ANSI_PADDINGON
SET ANSI_WARNINGSON
SET NUMERIC_ROUNDABORTOFF
declare
@crlf char(2)
SET @crlf=char(13)+char(10)
;WITHColumnDefs
as
(
select
TableObj=c.[object_id]
,ColSeq=c.column_id
,ColumnDef=quotename(c.Name)+'
'
+case
whenc.is_computed=1then
'as '+coalesce(k.[definition],'')
+casewhen
k.is_persisted=1then
' PERSISTED'+casewhen
k.is_nullable=0then
' NOT NULL' else
'' endelse
'' end
elseDataType
+case
when
DataType in('decimal','numeric')then
'('+cast(c.precisionas
varchar(10))+casewhen
c.scale0then
','+cast(c.scaleas
varchar(10))else
'' end
+')'
when
DataType in('char','varchar','nchar','nvarchar','binary','varbinary')then
'('+casewhen
c.max_length=-1then
'max' else
case whenDataType
in('nchar','nvarchar')then
cast(c.max_length/2as
varchar(10))else
cast(c.max_lengthas
varchar(10))end
end +')'
when
DataType='float'and
c.precision53then
'('+cast(c.precisionas
varchar(10))+')'
when
DataType in('time','datetime2','datetimeoffset')and
c.scale7then
'('+cast(c.scaleas
varchar(10))+')'
else
''
end
end
+casewhen
c.is_identity=1then
' IDENTITY('+cast(IDENT_SEED(quotename(object_schema_name(c.[object_id]))+'.'+quotename(object_name(c.[object_id])))as
varchar(30))+','+cast(ident_incr(quotename(object_schema_name(c.[object_id]))+'.'+quotename(object_name(c.[object_id])))as
varchar(30))+')'else
'' end
+casewhen
c.is_rowguidcol=1then
' ROWGUIDCOL'else
'' end
+casewhen
c.xml_collection_id>0THEN' (CONTENT '+QUOTENAME(SCHEMA_NAME(x.SCHEMA_ID))+'.'+QUOTENAME(x.name)+')'ELSE
'' end
+case
whenc.is_computed=0and
UserDefinedFlag=0
thencase
when
c.collation_namecast(databasepropertyex(db_name(),'collation')as
nvarchar(128))
then
' COLLATE '+c.collation_name
else
''
end
else''
end
+casewhen
c.is_computed=0then
case when
c.is_nullable=0then
' NOT' else
'' end+' NULL'else
'' end
+case
whenc.default_object_id>0
then' CONSTRAINT '+quotename(d.name)+'
DEFAULT '+coalesce(d.[definition],'')
else''
end
from sys.columnsc
cross
apply(
select
DataType=type_name(c.user_type_id)
,UserDefinedFlag=case
whenc.system_type_id=c.user_type_id
then 0
else 1
end)F1
left joinsys.default_constraintsd
ON c.default_object_id=d.[object_id]
left joinsys.computed_columnsk
ON c.[object_id]=k.[object_id]
andc.column_id=k.column_id
left
join sys.xml_schema_collectionsxON
c.xml_collection_id=
x.xml_collection_id
)
,IndexDefsas
(
select
TableObj=i.[object_id]
,IxName=quotename(i.name)
,IxPKFlag=i.is_primary_key
,IxType=casewhen
i.is_primary_key=1then
'PRIMARY KEY 'when
i.is_unique=1then
'UNIQUE ' else
'' end
+lower(type_desc)
,IxDef='('+IxColList+')'
+coalesce(' INCLUDE ('+IxInclList+')','')
,IxOpts=IxOptList
from sys.indexesi
left joinsys.statss
ON i.index_id=s.stats_idand
i.[object_id]=s.[object_id]
cross
apply(
select
stuff((selectcase
when i.is_padded=1then
', PAD_INDEX=ON'else
'' end
+casewheni.fill_factor0then
', FILLFACTOR='+cast(i.fill_factoras
varchar(10))else
'' end
+casewheni.ignore_dup_key=1then
', IGNORE_DUP_KEY=ON'else''
end
+casewhens.no_recompute=1then
',STATISTICS_RECOMPUTE=ON'else
'' end
+casewheni.allow_row_locks=0then
', ALLOW_ROW_LOCKS=OFF'else''
end
+casewheni.allow_page_locks=0then
', ALLOW_PAGE_LOCKS=OFF'else''
end)
,1,2,''))F_IxOpts(IxOptList)
cross
apply(
select
stuff((select','+quotename(c.name)
+case
when
ic.is_descending_key=1AND
i.type3
then
' DESC'
WHEN
ic.is_descending_key=0AND
i.type3
THEN
' ASC'
ELSE
''
end
fromsys.index_columnsic
joinsys.columnsc
ON ic.[object_id]=c.[object_id]
andic.column_id=c.column_id
whereic.[object_id]=i.[object_id]
andic.index_id=i.index_id
andic.is_included_column=0
orderbyic.key_ordinal
FORxmlpath(''),type).value('.','nvarchar(max)')
,1,1,''))F_IxCols(IxColList)
cross
apply(
select
stuff((select','+quotename(c.name)
fromsys.index_columnsic
joinsys.columnsc
ON ic.[object_id]=c.[object_id]
andic.column_id=c.column_id
whereic.[object_id]=i.[object_id]
andic.index_id=i.index_id
andic.is_included_column=1
orderbyic.key_ordinal
FORxmlpath(''),type).value('.','nvarchar(max)')
,1,1,''))F_IxIncl(IxInclList)
where
i.type_desc'HEAP'
)
,FKDefsas
(
select
TableObj=f.parent_object_id
,FKName=quotename(f.name)
,FKRef=quotename(object_schema_name(f.referenced_object_id))+'.'
+quotename(object_name(f.referenced_object_id))
,FKColList=ParentColList
,FKRefList=RefColList
,FKDelOpt=casef.delete_referential_action
when1
then 'CASCADE'
when2
then 'SET NULL'
when3
then 'SET DEFAULT'
end
,FKUpdOpt=casef.update_referential_action
when1
then 'CASCADE'
when2
then 'SET NULL'
when3
then 'SET DEFAULT'
end
,FKNoRepl=f.is_not_for_replication
from sys.foreign_keysf
cross
apply(
select
stuff((select','+quotename(c.name)
fromsys.foreign_key_columnsk
joinsys.columnsc
ON k.parent_object_id=c.[object_id]
and k.parent_column_id=c.column_id
wherek.constraint_object_id=f.[object_id]
orderbyconstraint_column_id
FORxmlpath(''),type).value('.','nvarchar(max)')
,1,1,''))F_Parent(ParentColList)
cross
apply(
select
stuff((select','+quotename(c.name)
fromsys.foreign_key_columnsk
joinsys.columnsc
ON k.referenced_object_id=c.[object_id]
and k.referenced_column_id=c.column_id
wherek.constraint_object_id=f.[object_id]
orderbyconstraint_column_id
FORxmlpath(''),type).value('.','nvarchar(max)')
,1,1,''))F_Ref(RefColList)
)
select TableName
,[definition]
from sys.tablest
cross apply(
select
TableName=quotename(object_schema_name(t.[object_id]))+'.'
+quotename(object_name(t.[object_id])))F_Name
cross apply(
select
stuff((select@crlf+' ,'+ColumnDef
fromColumnDefs
whereTableObj=t.[object_id]
orderbyColSeq
FORxmlpath(''),type).value('.','nvarchar(max)')
,1,5,''))F_Cols(ColumnList)
cross apply(
select
stuff((select@crlf+' ,CONSTRAINT '+quotename(name)+'
CHECK '
+casewhen
is_not_for_replication=1then'NOT FORREPLICATION '
else''
end
+coalesce([definition],'')
fromsys.check_constraints
whereparent_object_id=t.[object_id]
FORxmlpath(''),type).value('.','nvarchar(max)')
,1,2,''))F_Const(ChkConstList)
cross apply(
select
stuff((select@crlf+' ,CONSTRAINT '+IxName+'
'+IxType+' '+IxDef+coalesce('
WITH ('+IxOpts+')','')
fromIndexDefs
whereTableObj=t.[object_id]
andIxPKFlag=1
FORxmlpath(''),type).value('.','nvarchar(max)')
,1,2,''))F_IxConst(IxConstList)
cross apply(
select
stuff((select@crlf+' ,CONSTRAINT '+FKName+'
FOREIGN KEY '+'('+FKColList+')'+'
REFERENCES '+FKRef+' ('+FKRefList+')'
+casewhen
FKDelOpt is
NOT NULLthen
' ON DELETE '+FKDelOptelse
'' end
+casewhen
FKUpdOpt is
NOT NULLthen
' ON UPDATE '+FKUpdOptelse
'' end
+casewhen
FKNoRepl=1then
' NOT FOR REPLICATION'else''
end
fromFKDefs
whereTableObj=t.[object_id]
FORxmlpath(''),type).value('.','nvarchar(max)')
,1,2,''))F_Keys(FKConstList)
cross apply(
select
stuff((select@crlf+'CREATE '+IxType+'
INDEX '+IxName+' ON '+TableName+'
'+IxDef+coalesce(' WITH ('+IxOpts+')','')
fromIndexDefs
whereTableObj=t.[object_id]
andIxPKFlag=0
FORxmlpath(''),type).value('.','nvarchar(max)')
,1,2,''))F_Indexes(IndexList)
cross apply(
select
[definition]=(select'CREATE TABLE '+TableName+@crlf+'('+@crlf+'
'+ColumnList+coalesce(@crlf+ChkConstList,'')+coalesce(@crlf+IxConstList,'')+coalesce(@crlf+FKConstList,'')+@crlf+')'+coalesce(@crlf+IndexList,'')+@crlf
FOR xmlpath(''),type).value('.','nvarchar(max)'))F_Link
3、查询出SQL中数据库中所有视图，函数，存储过程触发器脚本
SELECT
QUOTENAME(object_schema_name(m.object_id))+'.'+QUOTENAME(object_name(m.object_id))AS
[name],o.type,m.definition
FROM sys.sql_modulesm
INNER JOIN
sys.objectso
ON m.object_id=
o.object_id
4、查询出SQL中数据库中某个表中数据的Insert语句
set nocounton
declare
@table_name varchar(100)
declare
@table_full_name varchar(100)
declare
@sql nvarchar(max)
declare
@sqlvalues nvarchar(max)
declare
@identity int
DECLARE
S_Cursor CURSOR FOR
SELECT o.nameasname, '['
+ s.name
+ '].['
+ o.name
+ ']'
as full_name
FROM sys.objectso
inner join
sys.schemass
on o.schema_id=
s.schema_id
where o.name='Product'
and s.name='dbo'
and o.type='U'
order byo.name
OPEN S_Cursor
FETCH NEXTFROM
S_Cursor INTO
@table_name,@table_full_name
WHILE
@@FETCH_STATUS = 0
BEGIN
set @sql=
' ('
set @sqlvalues=
'values (''+'
set @identity= 0
select
@sqlvalues = @sqlvalues+
col +
' + '','' + ',@sql
= @sql
+ '[' +
name + '],',
@identity=
@identity + is_identity
from (selectcase
when
t.name
= 'varchar' then'case when ['+ c.name +']
is null Then ''null'' Else '+''''''''' + ' + 'replace(['+c.name+'],'''''''','''''''''''')' +
'+'''''''''+' End'
when
t.name
= 'nvarchar' Then 'case When ['+ c.name +']
Is Null Then ''null'' Else '+'''N'''''' + ' +
'replace(['+ c.name+'],'''''''','''''''''''')' +
'+'''''''''+' End'
when
t.name
= 'char' Then 'case When ['+ c.name +']
Is Null Then ''null'' Else '+''''''''' + ' +
'cast(Replace(['+ c.name+'],'''''''','''''''''''') As
Char(' + Cast(c.max_length As
Varchar) +
'))+'''''''''+' End'
when
t.name
= 'nchar' Then 'case When ['+ c.name +']
Is Null Then ''null'' Else '+'''N'''''' + ' +
'cast(Replace(['+ c.name+'],'''''''','''''''''''') As
Char(' + Cast(c.max_length As
Varchar) +
'))+'''''''''+' End'
when
t.name
= 'datetime' Then 'case When ['+ c.name +']
Is Null Then ''null'' Else '+''''''''' + ' +
'convert(char(23),['+c.name+
'],121)'+ '+'''''''''+' End'
when
t.name
= 'smalldatetime'
Then 'case When ['+ c.name +']
Is Null Then ''null'' Else '+''''''''' + ' +
'convert(char(23),['+c.name+
'],120)'+ '+'''''''''+' End'
when
t.name
in('int','smallint','tinyint')Then
'case When ['+ c.name +'] Is Null Then
''null'' Else ' +
'cast(['+ c.name +
'] As Varchar)'+' End'
when
t.name
= 'uniqueidentifier'
Then'case When ['+ c.name +']
Is Null Then ''null'' Else '+''''''''' + ' +
'convert(char(36),['+c.name+
'])'+ '+'''''''''+' End'
--whent.name='ntext' Then 'case When ['+c.name+'] Is Null Then ''null'' Else '+'''N'''''' + ' + 'replace(['+ c.name+'],'''''''','''''''''''')' + '+'''''''''+' End'
else
'case When ['+
c.name
+'] Is Null Then ''null'' Else ' +'''N'''''' + ' +
'cast(['+ c.name +
'] As nvarchar(4000))'+ '+'''''''''+' End'
End
as col
,c.nameas
name
,c.column_idas
column_id
,c.is_identityas
is_identity
from
sys.columnsc
inner
join sys.typest
on c.system_type_id=
t.system_type_idand
c.user_type_id=
t.user_type_id
inner
join sys.objectso
on o.object_id=
c.object_id
where
o.type
= 'U'
and o.name=
@table_name)t
order bycolumn_id
set @sql ='select ''set identity_insert'+@table_full_name+'
on Insert Into '+ @table_full_name
+ Left(@Sql,Len(@Sql)-1)+')
' + Left(@Sqlvalues,Len(@Sqlvalues)-4) +
')'' From '+@table_full_name
exec(@sql)
FETCH NEXTFROM
S_Cursor INTO
@table_name,@table_full_name
END
CLOSE S_Cursor
DEALLOCATE
S_Cursor
![[Web front-end] Démarrage rapide de Node.js](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
