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"