按模式排序的SQL语句以获取表名、视图和存储过程

Question

有没有一条SQL语句可以列出MS SQL Server数据库中所有表、视图和存储过程的名称，并按模式名称排序？

我想从这个列表中生成一个Excel电子表格，包含以下列：模式、类型（表、视图、存储过程）和名称。

- spong

Answer 1

以下是您所要求的内容：

select 
    s.name as [Schema], 
    o.type_desc as [Type],
    o.name as [Name] 
from
    sys.all_objects o
    inner join sys.schemas s on s.schema_id = o.schema_id 
where
    o.type in ('U', 'V', 'P') -- tables, views, and stored procedures
order by
    s.name

- Jeffrey L Whitledge

Answer 2

这是我最终使用的SQL语句：

SELECT   
      CASE so.type
           WHEN 'U' THEN 'table'
           WHEN 'P' THEN 'stored proc'
           WHEN 'V' THEN 'view'
      END AS [type],
      s.name AS [schema],
      so.[name] AS [name]
FROM sys.sysobjects so
JOIN sys.schemas s
ON so.uid = s.schema_id
WHERE so.type IN ('U', 'P', 'V')
ORDER BY [type], [schema], [name] asc

- spong

Answer 3

您可以使用系统视图 INFORMATION_SCHEMA.TABLES、INFORMATION_SCHEMA.VIEWS 和 INFORMATION_SCHEMA.COLUMNS 来创建查询

编辑：还有用于存储过程的 INFORMATION_SCHEMA.ROUTINES

- codingbadger

Answer 4

开始

select * from sys.sysobjects

编辑：现在有模式了

select * from sys.sysobjects
inner join sys.schemas on sys.sysobjects.uid = sys.schemas.schema_id

- Arthur

Answer 5

尝试：

SELECT
    ROUTINE_SCHEMA,ROUTINE_TYPE ,ROUTINE_NAME
    FROM INFORMATION_SCHEMA.ROUTINES
UNION 
SELECT 
    TABLE_SCHEMA,TABLE_TYPE,TABLE_NAME 
    FROM INFORMATION_SCHEMA.TABLES
UNION 
SELECT 
    TABLE_SCHEMA,'VIEW' ,TABLE_NAME
    FROM INFORMATION_SCHEMA.VIEWS
    ORDER BY ROUTINE_SCHEMA,ROUTINE_TYPE ,ROUTINE_NAME

- KM.

Answer 6

如果您需要深入挖掘列及其数据类型，请尝试使用此SQL。您可以使用sysobjects.xtype的以下选项（U = 用户表，P = 存储过程，V = 视图）。

SELECT   object_type = sysobjects.xtype,
     table_name = sysobjects.name,
     column_name = syscolumns.name,
     datatype = systypes.name,
     length = syscolumns.length
FROM sysobjects 
JOIN syscolumns ON sysobjects.id = syscolumns.id
JOIN systypes ON syscolumns.xtype=systypes.xtype
   WHERE sysobjects.xtype='U' AND 
syscolumns.name LIKE '%[column_name_here]%' 
AND sysobjects.name LIKE '%[table or Stored Proc Name]%' 
ORDER BY sysobjects.name,syscolumns.colid

- Kashif Awan

Answer 7

有一些内置的系统视图可以使用：

• sys.views
• sys.tables
• sys.procedures
• sys.schema

在 MSDN 上可以找到更多关于目录视图的信息。

- Anders Abel