List table names, owner, schema and columns in SQL server database

Question

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] 

Solution 1

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

Solution 2

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

Comments

[user3844877]

I actualy have to check if a table name , column name exists supplied by an owner(schema owner in which the table exists) .

[user3844877]

can i supply the required table name and column name from inserted trigger and have sp_table return the answer ?

[user3844877]

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 = '%'

[Alastair Campbell]

You can pass parameters to sp_tables like exec sp_tables <table name> and you will get the details just for that table.