Sql 工具语句

1. 查以 tlkp 开头的所有的表名：

-------------------------------------------------------------------------------------------------------------

select table_name from information_schema.tables where table_name like 'tlkp%'

-------------------------------------------------------------------------------------------------------------

 潜了太长时间的水，现在开始更新一下。

 

1, 只备份一个表的结构

SELECT * into hr_ttm from tblAttachment where 1=0

2, 备份一个表

SELECT * into hr_ttm from tblAttachment

3, 查询一个表的所有列名

select name from syscolumns where id=(select id from sysobjects where id=object_id('Daypart'))

--------------------------------------------------------------------------
declare @sTableName varchar(100)='market'
declare @sFieldName varchar(4000) =''

select  @sFieldName += '[' + name + '],' from syscolumns where id=(select id from sysobjects where id=object_id(@sTableName))

print @sFieldName
----------------------------------------------------------------------------

4，判断一个表是否存在

if object_id('tmpProjectSummary') is not null
 print '已存在'

 if object_id('tempdb.dbo.#TmpDaypartDiscount') is not null
  drop table #TmpDaypartDiscount  --删除临时表 

5. 备份数据库

declare @sql varchar(1000)
set @sql=
'Backup Database BugManager To disk= ''F:\Project\DataBaseBackFile\SqlServer\BugManager_'
+
convert(char(8),getdate(),112) +
rtrim(right(replace(convert(char(20),getdate(),120),':',''),6))
+
'.bak'''
exec(@sql)

6,

查询所有的数据库名称

select name from master..sysdatabases order by name

查询某个数据库中的所有表名 和 视图名
 use BugManager select name from sys.Objects where type='U' OR type='V' order by name
 
 查询以 表名以 security_ 开头的表
 select name from sysobjects where xtype='U' and name like 'security_%'

 查询数据库里面的所有的表名  
  
 select name from sysobjects where xtype='TR' --所有触发器     
 select name from sysobjects where xtype='P' --所有存储过程     
 select name from sysobjects where xtype='V' --所有视图     
 select name from sysobjects where xtype='U' --所有表     
    
 以上为SqlServer用法     
    
 Select object_name From user_objects Where object_type='TRIGGER';  --所有触发器     
 Select object_name From user_objects Where object_type='PROCEDURE';  --所有存储过程     
 Select object_name From user_objects Where object_type='VIEW';  --所有视图     
 Select object_name From user_objects Where object_type='TABLE'; --所有表     
 Select object_name From user_objects Where object_type='SEQUENCE'; --序列     
    
 以上为Oracle用法  

7. 取出某个所数据库的所有表的数据

if object_id('tempdb.dbo.#tmpTableName') is not null
 drop table #tmpTableName  --删除临时表 
  
 use BugManager select name,ROW_NUMBER() OVER(ORDER BY name) as Item into #tmpTableName from sys.Objects where type='U' order by name
 --select * from #tmpTableName
 
 declare @sSql varchar(max)=''
 select @sSql=@sSql + '
 select * from ' + name from #tmptablename

 print @sSql
 
 exec(@sSql)

8. 取出某个表的所有字段

declare @sTableName varchar(100)='m_Company'
declare @sFieldName varchar(4000) =''
declare @sSql varchar(max)=''

select  @sFieldName += '
  ,[' + name + ']' from syscolumns where id=(select id from sysobjects where id=object_id(@sTableName))

if(LEN(@sFieldName)>1)
begin
 set @sFieldName=substring(@sFieldName,6,LEN(@sFieldName)-2)
end

select @sSql='
select
  ' +  @sFieldName + '
from [' + @sTableName + ']'

print @sSql