sql:sql server,MySQL,PostgreSQL的表,视图,存储过程结构查询

sql server 2005:

1 --SQL SERVER 2005 生成代码需要知道的SQL语句
2 use LibrarySystem
3 --查询当前数据库所有表和其的主键字段，字段类型，长度，是否为空值
4 SELECT d.name as 'TableName',a.name as 'FieldName',b.name as 'TypeName',a.length as 'Length',a.isnullable as 'IS_NULL' FROM  syscolumns  a,  systypes b,sysobjects d ,INFORMATION_SCHEMA.KEY_COLUMN_USAGE c WHERE  a.xtype=b.xusertype  and  a.id=d.id  and  d.xtype='U' and c.TABLE_NAME   = d.name and c.COLUMN_NAME=a.name
5 --获取BookKindList表结构里面的字段名， 类型，长度
6 SELECT c.name   as   FieldName,t.name   as   FieldType,   c.length   as   FieldLength   FROM   SYSCOLUMNS   c   inner   join     systypes   t   on   c.xusertype=t.xusertype   WHERE   c.ID   =   OBJECT_ID('BookKindList')
7 --

MySQL 6.7

 1 #数据库MySQL 6.7
 2 use sakila;
 3 #查询表名
 4 show tables;
 5 #
 6 SELECT TABLE_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='sakila';
 7 
 8 select column_name from information_schema.columns where table_schema='sakila' and table_name='actor';
 9 #表结构 字段名， 类型，长度
10 select * from information_schema.columns where table_schema='sakila' and table_name='actor';

#所有表的主键
select * from information_schema.columns where table_schema='attend' and column_key='PRI';

PostgreSQL 8.4,9.3

1 --查询结构PostgreSQL 8.4,9.3
2 SELECT * FROM information_schema.columns;
3 --查询数据库的建成立的表结构
4 SELECT * FROM information_schema.columns where table_catalog='geovindu' and table_schema='public';

sql server 外键字段和外键表查询

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

--外鍵字段和錶名
SELECT  obj.name AS FK_NAME,
    sch.name AS [schema_name],
    tab1.name AS [table],
    col1.name AS [column],
    tab2.name AS [referenced_table],
    col2.name AS [referenced_column]
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
    ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
    ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
    ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
    ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2
    ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2
    ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id
     
--
SELECT PKTABLE_QUALIFIER = CONVERT(SYSNAME,DB_NAME()),
       PKTABLE_OWNER = CONVERT(SYSNAME,SCHEMA_NAME(O1.SCHEMA_ID)),
       PKTABLE_NAME = CONVERT(SYSNAME,O1.NAME),
       PKCOLUMN_NAME = CONVERT(SYSNAME,C1.NAME),
       FKTABLE_QUALIFIER = CONVERT(SYSNAME,DB_NAME()),
       FKTABLE_OWNER = CONVERT(SYSNAME,SCHEMA_NAME(O2.SCHEMA_ID)),
       FKTABLE_NAME = CONVERT(SYSNAME,O2.NAME),
       FKCOLUMN_NAME = CONVERT(SYSNAME,C2.NAME),
       -- Force the column to be non-nullable (see SQL BU 325751)
       --KEY_SEQ             = isnull(convert(smallint,k.constraint_column_id), sysconv(smallint,0)),
       UPDATE_RULE = CONVERT(SMALLINT,CASE OBJECTPROPERTY(F.OBJECT_ID,'CnstIsUpdateCascade') 
                                        WHEN 1 THEN 0
                                        ELSE 1
                                      END),
       DELETE_RULE = CONVERT(SMALLINT,CASE OBJECTPROPERTY(F.OBJECT_ID,'CnstIsDeleteCascade') 
                                        WHEN 1 THEN 0
                                        ELSE 1
                                      END),
       FK_NAME = CONVERT(SYSNAME,OBJECT_NAME(F.OBJECT_ID)),
       PK_NAME = CONVERT(SYSNAME,I.NAME),
       DEFERRABILITY = CONVERT(SMALLINT,7)   -- SQL_NOT_DEFERRABLE
FROM   SYS.ALL_OBJECTS O1,
       SYS.ALL_OBJECTS O2,
       SYS.ALL_COLUMNS C1,
       SYS.ALL_COLUMNS C2,
       SYS.FOREIGN_KEYS F
       INNER JOIN SYS.FOREIGN_KEY_COLUMNS K
         ON (K.CONSTRAINT_OBJECT_ID = F.OBJECT_ID)
       INNER JOIN SYS.INDEXES I
         ON (F.REFERENCED_OBJECT_ID = I.OBJECT_ID
             AND F.KEY_INDEX_ID = I.INDEX_ID)
WHERE  O1.OBJECT_ID = F.REFERENCED_OBJECT_ID
       AND O2.OBJECT_ID = F.PARENT_OBJECT_ID
       AND C1.OBJECT_ID = F.REFERENCED_OBJECT_ID
       AND C2.OBJECT_ID = F.PARENT_OBJECT_ID
       AND C1.COLUMN_ID = K.REFERENCED_COLUMN_ID
       AND C2.COLUMN_ID = K.PARENT_COLUMN_ID    
 
 
---
SELECT
  object_name(parent_object_id),
  object_name(referenced_object_id),
  name
FROM sys.foreign_keys
WHERE parent_object_id = object_id('BookInfoList')
 
----setup variables. Just change 'BookInfoList' to tbl you want
declare @objid int,
    @objname nvarchar(776)
select @objname = 'BookInfoList'   
select @objid = object_id(@objname)
 
if exists (select * from sys.foreign_keys where referenced_object_id = @objid)
    select 'Table is referenced by foreign key' =
        db_name() + '.'
        + rtrim(schema_name(ObjectProperty(parent_object_id,'schemaid')))
        + '.' + object_name(parent_object_id)
        + ': ' + object_name(object_id)
    from sys.foreign_keys 
    where referenced_object_id = @objid 
    order by 1
     
--
SELECT
   'ALTER TABLE ['+sch.name+'].['+referencingTable.Name+'] DROP CONSTRAINT ['+foreignKey.name+']' '[DropCommand]'
FROM sys.foreign_key_columns fk
    JOIN sys.tables referencingTable ON fk.parent_object_id = referencingTable.object_id
    JOIN sys.schemas sch ON referencingTable.schema_id = sch.schema_id
    JOIN sys.objects foreignKey ON foreignKey.object_id = fk.constraint_object_id
    JOIN sys.tables referencedTable ON fk.referenced_object_id = referencedTable.object_id
WHERE referencedTable.name = 'BookInfoList'
 
--外鍵，外鍵關聯錶
SELECT
OBJECT_NAME(parent_object_id) 'Parent table',
c.NAME 'Parent column name',
OBJECT_NAME(referenced_object_id) 'Referenced table',
cref.NAME 'Referenced column name'
FROM
sys.foreign_key_columns fkc 
INNER JOIN
sys.columns c 
   ON fkc.parent_column_id = c.column_id 
      AND fkc.parent_object_id = c.object_id
INNER JOIN
sys.columns cref 
   ON fkc.referenced_column_id = cref.column_id 
      AND fkc.referenced_object_id = cref.object_id  where   OBJECT_NAME(parent_object_id) = 'BookInfoList'
 
--
SELECT t.name AS TableWithForeignKey, fk.constraint_column_id AS FK_PartNo, 
       c.name AS ForeignKeyColumn, o.name AS FK_Name 
  FROM sys.foreign_key_columns AS fk
       INNER JOIN sys.tables AS t ON fk.parent_object_id = t.object_id
       INNER JOIN sys.columns AS c ON fk.parent_object_id = c.object_id 
                                  AND fk.parent_column_id = c.column_id
       INNER JOIN sys.objects AS o ON fk.constraint_object_id = o.object_id
  WHERE fk.referenced_object_id = (SELECT object_id FROM sys.tables 
                                        WHERE name = 'BookInfoList')
  ORDER BY TableWithForeignKey, FK_PartNo;
 
 
 
/*********************************************************************************************
    LIST OUT ALL PRIMARY AND FOREIGN KEY CONSTRAINTS IN A DB OR FOR A SPECIFIED TABLE
*********************************************************************************************/
DECLARE @tblName VARCHAR(255) 
/*******************/
    SET @tblName = NULL-->NULL will return all PK/FK constraints for every table in the database
/*******************/
SELECT PKTABLE_QUALIFIER = CONVERT(SYSNAME,DB_NAME()), 
       PKTABLE_OWNER = CONVERT(SYSNAME,SCHEMA_NAME(O1.schema_id)), 
       PKTABLE_NAME = CONVERT(SYSNAME,O1.name), 
       PKCOLUMN_NAME = CONVERT(SYSNAME,C1.name), 
       FKTABLE_QUALIFIER = CONVERT(SYSNAME,DB_NAME()), 
       FKTABLE_OWNER = CONVERT(SYSNAME,SCHEMA_NAME(O2.schema_id)), 
       FKTABLE_NAME = CONVERT(SYSNAME,O2.name), 
       FKCOLUMN_NAME = CONVERT(SYSNAME,C2.name), 
       -- Force the column to be non-nullable (see SQL BU 325751) 
       KEY_SEQ             = isnull(convert(smallint,K.constraint_column_id),0), 
       UPDATE_RULE = CONVERT(SMALLINT,CASE OBJECTPROPERTY(F.object_id,'CnstIsUpdateCascade')  
                                        WHEN 1 THEN 0 
                                        ELSE 1 
                                      END), 
       DELETE_RULE = CONVERT(SMALLINT,CASE OBJECTPROPERTY(F.object_id,'CnstIsDeleteCascade')  
                                        WHEN 1 THEN 0 
                                        ELSE 1 
                                      END), 
       FK_NAME = CONVERT(SYSNAME,OBJECT_NAME(F.object_id)), 
       PK_NAME = CONVERT(SYSNAME,I.name), 
       DEFERRABILITY = CONVERT(SMALLINT,7)   -- SQL_NOT_DEFERRABLE 
FROM   sys.all_objects O1, 
       sys.all_objects O2, 
       sys.all_columns C1, 
       sys.all_columns C2, 
       sys.foreign_keys F 
       INNER JOIN sys.foreign_key_columns K 
         ON (K.constraint_object_id = F.object_id) 
       INNER JOIN sys.indexes I 
         ON (F.referenced_object_id = I.object_id 
             AND F.key_index_id = I.index_id) 
WHERE  O1.object_id = F.referenced_object_id 
       AND O2.object_id = F.parent_object_id 
       AND C1.object_id = F.referenced_object_id 
       AND C2.object_id = F.parent_object_id 
       AND C1.column_id = K.referenced_column_id
       AND C2.column_id = K.parent_column_id
       AND (   O1.name = @tblName 
            OR O2.name = @tblName
            OR @tblName IS null)
ORDER BY PKTABLE_NAME,FKTABLE_NAME
 
--
SELECT
    ccu.table_name AS SourceTable
    ,ccu.constraint_name AS SourceConstraint
    ,ccu.column_name AS SourceColumn
    ,kcu.table_name AS TargetTable
    ,kcu.column_name AS TargetColumn
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
    INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
        ON ccu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME 
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu 
        ON kcu.CONSTRAINT_NAME = rc.UNIQUE_CONSTRAINT_NAME  
ORDER BY ccu.table_name

　　SQL SEVERE 得到存储过过程的参数

100

101

102

103

104

105

106

107

108

109

110

111

--查询存储过程结构
select * from  sys.sql_modules
select * from sys.procedures 
select * from sysobjects where type = 'p'
 
SELECT  o.type_desc AS ROUTINE_TYPE
       ,QUOTENAME(s.[name]) + '.' + QUOTENAME(o.[name]) AS [OBJECT_NAME]
       ,(LEN(m.definition) - LEN(REPLACE(m.definition, CHAR(10), ''))) AS LINES_OF_CODE
FROM    sys.sql_modules AS m
INNER JOIN sys.objects AS o
        ON m.[object_id] = o.[OBJECT_ID]
INNER JOIN sys.schemas AS s
        ON s.[schema_id] = o.[schema_id]
 
--
select * from sys.objects where type_desc like '%pro%' and name like 'sp%';
 
--
select * from dbo.sysdiagrams
 
select * from sys.sysobjects where type = 'p'
/*
不同的对象用xtype来标识。
C = CHECK 约束
D = 默认值或 DEFAULT 约束
F = FOREIGN KEY 约束
L = 日志
FN = 标量函数
IF = 内嵌表函数
P = 存储过程
PK = PRIMARY KEY 约束（类型是 K）
RF = 复制筛选存储过程
S = 系统表
TF = 表函数
TR = 触发器
U = 用户表
UQ = UNIQUE 约束（类型是 K）
V = 视图
X = 扩展存储过程
*/
 
SELECT obj.Name 存储过程名, sc.TEXT 存储过程内容  
FROM syscomments sc  
INNER JOIN sysobjects obj ON sc.Id = obj.ID  
WHERE --sc.TEXT LIKE '%' + '字段名' + '%'  AND
 TYPE = 'P' 
GO  
 
--
SELECT DISTINCT
TOP 100 PERCENT o.xtype,
CASE o.xtype WHEN 'X' THEN '扩展存储过程' WHEN 'TR' THEN '触发器' WHEN 'PK' THEN
'主键' WHEN 'F' THEN '外键' WHEN 'C' THEN '约束' WHEN 'V' THEN '视图' WHEN 'FN'
THEN '函数-标量' WHEN 'IF' THEN '函数-内嵌' WHEN 'TF' THEN '函数-表值' ELSE '存储过程'
END AS 类型, o.name AS 对象名, o.crdate AS 创建时间, o.refdate AS 更改时间,
c.text AS 声明语句
FROM dbo.sysobjects o LEFT OUTER JOIN
dbo.syscomments c ON o.id = c.id
WHERE (o.xtype IN ('X', 'TR', 'C', 'V', 'F', 'IF', 'TF', 'FN', 'P', 'PK')) AND
(OBJECTPROPERTY(o.id, N'IsMSShipped') = 0)
ORDER BY CASE o.xtype WHEN 'X' THEN '扩展存储过程' WHEN 'TR' THEN '触发器' WHEN
'PK' THEN '主键' WHEN 'F' THEN '外键' WHEN 'C' THEN '约束' WHEN 'V' THEN '视图'
WHEN 'FN' THEN '函数-标量' WHEN 'IF' THEN '函数-内嵌' WHEN 'TF' THEN '函数-表值'
ELSE '存储过程' END DESC
 
 
--
SELECT TOP 100 PERCENT --a.id,
CASE WHEN a.colorder = 1 THEN d.name ELSE '' END AS 表名,
CASE WHEN a.colorder = 1 THEN isnull(f.value, '') ELSE '' END AS 表说明,
a.colorder AS 字段序号, a.name AS 字段名, CASE WHEN COLUMNPROPERTY(a.id,
a.name, 'IsIdentity') = 1 THEN '√' ELSE '' END AS 标识,
CASE WHEN EXISTS
(SELECT 1
FROM dbo.sysindexes si INNER JOIN
dbo.sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid INNER JOIN
dbo.syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid INNER JOIN
dbo.sysobjects so ON so.name = si.name AND so.xtype = 'PK'
WHERE sc.id = a.id AND sc.colid = a.colid) THEN '√' ELSE '' END AS 主键,
b.name AS 类型, a.length AS 长度, COLUMNPROPERTY(a.id, a.name, 'PRECISION')
AS 精度, ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS 小数位数,
CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END AS 允许空, ISNULL(e.text, '')
AS 默认值, ISNULL(g.[value], '') AS 字段说明, d.crdate AS 创建时间,
CASE WHEN a.colorder = 1 THEN d.refdate ELSE NULL END AS 更改时间
FROM dbo.syscolumns a LEFT OUTER JOIN
dbo.systypes b ON a.xtype = b.xusertype INNER JOIN
dbo.sysobjects d ON a.id = d.id AND d.xtype = 'U' AND
d.status >= 0 LEFT OUTER JOIN
dbo.syscomments e ON a.cdefault = e.id LEFT OUTER JOIN
dbo.sysproperties g ON a.id = g.id AND a.colid = g.smallid AND
g.name = 'MS_Description' LEFT OUTER JOIN
dbo.sysproperties f ON d.id = f.id AND f.smallid = 0 AND
f.name = 'MS_Description'
ORDER BY d.name, a.colorder 
 
--
select object_name(id),* from syscomments where text like'%bookinfo%'
 
--获取所有数据库名: 
Select Name FROM Master..SysDatabases order by Name 
--获取某个表的全部字段名：
Select Name FROM SysColumns Where id=Object_Id('BookInfoList')
   
--得到数据库存储过程列表
select * from dbo.sysobjects where OBJECTPROPERTY(id, N'IsProcedure') = 1 order by name
--得到某个存储过程的参数信息：(SQL方法)dbo.proc_Insert_BookInfoList
select * from syscolumns where ID in   
  (SELECT id FROM sysobjects as a  
   WHERE OBJECTPROPERTY(id, N'IsProcedure') = 1    
   and id = object_id(N'[dbo].[proc_Insert_BookInfoList]'))
   

　　sql server

--函数结构
SELECT * FROM INFORMATION_SCHEMA.PARAMETERS
 
SELECT  *
FROM    sys.parameters
WHERE   object_id = OBJECT_ID('f_GetLossesProfitStatusName')

　--外鍵字段和錶名，及数据类型

--外鍵字段和錶名，及数据类型
--ForeignKeyName,SchemaName,ParentTable,ParentColumn,ParentTypename,ReferencedTable,ReferencedColumn,ReferencedTypename
SELECT  obj.name AS [ForeignKeyName],
    sch.name AS [SchemaName],
    tab1.name AS [ParentTable],
    col1.name AS [ParentColumn],
    typ1.name AS [ParentTypename],
    tab2.name AS [ReferencedTable],
    col2.name AS [ReferencedColumn],
    typ2.name AS [ReferencedTypename]
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
    ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
    ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
    ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
    ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.types typ1
    ON col1.user_type_id=typ1.user_type_id
INNER JOIN sys.tables tab2
    ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2
    ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id
 INNER JOIN sys.types typ2
    ON col2.user_type_id= typ2.user_type_id    
 --WHERE tab1.name='BookInfoList' 
 ORDER BY     OBJECT_NAME(fkc.parent_object_id) 

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

--得到数据库存储过程列表
select * from dbo.sysobjects where OBJECTPROPERTY(id, N'IsProcedure') = 1 order by name
--列出所有数据库name,dbid,status,crdate,filename
SELECT * FROM sys.sysdatabases order by name ASC
 
SELECT [dbid],[name],[status],crdate FROM sys.sysdatabases  WHERE [status]=65536 order by [name] ASC
 
 
SELECT * FROM sys.tables  --当前数据库
 
SELECT * FROM LibraryDu.sys.tables
 
SELECT * FROM LibrarySystem.sys.tables
 
SELECT * FROM LibraryDu.sys.views
 
SELECT * FROM LibraryDu.sys.procedures
 
--查询表结构
select    column_name, data_type, is_nullable, 
          character_maximum_length
FROM LibraryDu.INFORMATION_SCHEMA.COLUMNS
where     table_name='BookInfoList';
 
 
--查询视图结构
select    column_name, data_type, is_nullable, 
          character_maximum_length
from      information_schema.columns
where     table_name='View_BookInfoList';
 
select    column_name, data_type, is_nullable, 
          character_maximum_length
FROM LibraryDu.INFORMATION_SCHEMA.COLUMNS
where     table_name='View_BookInfoList';
 
--
SELECT * FROM LibraryDu.dbo.View_BookInfoList
 
 
--函数结构
SELECT * FROM INFORMATION_SCHEMA.PARAMETERS
SELECT * FROM LibraryDu.INFORMATION_SCHEMA.PARAMETERS
SELECT * FROM INFORMATION_SCHEMA.TABLES
 
SELECT  *
FROM    sys.parameters
WHERE   object_id = OBJECT_ID('f_GetLossesProfitStatusName')
 
SELECT * FROM sys.objects
 
SELECT * FROM sys.objects obj,sys.parameters par,sys.types ty WHERE obj.type='FN'
AND obj.object_id=par.object_id
AND par.user_type_id=ty.user_type_id
 
--
SELECT obj.object_id,par.parameter_id, obj.name AS 'funame',par.name AS 'parametersname',par.is_output, ty.name AS 'typename',ty.max_length FROM sys.objects obj,sys.parameters par,sys.types ty WHERE obj.type='FN'
AND obj.object_id=par.object_id
AND par.user_type_id=ty.user_type_id
AND obj.name='f_GetBookReturnEndatetime'
 
 
SELECT  *
FROM    LibraryDu.sys.parameters
WHERE   object_id = OBJECT_ID('f_GetLossesProfitStatusName')
 
--查询存储过程结构
 
SELECT * FROM sys.objects obj,sys.parameters par,sys.types ty WHERE obj.type='P'
AND obj.object_id=par.object_id
AND par.user_type_id=ty.user_type_id
 
--proc_Insert_InventoryProblemListOutput
SELECT obj.object_id,par.parameter_id, obj.name AS 'funame',par.name AS 'parametersname',par.is_output, ty.name AS 'typename',ty.max_length FROM sys.objects obj,sys.parameters par,sys.types ty WHERE obj.type='P'
AND obj.object_id=par.object_id
AND par.user_type_id=ty.user_type_id
AND obj.name='proc_Insert_InventoryProblemListOutput'
 
 
 
select * from sys.sql_modules
select * from sys.procedures 
select * from sys.sysobjects where type = 'p'
 
select * from LibraryDu.sys.sql_modules
select * from LibraryDu.sys.procedures 
select * from LibraryDu.sys.sysobjects where type = 'p'
 
---ROUTINE_TYPE: SQL_STORED_PROCEDURE（存储过程）,SQL_SCALAR_FUNCTION（函数）,VIEW（视图）
SELECT  o.type_desc AS ROUTINE_TYPE
       ,QUOTENAME(s.[name]) + '.' + QUOTENAME(o.[name]) AS [OBJECT_NAME]
       ,(LEN(m.definition) - LEN(REPLACE(m.definition, CHAR(10), ''))) AS LINES_OF_CODE
FROM    sys.sql_modules AS m
INNER JOIN sys.objects AS o
        ON m.[object_id] = o.[OBJECT_ID]
INNER JOIN sys.schemas AS s
        ON s.[schema_id] = o.[schema_id]
 
 
--有輸入，輸出蔘數 isoutparam 0:輸入 1:輸出
  select * from syscolumns where ID in   
  (SELECT id FROM sysobjects as a  
   WHERE OBJECTPROPERTY(id, N'IsProcedure') = 1    
   and id = object_id(N'[dbo].proc_Insert_DepartmentOutput'))
  
 --數据類型錶  
 SELECT * FROM sys.types 
  
 
--得到某个存储过程的参数信息
SELECT  *
FROM    sys.parameters
WHERE   object_id = OBJECT_ID('proc_Insert_BookInfoList')
--is_output 0:輸入  1:輸出
SELECT  *
FROM    sys.parameters
WHERE   object_id = OBJECT_ID('proc_Insert_DepartmentOutput')
 
 
--得到某个存储过程的参数信息
SELECT  *
FROM    syscolumns
WHERE   id = OBJECT_ID('proc_Insert_BookInfoList')
 
exec sp_helptext 'proc_Insert_BookInfoList'--获得存储过程创建脚本

--主键
select * 
from sysobjects 
where xtype='pk' and
   parent_obj in (select id from sysobjects where name='BookInfoList')
 
--外键
select * 
from sysobjects  
WHERE  xtype='f' and
   parent_obj in (select id from sysobjects where name='BookInfoList')
 
---是否主鍵
SELECT col.TABLE_CATALOG AS [Database]
     , col.TABLE_SCHEMA AS Owner
     , col.TABLE_NAME AS TableName
     , col.COLUMN_NAME AS ColumnName
     , col.ORDINAL_POSITION AS OrdinalPosition
     , col.COLUMN_DEFAULT AS DefaultSetting
     , col.DATA_TYPE AS DataType
     , col.CHARACTER_MAXIMUM_LENGTH AS MaxLength
     , col.DATETIME_PRECISION AS DatePrecision
     , CAST(CASE col.IS_NULLABLE
                WHEN 'NO' THEN 0
                ELSE 1
            END AS bit)AS IsNullable
     , COLUMNPROPERTY(OBJECT_ID('[' + col.TABLE_SCHEMA + '].[' + col.TABLE_NAME + ']'), col.COLUMN_NAME, 'IsIdentity')AS IsIdentity
     , COLUMNPROPERTY(OBJECT_ID('[' + col.TABLE_SCHEMA + '].[' + col.TABLE_NAME + ']'), col.COLUMN_NAME, 'IsComputed')AS IsComputed
     , CAST(ISNULL(pk.is_primary_key, 0)AS bit)AS IsPrimaryKey
  FROM INFORMATION_SCHEMA.COLUMNS AS col
       LEFT JOIN(SELECT SCHEMA_NAME(o.schema_id)AS TABLE_SCHEMA
                      , o.name AS TABLE_NAME
                      , c.name AS COLUMN_NAME
                      , i.is_primary_key
                   FROM sys.indexes AS i JOIN sys.index_columns AS ic ON i.object_id = ic.object_id
                                                                     AND i.index_id = ic.index_id
                                         JOIN sys.objects AS o ON i.object_id = o.object_id
                                         LEFT JOIN sys.columns AS c ON ic.object_id = c.object_id
                                                                   AND c.column_id = ic.column_id
                  WHERE i.is_primary_key = 1)AS pk ON col.TABLE_NAME = pk.TABLE_NAME
                                                  AND col.TABLE_SCHEMA = pk.TABLE_SCHEMA
                                                  AND col.COLUMN_NAME = pk.COLUMN_NAME
 WHERE col.TABLE_NAME = 'BookInfoList'
   AND col.TABLE_SCHEMA = 'dbo'
 ORDER BY col.TABLE_NAME, col.ORDINAL_POSITION;

---某個錶的列名,主键，外键
---20150923 涂聚文 SQL Server: Get table primary key and Foreign Key using sql query
 DECLARE @SchemaName VARCHAR(200),@TableName VARCHAR(200)
SET @SchemaName='dbo'
SET @TableName='BookInfoList'
SELECT
              clmns.[name] AS [ColumnName],
              usrt.[name] AS [DataType],
              ISNULL(baset.[name], N'') AS [SystemType],
              CAST(CASE WHEN baset.[name] IN (N'char', N'varchar', N'binary', N'varbinary', N'nchar', N'nvarchar') THEN clmns.prec ELSE clmns.length END AS int) AS [Length],
              CAST(clmns.xprec AS tinyint) AS [NumericPrecision],
              CAST(clmns.xscale AS int) AS [NumericScale],
              CASE CAST(clmns.isnullable AS bit) WHEN 1 THEN 'YES' ELSE 'NO' END AS [Nulldata],
              defaults.text AS [DefaultValue],
              CAST(COLUMNPROPERTY(clmns.id, clmns.[name], N'IsIdentity') AS int) AS [Identity],
              CAST(COLUMNPROPERTY(clmns.id, clmns.[name], N'IsRowGuidCol') AS int) AS IsRowGuid,
              CAST(COLUMNPROPERTY(clmns.id, clmns.[name], N'IsComputed') AS int) AS IsComputed,
              CAST(COLUMNPROPERTY(clmns.id, clmns.[name], N'IsDeterministic') AS int) AS IsDeterministic,
              CAST(CASE COLUMNPROPERTY(clmns.id, clmns.[name], N'IsIdentity') WHEN 1 THEN IDENT_SEED(QUOTENAME(SCHEMA_NAME(tbl.uid)) + '.' + QUOTENAME(tbl.[name])) ELSE 0 END AS nvarchar(40)) AS [IdentitySeed],
              CAST(CASE COLUMNPROPERTY(clmns.id, clmns.[name], N'IsIdentity') WHEN 1 THEN IDENT_INCR(QUOTENAME(SCHEMA_NAME(tbl.uid)) + '.' + QUOTENAME(tbl.[name])) ELSE 0 END AS nvarchar(40)) AS [IdentityIncrement],
              cdef.[text] AS ComputedDefinition,
              clmns.[collation] AS Collation,
              CAST(clmns.colid AS int) AS ObjectId,
              CAST(ISNULL(pk.is_primary_key, 0)AS bit)AS IsPrimaryKey,
              CAST(ISNULL(IsForeignKey,0) AS bit) AS IsForeignKey
            FROM
              sys.sysobjects AS tbl
              INNER JOIN sys.syscolumns AS clmns ON clmns.id=tbl.id
              LEFT JOIN sys.systypes AS usrt ON usrt.xusertype = clmns.xusertype
              LEFT JOIN sys.sysusers AS sclmns ON sclmns.uid = usrt.uid
              LEFT JOIN sys.systypes AS baset ON baset.xusertype = clmns.xtype and baset.xusertype = baset.xtype
              LEFT JOIN sys.syscomments AS defaults ON defaults.id = clmns.cdefault
              LEFT JOIN sys.syscomments AS cdef ON cdef.id = clmns.id AND cdef.number = clmns.colid
              LEFT JOIN(SELECT SCHEMA_NAME(o.schema_id)AS TABLE_SCHEMA --是否主键
                      , o.name AS TABLE_NAME
                      , c.name AS COLUMN_NAME
                      , i.is_primary_key
                   FROM sys.indexes AS i JOIN sys.index_columns AS ic ON i.object_id = ic.object_id
                                                                     AND i.index_id = ic.index_id
                                         JOIN sys.objects AS o ON i.object_id = o.object_id
                                         LEFT JOIN sys.columns AS c ON ic.object_id = c.object_id
                                                                   AND c.column_id = ic.column_id
                  WHERE i.is_primary_key = 1)AS pk ON tbl.name = pk.TABLE_NAME
                                                    AND pk.TABLE_SCHEMA=@SchemaName
                                                  AND pk.COLUMN_NAME=clmns.name
               --是否外键                                   
               LEFT JOIN(SELECT 
    sch.name AS [schema_name],
    tab1.name AS [table],
    col1.name AS [column],
    tab2.name AS [referenced_table],
    col2.name AS [referenced_column],
    CAST(ISNULL(1, 0)AS bit)AS IsForeignKey
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
    ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
    ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
    ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
    ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id 
INNER JOIN sys.tables tab2
    ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2
    ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id) AS  fk ON tbl.name=fk.[table]
    AND fk.schema_name=@SchemaName
    AND fk.[column]=clmns.name   
    --                                 
            WHERE
              (tbl.[type] = 'U' OR tbl.[type] = 'S')
              AND SCHEMA_NAME(tbl.uid) = @SchemaName
              AND tbl.[name] = @TableName
            ORDER BY
              clmns.colorder
GO

---所有用户表(主键，外键，描述等信息）涂聚文 20150924 Geovin Du
SELECT 
              tbl.[name] AS [TableName],
              clmns.[name] AS [ColumnName],
              usrt.[name] AS [DataType],
              ISNULL(baset.[name], N'') AS [SystemType],
              CAST(CASE WHEN baset.[name] IN (N'char', N'varchar', N'binary', N'varbinary', N'nchar', N'nvarchar') THEN clmns.prec ELSE clmns.length END AS int) AS [Length],
              CAST(clmns.xprec AS tinyint) AS [NumericPrecision],
              CAST(clmns.xscale AS int) AS [NumericScale],
              CASE CAST(clmns.isnullable AS bit) WHEN 1 THEN 'YES' ELSE 'NO' END AS [Nulldata],
              defaults.text AS [DefaultValue],
              CAST(COLUMNPROPERTY(clmns.id, clmns.[name], N'IsIdentity') AS int) AS [Identity],
              CAST(COLUMNPROPERTY(clmns.id, clmns.[name], N'IsRowGuidCol') AS int) AS IsRowGuid,
              CAST(COLUMNPROPERTY(clmns.id, clmns.[name], N'IsComputed') AS int) AS IsComputed,
              CAST(COLUMNPROPERTY(clmns.id, clmns.[name], N'IsDeterministic') AS int) AS IsDeterministic,
              CAST(CASE COLUMNPROPERTY(clmns.id, clmns.[name], N'IsIdentity') WHEN 1 THEN IDENT_SEED(QUOTENAME(SCHEMA_NAME(tbl.uid)) + '.' + QUOTENAME(tbl.[name])) ELSE 0 END AS nvarchar(40)) AS [IdentitySeed],
              CAST(CASE COLUMNPROPERTY(clmns.id, clmns.[name], N'IsIdentity') WHEN 1 THEN IDENT_INCR(QUOTENAME(SCHEMA_NAME(tbl.uid)) + '.' + QUOTENAME(tbl.[name])) ELSE 0 END AS nvarchar(40)) AS [IdentityIncrement],
              cdef.[text] AS ComputedDefinition,
              clmns.[collation] AS Collation,
              CAST(clmns.colid AS int) AS ObjectId,
              CAST(ISNULL(pk.is_primary_key, 0)AS bit)AS IsPrimaryKey,
              CAST(ISNULL(IsForeignKey,0) AS bit) AS IsForeignKey,
              ISNULL(pro.Description,'') AS ColumnDescription
            FROM
              sys.sysobjects AS tbl
              INNER JOIN sys.syscolumns AS clmns ON clmns.id=tbl.id
              LEFT JOIN sys.systypes AS usrt ON usrt.xusertype = clmns.xusertype
              LEFT JOIN sys.sysusers AS sclmns ON sclmns.uid = usrt.uid
              LEFT JOIN sys.systypes AS baset ON baset.xusertype = clmns.xtype and baset.xusertype = baset.xtype
              LEFT JOIN sys.syscomments AS defaults ON defaults.id = clmns.cdefault
              LEFT JOIN sys.syscomments AS cdef ON cdef.id = clmns.id AND cdef.number = clmns.colid
              LEFT JOIN(SELECT SCHEMA_NAME(o.schema_id)AS TABLE_SCHEMA --是否主键
                      , o.name AS TABLE_NAME
                      , c.name AS COLUMN_NAME
                      , i.is_primary_key
                   FROM sys.indexes AS i JOIN sys.index_columns AS ic ON i.object_id = ic.object_id
                                                                     AND i.index_id = ic.index_id
                                         JOIN sys.objects AS o ON i.object_id = o.object_id
                                         LEFT JOIN sys.columns AS c ON ic.object_id = c.object_id
                                                                   AND c.column_id = ic.column_id
                  WHERE i.is_primary_key = 1)AS pk ON tbl.name = pk.TABLE_NAME
                                                    AND pk.TABLE_SCHEMA='dbo'
                                                  AND pk.COLUMN_NAME=clmns.name
               --是否外键                                   
               LEFT JOIN(SELECT 
    sch.name AS [schema_name],
    tab1.name AS [table],
    col1.name AS [column],
    tab2.name AS [referenced_table],
    col2.name AS [referenced_column],
    CAST(ISNULL(1, 0)AS bit)AS IsForeignKey
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
    ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
    ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
    ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
    ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id 
INNER JOIN sys.tables tab2
    ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2
    ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id) AS  fk ON tbl.name=fk.[table]
    AND fk.schema_name='dbo'
    AND fk.[column]=clmns.name
 --备注
    LEFT JOIN (SELECT obj.name AS [TableName], col.name AS [ColumnName],pro.value AS [Description] FROM sys.columns col,sys.extended_properties pro,sys.objects obj
WHERE col.object_id=pro.major_id
AND col.column_id=pro.minor_id
AND obj.object_id=col.object_id) AS pro ON tbl.name=pro.TableName AND clmns.name=pro.ColumnName
WHERE
           tbl.[type] = 'U'    ---(tbl.[type] = 'U' OR tbl.[type] = 'S')
            --AND SCHEMA_NAME(tbl.uid) = @SchemaName
            --AND tbl.[name] = @TableName
            ORDER BY
              clmns.colorder,tbl.name
GO
              

　　https://docs.oracle.com/cd/B19306_01/server.102/b14220/schema.htm

Oracle Schema Browser:

posted @ 2015-03-21 14:51 ®Geovin Du Dream Park™ 阅读(1302) 评论(0) 编辑收藏举报

®Geovin Du Dream Park™

why we only heard about haves and have-nots, but we did'nt heard about doers and doer-nots. 人生是一种心境,生活是一种艺术,成功是一种心态,幸福是一种感觉,竞争是一种建构,情感是一种容合.学习是一种成长.

sql:sql server,MySQL,PostgreSQL的表,视图,存储过程结构查询

公告

搜索

常用链接

我的标签

积分与排名

随笔分类

随笔档案

文章分类

相册

捷为工作室

阅读排行榜

评论排行榜

推荐排行榜

最新评论