July 28, 2010 at 9:54 am
I've found many, but none include table owner. I've also tried playing with the different codes to get the data from a few sys tables and joins with no luck.
any help appreciated!
¤ §unshine ¤
July 28, 2010 at 12:12 pm
NOt sure I understood your question completely put this will return all table names and associated schemas.
SELECT SCHEMA_NAME(schema_id) + '.' + Name FROM sys.objects WHERE type_desc = 'USER_TABLE'
Using Nortwind db as an example - partial returns (Out of 75) are
Production.ProductProductPhoto
Sales.StoreContact
Person.Address
Production.ProductReview
Production.TransactionHistory
Person.AddressType
dbo.InvCtrl
Production.ProductSubcategory
dbo.AWBuildVersion
Production.TransactionHistoryArchive
Purchasing.ProductVendor
Production.BillOfMaterials
Production.UnitMeasure
dbo.SalesByPerson
Purchasing.Vendor
Purchasing.PurchaseOrderDetail
Person.Contact
dbo.sysdiagrams
Purchasing.VendorAddress
July 28, 2010 at 1:27 pm
I apologize for not being clear. I am looking for a script that gives me the schema, name of the table, and rowcount for all tables in a database. I've found rowcount scripts but none with schemas included. I am unable to open your script.
¤ §unshine ¤
July 28, 2010 at 4:42 pm
SELECT OBJECT_SCHEMA_NAME(P.object_id), P.* FROM sys.partitions AS P
You may have more than one row for a table in the output - This is based on the number of indexes on the table.
The result also includes system tables. You can join with sys.objects to filter those out
July 29, 2010 at 7:08 am
sunshine-587009
I am unable to open your script.
Do you mean unable to copy and paste the T-SQL into SSMS for testing or
it can be cut and pasted but returns no results?
July 29, 2010 at 1:52 pm
I was unable to see any code in the original post from you to run on my end. But I thank you very much for reposting it. Here is the script I used for the resultset I needed, with the help of your script. Thank you so much!
SELECT
OBJECT_SCHEMA_NAME(p.object_id) AS Owner,
o.Name as TableName,
ddps.Row_Count
FROM sys.partitions AS p INNER JOIN
sys.indexes AS i INNER JOIN
sys.objects AS o ON i.object_id = o.object_id INNER JOIN
sys.dm_db_partition_stats AS ddps ON i.object_id = ddps.object_id AND i.index_id = ddps.index_id ON p.object_id = o.object_id
WHERE (i.index_id < 2)
AND (o.is_ms_shipped = 0)
ORDER BY o.NAME
¤ §unshine ¤
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply