MYBLOG

1、SQL数据库备份命令

BACKUP DATABASE test to DISK ='C/dfasd.bak'

2、查询所有的数据库名称

select name from master..sysdatabases

 3、查询指定数据库中的所有表名称

Select TABLE_NAMEFROM 数据库名称.INFORMATION_SCHEMA.TABLESWhere TABLE_TYPE='BASE TABLE'

4、查询指定表中的所有字段及其精度信息

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 ='表名');

5、查询出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.scale<>0then ','+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.precision<>53then '('+cast(c.precisionas varchar(10))+')'
                            when DataType in('time','datetime2','datetimeoffset')and c.scale<>7then '('+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_name<>cast(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_factor<>0then ', 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.type<>3
                           then ' DESC'
                           WHEN ic.is_descending_key=0AND i.type<>3
                           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

6、查询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

7、查询出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