exec sp_MSforeachdb @command1='USE ? if exists(SELECT 1 from sysobjects where id=object_id(''表名'')) PRINT ''?'''
查询字段在数据库哪个表
SELECT
表名 = D.name ,
字段序号 = A.colorder,
字段名 = A.name,
类型 = B.name,
字段说明=isnull(g.[value],'')FROM syscolumns A
LEFTJOIN systypes B ON A.xusertype=B.xusertype
INNERJOIN sysobjects D ON A.id=D.id and D.xtype='U'and D.name<>'dtproperties'LEFTJOIN sys.extended_properties G ON a.id=g.major_id and a.colid=g.minor_id
WHERE A.name='userID'--字段名
查询数据库的所有表
SELECT NAME FROM SYSOBJECTS WHERETYPE='U'SELECT*FROM INFORMATION_SCHEMA.TABLES
xtype='U':表示所bai有用户表,xtype='S':表示所有系统表。
查询字段值属于数据库的哪张表,哪个列 --存储过程
USE[数据库名]
GO
/****** Object: StoredProcedure [dbo].[SP_FindValueInDB] Script Date: 2017/3/5 10:35:01
--此存储过程用于查询一个值属于数据库中的那张表的哪个列,返回结果为两列,一列是表名,一列是列名
******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTERPROCEDURE[dbo].[SP_FindValueInDB](@valueVARCHAR(1024))ASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;DECLARE@sqlVARCHAR(1024)DECLARE@tableVARCHAR(64)DECLARE@columnVARCHAR(64)CREATETABLE#t (
tablename VARCHAR(64),
columnname VARCHAR(64))DECLARETABLESCURSORFORSELECT o.name, c.name
FROM syscolumns c
INNERJOIN sysobjects o ON c.id = o.id
WHERE o.type='U'AND c.xtype IN(167,175,231,239)ORDERBY o.name, c.name
OPENTABLESFETCHNEXTFROMTABLESINTO@table,@columnWHILE @@FETCH_STATUS=0BEGINSET@sql='IF EXISTS(SELECT NULL FROM ['+@table+'] 'SET@sql=@sql+'WHERE RTRIM(LTRIM(['+@column+'])) LIKE ''%' + @value + '%'')'
SET @sql = @sql + 'INSERTINTO#t VALUES (''' + @table + ''', ''' SET@sql=@sql+@column+''')'
EXEC(@sql)FETCHNEXTFROMTABLESINTO@table,@columnENDCLOSETABLESDEALLOCATETABLESSELECT*FROM#t DROPTABLE#t End
查询字段值属于数据库的哪张表,哪个列 --游标
declare@cloumnsvarchar(40)declare@tablenamevarchar(40)declare@strvarchar(40)declare@countsintdeclare@sql nvarchar(2000)declare MyCursor CursorForSelect a.name asColumns, b.name as TableName from syscolumns a,sysobjects b,systypes c
where a.id = b.id
and b.type='U'and a.xtype=c.xtype
and c.name like'%char%'set@str='申请VPN权限'Open MyCursor
FetchnextFrom MyCursor Into@cloumns,@tablenameWhile(@@Fetch_Status=0)Beginset@sql='select @tmp_counts=count(*) from '+@tablename+' where '+@cloumns+' = '''+@str+''''execute sp_executesql @sql,N'@tmp_counts int out',@countsoutif@counts>0beginprint'表名为:'+@tablename+',字段名为'+@cloumnsendFetchnextFrom MyCursor Into@cloumns,@tablenameEndClose MyCursor
Deallocate MyCursor
查询数据库所有触发器
SqlServer用法
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’ --所有表
Oracle用法
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
表名 =casewhen a.colorder=1then d.name else''end,
表说明 =casewhen a.colorder=1then isnull(f.value,'')else''end,
字段序号 = a.colorder,
字段名 = a.name,
标识 =casewhen COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1then'√'else''end,
主键 =casewhenexists(SELECT1FROM sysobjects where xtype='PK'and parent_obj=a.id and name in(SELECT name FROM sysindexes WHERE indid in(SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid)))then'√'else''end,
类型 = b.name,
占用字节数 = a.length,
长度 = COLUMNPROPERTY(a.id,a.name,'PRECISION'),
小数位数 = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
允许空 =casewhen a.isnullable=1then'√'else''end,
默认值 = isnull(e.text,''),
字段说明 = isnull(g.[value],'')FROM
syscolumns a
leftjoin
systypes b
on
a.xusertype=b.xusertype
innerjoin
sysobjects d
on
a.id=d.id and d.xtype='U'and d.name<>'dtproperties'leftjoin
syscomments e
on
a.cdefault=e.id
leftjoin
sys.extended_properties g
on
a.id=G.major_id and a.colid=g.minor_id
leftjoin
sys.extended_properties f
on
d.id=f.major_id and f.minor_id=0where
d.name='djcl_sap_customerPrice_mx_sap'--如果只查询指定表,加上此where条件,tablename是要查询的表名;去除where条件查询所有的表信息orderby
a.id,a.colorder