• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 616
  • Last Modified:

Generate table owner list for all the tables in a database

I am using SQL 2008 please how do I write a query to generate table owner list and sizes for all the tables in a database. The query headings will be TABLENAME, OWNER, SIZE
0
agbelo
Asked:
agbelo
  • 5
  • 5
1 Solution
 
jorge_torizResearch & Development ManagerCommented:
Well, I think this can help you as a start point:

SELECT SCHEMA_NAME(T.[schema_id]) + '.' + T.name, USER_NAME(S.principal_id) AS Owner
FROM sys.tables T
INNER JOIN sys.schemas S
ON T.[schema_id] = S.[schema_id]
ORDER BY T.name

In SQL Server 2008 the owner is the one that own the schema where the object resides.
0
 
jorge_torizResearch & Development ManagerCommented:
Well, in the preceeding sentence, I must fix: "the owner (in tables) is the one that own the schema where the object resides"
0
 
agbeloAuthor Commented:
That was surely very helpful so what about if this was for a SQL 2000 server? How would the code look?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
agbeloAuthor Commented:
Solution will be complete when my follow up question is answered
0
 
agbeloAuthor Commented:
And also include the table sizes column in your answer
0
 
jorge_torizResearch & Development ManagerCommented:
Ups... I can't really remember anything about sql 2000, let me find the system views and verify if this information can be get
0
 
jorge_torizResearch & Development ManagerCommented:
Well, you can get the estimated row size with this one:

SELECT SCHEMA_NAME(T.[schema_id]) + '.' + T.name, USER_NAME(S.principal_id) AS Owner, SUM(C.max_length) AS EstimatedRowSize
FROM sys.tables T
INNER JOIN sys.schemas S
ON T.[schema_id] = S.[schema_id]
INNER JOIN sys.columns C
ON T.[object_id] = C.[object_id]
GROUP BY T.[schema_id], T.name, S.principal_id
ORDER BY T.name
0
 
agbeloAuthor Commented:
Please I asked for the estimated table size for each table and make values return in MB
0
 
agbeloAuthor Commented:
I actuallly have a query that would do what I need I just need to re-engineer it to give me the object owners. Maybe you can help me here. Below is the query I am using and it works in both SQL 2000 and SQL 2008. Please help to modify it to return the object owners

SELECT so.[name], used AS "# of Pages", rows AS "# of Rows", (used * 8) /
1024 AS "# of MB"  
FROM sysobjects so JOIN sysindexes si ON so.id = si.id
WHERE so.xtype = 'U'
ORDER BY used DESC
0
 
jorge_torizResearch & Development ManagerCommented:
You can use this query to get the table size:

SELECT T.name AS TableName, SUM(P.record_count * P.avg_record_size_in_bytes) AS TotalSize
FROM sys.tables T
CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') AS P
WHERE T.[object_id] = P.[object_id]
GROUP BY T.name
ORDER BY T.name
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now