Skip to content Skip to sidebar Skip to footer

List Table Names, Owner, Schema And Columns In Sql Server Database

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 Sc

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 appearDeclare@temp_table table(
DB_NAME varchar(max),
TABLE_OWNER varchar(max),
TABLE_NAME varchar(max),
TABLE_TYPE varchar(max),
REMARKS varchar(max)
)

INSERTINTO@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 notin ('master','model','msdn','tempdb')
orderby1

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.

I would have added this as a comment, but I would apparently need 50 reputation to do so.

Post a Comment for "List Table Names, Owner, Schema And Columns In Sql Server Database"