sql数据库备份解决方案
命令备份：backup database test to disk ='c/dfasd.bak'
1、查询出指定数据库中的所有表名称
select table_namefrom数据库名称.information_schema.tableswheretable_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 typelengthfromsys.columnsaleft join sys.typesbon a.system_type_id=b.system_type_idanda.user_type_id=b.user_type_idwherea.object_id=(selectobject_id fromsys.objectswherename ='表名');
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)
;withcolumndefsas
(
select tableobj=c.[object_id]
,colseq=c.column_id
,columndef=quotename(c.name)+' '
+case
whenc.is_computed=1then'as '+coalesce(k.[definition],'')
+casewhenk.is_persisted=1then' persisted'+casewhenk.is_nullable=0then' not null' else '' endelse'' end
elsedatatype
+case
when datatype in('decimal','numeric')then'('+cast(c.precisionasvarchar(10))+casewhenc.scale0then','+cast(c.scaleasvarchar(10))else'' end +')'
when datatype in('char','varchar','nchar','nvarchar','binary','varbinary')then'('+casewhenc.max_length=-1then'max' else case whendatatypein('nchar','nvarchar')thencast(c.max_length/2asvarchar(10))elsecast(c.max_lengthasvarchar(10))endend +')'
when datatype='float'andc.precision53then'('+cast(c.precisionasvarchar(10))+')'
when datatype in('time','datetime2','datetimeoffset')andc.scale7then'('+cast(c.scaleasvarchar(10))+')'
else ''
end
end
+casewhenc.is_identity=1then' identity('+cast(ident_seed(quotename(object_schema_name(c.[object_id]))+'.'+quotename(object_name(c.[object_id])))asvarchar(30))+','+cast(ident_incr(quotename(object_schema_name(c.[object_id]))+'.'+quotename(object_name(c.[object_id])))asvarchar(30))+')'else'' end
+casewhenc.is_rowguidcol=1then' rowguidcol'else '' end
+casewhenc.xml_collection_id>0then' (content '+quotename(schema_name(x.schema_id))+'.'+quotename(x.name)+')'else'' end
+case
whenc.is_computed=0anduserdefinedflag=0
thencase
when c.collation_namecast(databasepropertyex(db_name(),'collation')asnvarchar(128))
then ' collate '+c.collation_name
else ''
end
else''
end
+casewhenc.is_computed=0thencase 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_constraintsdon c.default_object_id=d.[object_id]
left joinsys.computed_columnskon c.[object_id]=k.[object_id]
andc.column_id=k.column_id
left join sys.xml_schema_collectionsxonc.xml_collection_id=x.xml_collection_id
)
,indexdefsas
(
select tableobj=i.[object_id]
,ixname=quotename(i.name)
,ixpkflag=i.is_primary_key
,ixtype=casewheni.is_primary_key=1then'primary key 'wheni.is_unique=1then'unique ' else '' end
+lower(type_desc)
,ixdef='('+ixcollist+')'
+coalesce(' include ('+ixincllist+')','')
,ixopts=ixoptlist
from sys.indexesi
left joinsys.statsson i.index_id=s.stats_idandi.[object_id]=s.[object_id]
cross apply(
select stuff((selectcasewhen i.is_padded=1then', pad_index=on'else'' end
+casewheni.fill_factor0then', fillfactor='+cast(i.fill_factorasvarchar(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=1andi.type3
then ' desc'
when ic.is_descending_key=0andi.type3
then ' asc'
else ''
end
fromsys.index_columnsic
joinsys.columnscon 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.columnscon 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.columnscon 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.columnscon 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 '
+casewhenis_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+')'
+casewhenfkdelopt is not nullthen' on delete '+fkdeloptelse'' end
+casewhenfkupdopt is not nullthen' on update '+fkupdoptelse'' end
+casewhenfknorepl=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_modulesminner 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.objectsoinner join sys.schemasson 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 nextfroms_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.nameasname
,c.column_idascolumn_id
,c.is_identityasis_identity
from sys.columnsc
inner join sys.typeston c.system_type_id=t.system_type_idandc.user_type_id=t.user_type_id
inner join sys.objectsoon 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 nextfroms_cursor into @table_name,@table_full_name
end
close s_cursor
deallocate s_cursor