列出表名称，所有者，架构和SQL Server数据库中的列 [英] List table names, owner, schema and columns in SQL server database

问题描述

在SQL SERVER中，如何获取所有表名，列名和所有者的列表?
我已经做到了，但是在哪里可以获得所有者详细信息?

In SQL SERVER how can I get a list of all table names, column names and owners?
I have done this but where do I get the OWNER details?

SELECT t.name AS tableName, 
       s.name SchemaName 
FROM   sys.tables AS t 
       INNER JOIN sys.schemas AS s 
               ON t.[schema_id] = s.[schema_id] 

推荐答案

请注意，"TABLE_OWNER"与"SCHEMA Owner"相同，并且"TABLE_TYPE"将标识该项目是否为表或视图.

Note that "TABLE_OWNER" is that same as "SCHEMA Owner" and "TABLE_TYPE" will identify if the item is a table OR view.

希望这会有所帮助！

--This will return all tables, table owners and table types for all database(s) that are NOT 'Offline'
--Offline database information will not appear

Declare @temp_table table(
DB_NAME varchar(max),
TABLE_OWNER varchar(max),
TABLE_NAME varchar(max),
TABLE_TYPE varchar(max),
REMARKS varchar(max)
)

INSERT INTO @temp_table (DB_NAME, TABLE_OWNER, TABLE_NAME, TABLE_TYPE,REMARKS)

EXECUTE master.sys.sp_MSforeachdb 'USE [?]; EXEC sp_tables'

SELECT * 
FROM @temp_table 
--Uncomment below if you are seaching for 1 database
--WHERE DB_NAME = '<Enter specific DB Name>'

--For all databases other than 'System Databases'
WHERE DB_NAME not in ('master','model','msdn','tempdb')
order by 1

这篇关于列出表名称，所有者，架构和SQL Server数据库中的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！