常用查询

a.mysql数据库
1.查询mysql中所有数据库名称
select SCHEMA_NAME from information_schema.SCHEMATA
2.查询mysql某个数据库下的所有表名
select TABLE_NAME FROM information_schema.TABLES where TABLE_SCHEMA = '数据库名'
3.授权用户可以在任何主机连接
grant all privileges on *.* to 'username'@'%' identified by 'password' with grant option;
4.查看mysql视图是否存在
select count(information_schema.VIEWS.TABLE_SCHEMA) from 
information_schema.VIEWS where information_schema.VIEWS.TABLE_NAME = ‘视图名’
 // and (information_schema.VIEWS.TABLE_SCHEMA = ‘数据库名称’)

b.sqlserver数据库
1.判断表中字段是否存在
select * from syscolumns where id = object_id('表名') and name = '字段名'
2.查询一个库中所有表名
select name from sysobjects where xtype = 'U' //and name like '%a%'
3.判断数据库是否存在
select * from sys.databases where name = '数据库名'
4.判断表是否存在
select * from sysobjects where id = object_id(N'[表名]') and OBJECTPROPERTY(id,N'IsUserTable')=1
5.判断存储过程是否存在
select * from sysobjects where id = object_id(N'[存储过程名]') and OBJECTPROPERTY(id,N'IsProcedure') = 1
6.判断临时表是否存在
if object_id('tempdb..#临时表名') is not null 
drop table #临时表名
7.判断视图是否存在
select * from sys.views where object_id = OBJECT_ID(N'[dbo].[视图名]'))
8.获取用户创建的对象信息
select [name],[id],crdate from sysobjects where xtype = 'U'
9.判断表中是否存在索引
select * from sysindexes where id=OBJECT_ID('表名‘) and name='索引名'