记录一下日常用到的比较常用的SQL语句。
想起来哪些就记录一下,方便自己日后使用。
目录
1、查询某一个字段在哪些用户表中存在
Oracle
SELECT TABLE_NAME,COLUMN_NAME FROM USER_TAB_COLUMNS where COLUMN_NAME like '%CLASS_NAME%'; --查询当前用户下数据库中的某个字段在哪些表中存在
--TABLE_NAME 表名
--COLUMN_NAME 列名
SQL Server
SELECT A.NAME AS TABLE_NAME FROM SYSOBJECTS A
INNER JOIN SYSCOLUMNS B ON B.ID = A.ID
WHERE B.NAME = 'COLUMN_NAME'
--TABLE_NAME 表名
--COLUMN_NAME 列名
2、查询某一个字符串在哪些用户表中存在
SQL Server
DECLARE @what varchar(800)
SET @what='要搜索的字符串' --要搜索的字符串
DECLARE @sql varchar(8000)
DECLARE TableCursor CURSOR LOCAL FOR
SELECT sql='IF EXISTS ( SELECT 1 FROM ['+o.name+'] WHERE ['+c.name+'] LIKE ''%'+@what+'%'' ) PRINT ''所在的表及字段:['+o.name+'].['+c.name+']'''
FROM syscolumns c JOIN sysobjects o ON c.id=o.id
-- 175=char 56=int 可以查 select * from sys.types
WHERE o.xtype='U' AND c.status>=0 AND c.xusertype IN (175, 239, 231, 167 )
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @sql
WHILE @@FETCH_STATUS=0
BEGIN
EXEC( @sql )
FETCH NEXT FROM TableCursor INTO @sql
END
CLOSE TableCursor
-- 删除游标引用
DEALLOCATE TableCursor
3、关于死锁
Oracle
SELECT B.OWNER,B.OBJECT_NAME,A.SESSION_ID,A.LOCKED_MODE FROM V$LOCKED_OBJECT A,DBA_OBJECTS B WHERE B.OBJECT_ID = A.OBJECT_ID;--查看被锁的表对象
SELECT B.USERNAME,B.SID,B.SERIAL#,B.LOGON_TIME FROM V$LOCKED_OBJECT A , V$SESSION B WHERE A.SESSION_ID = B.SID ORDER BY B.LOGON_TIME;--查看哪个用户的哪个进程导致的死锁
ALTER SYSTEM KILL SESSION '1,2';--1代表SID,2代表SERIAL# 杀掉进程
4、查询某一个表在数据库中那些地方被使用
SQL Server
SELECT DISTINCT
o.name AS Object_Name ,
o.type_desc
FROM sys.sql_modules m
INNER JOIN sys.objects o ON m.object_id = o.object_id
WHERE m.definition LIKE '%你要查询的表名%';