3

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]
Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
user3844877
  • 493
  • 4
  • 9
  • 18
  • 1
    There are differences between schema and owner. Read [this link](http://www.sqlteam.com/article/understanding-the-difference-between-owners-and-schemas-in-sql-server) for more details. If you need to read the `Schema.Table.Columns` list, it's better to use the dedicated `INFORMATION_SCHEMA` (which contains a _TABLES_ and a _COLUMNS_ view) SQL-92 standard views. – Alessandro Alpi Aug 06 '14 at 10:24
  • I wanted to use the object id which the information schema doesnt have . I have to look for a particular table name and column name provided by a user and see if he/she have permission to access the table though schema – user3844877 Aug 06 '14 at 10:58
  • You can use the OBJECT_ID('schema.name') funtction in order to get the table object_id. – Alessandro Alpi Aug 06 '14 at 12:35

2 Answers2

4

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

Hope this helps!

--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
Jeremy F.
  • 1,778
  • 11
  • 51
  • 86
3

Have you tried using the built-in sp_tables stored procedure? See http://msdn.microsoft.com/en-us/library/ms186250.aspx for usage.

casperOne
  • 73,706
  • 19
  • 184
  • 253
Alastair Campbell
  • 648
  • 4
  • 18
  • I actualy have to check if a table name , column name exists supplied by an owner(schema owner in which the table exists) . – user3844877 Aug 06 '14 at 10:54
  • can i supply the required table name and column name from inserted trigger and have sp_table return the answer ? – user3844877 Aug 06 '14 at 11:03
  • so I can get the table_name and owner using the code below but how do I get the column name ? `EXEC sp_tables @table_name = '%', @table_owner = '%'` – user3844877 Aug 06 '14 at 11:06
  • You can pass parameters to sp_tables like `exec sp_tables ` and you will get the details just for that table.
     – Alastair Campbell Aug 06 '14 at 11:07