SELECT table_schema“Database Name”，sum（data_length + index_length）/ 1024/1024“Database size in MB”，sum（data_free）/ 1024/1024“Free Space in MB”FROM information_schema.TABLES GROUP BY table_schema;
数据库名称关键字
SELECT table_schema“Database Name”FROM information_schema.TABLES WHERE table_schema LIKE“ ％passwords％ ”GROUP BY table_schema;
表名关键字
SELECT table_schema，table_name FROM information_schema.tables WHERE table_schema NOT LIKE“information_schema”AND table_name LIKE“ ％admin％ ”;
列名关键字
SELECT column_name，table_name FROM information_schema.columns WHERE column_name LIKE“ ％password％ ”;
列数据正则表达式
SELECT * from credit_cards WHERE cc_number REGEXP’ ^ 4 [0-9] {15} $ ';
SELECT owner，table_name，column_name FROM all_tab_columns WHERE column_name LIKE’％PASS％’;
寻找特权
SELECT * FROM session_privs SELECT * FROM USER_SYS_PRIVS SELECT * FROM USER_TAB_PRIVS SELECT * FROM USER_TAB_PRIVS_MADE SELECT * FROM USER_TAB_PRIVS_RECD SELECT * FROM ALL_TAB_PRIVS SELECT * FROM USER_ROLE_PRIVS
提取存储过程/ Java源
SELECT * FROM all_source WHERE owner NOT IN（‘SYS’，‘SYSTEM’）SELECT * FROM all_source WHERE TYPE LIKE’％JAVA％’ SELECT TO_CHAR（DBMS_METADATA.get_ddl（‘TABLE’，‘DEPT’，‘CONSUELA’））FROM dual
SELECT NAME FROM sysdatabases WHERE (NAME NOT LIKE ‘distribution’) AND (NAME NOT LIKE ‘master’) AND (NAME NOT LIKE ‘model’) AND (NAME NOT LIKE ‘msdb’) AND (NAME NOT LIKE ‘publication’) AND (NAME NOT LIKE ‘reportserver’) AND (NAME NOT LIKE ‘reportservertempdb’) AND (NAME NOT LIKE ‘resource’) AND (NAME NOT LIKE ‘tempdb’) ORDER BY NAME;
列出非默认表
SELECT ‘[’ + SCHEMA_NAME(t.schema_id) + ‘].[’ + t.name + ‘]’ AS fulltable_name, SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name, i.rows FROM sys.tables AS t INNER JOIN sys.sysindexes AS i ON t.object_id = i.id AND i.indid < 2 WHERE (ROWS> 0) AND (t.name NOT LIKE ‘syscolumns’) AND (t.name NOT LIKE ‘syscomments’) AND (t.name NOT LIKE ‘sysconstraints’) AND (t.name NOT LIKE ‘sysdepends’) AND (t.name NOT LIKE ‘sysfilegroups’) AND (t.name NOT LIKE ‘sysfiles’) AND (t.name NOT LIKE ‘sysforeignkeys’) AND (t.name NOT LIKE ‘sysfulltextcatalogs’) AND (t.name NOT LIKE ‘sysindexes’) AND (t.name NOT LIKE ‘sysindexkeys’) AND (t.name NOT LIKE ‘sysmembers’) AND (t.name NOT LIKE ‘sysobjects’) AND (t.name NOT LIKE ‘syspermissions’) AND (t.name NOT LIKE ‘sysprotects’) AND (t.name NOT LIKE ‘sysreferences’) AND (t.name NOT LIKE ‘systypes’) AND (t.name NOT LIKE ‘sysusers’) ORDER BY TABLE_NAME;
列名搜索
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME like ‘%password%’
列出非默认列
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE CHARACTER_MAXIMUM_LENGTH > 14 AND DATA_TYPE NOT IN (‘bigint’,‘binary’,‘bit’,‘cursor’,‘date’,‘datetime’,‘datetime2’, ‘datetimeoffset’,‘float’,‘geography’,‘hierarchyid’,‘image’,‘int’,‘money’,‘real’, ‘smalldatetime’,‘smallint’,‘smallmoney’,‘sql_variant’,‘table’,‘time’,‘timestamp’, ‘tinyint’,‘uniqueidentifier’,‘varbinary’,‘xml’) AND TABLE_NAME=‘CreditCard’ OR CHARACTER_MAXIMUM_LENGTH < 1 AND DATA_TYPE NOT IN ( ‘bigint’, ‘binary’, ‘bit’, ‘cursor’, ‘date’, ‘datetime’, ‘datetime2’, ‘datetimeoffset’, ‘float’, ‘geography’, ‘hierarchyid’, ‘image’, ‘int’, ‘money’, ‘real’, ‘smalldatetime’, ‘smallint’, ‘smallmoney’, ‘sql_variant’, ‘table’, ‘time’, ‘timestamp’, ‘tinyint’, ‘uniqueidentifier’, ‘varbinary’, ‘xml’) AND TABLE_NAME=‘CreditCard’ ORDER BY COLUMN_NAME;
4081
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
