There are many times when I want to know which tables in a database actually have data. I have come up with a slightly unconventional solution to this problem in SQL Server. Yes, I know it can be done programatically with "insert-scripting-language-here", but this was a case where I needed to know, had no web servers set up on my machine, and just plain wanted to get it done. Further complicating things was this database is on SQL 2000, so the power shell option wasn't available either.
I expect to be heckled for this solution, so bring it.
Start by running the built in stored procedure sp_tables.
Highlight and copy these columns from the results:
TABLE_OWNER
TABLE_NAME
TABLE_TYPE
Open excel (yes, that's right)
Paste your copied data.
Delete all system tables and views rows
Insert two new columns, one to the far left, and one to the left of the second column.
In column A type
select count(*) from
-- you need a space after "from"
In column C put a period
Copy those rows down the length of the table. The easiest way is to grab the loewest right corner of those cells, right click ad drag your mouse to the last ow of data and choose "copy cells"
Go to column F and concatenate A through D
=CONCATENATE(A2,B2,C2,D2)
Now copy that cell all the way to the bottom.
Now highlight the whole column and copy it
Go back to Query Analyzer. Change the results window to text mode.
Paste in your awesome SQL statements. Execute this bad boy. Don't do this in production, or your DBA will kill you. If you must do it in produciton use this instead of count:
SELECT rows FROM sysindexes
WHERE id = OBJECT_ID('table_name') AND indid < 2
Yes, I should have done it this way here, but it was more typing, and this solution is about human efficiency. The efficiency of machines is not a problem we are solving today.
Copy the result set and open your favorite text editor, and find-replace your way down to numbers only.
I did it in Homesite (yep, keep laughing)
Copy and paste your list of numbers back into a new column in excel, and blammo, you've got your answer.
Look how fine this is!
2 Comments
Eric Belair wrote on 07/06/10 7:29 AM
Morgan, you never cease to amaze me. I miss working with you....`
Chris wrote on 07/03/10 10:50 AM
Not sure if it works in 2000 but try this sql (works in 2005 & 2008)use db_name
drop TABLE #TableSizes
CREATE TABLE #TableSizes
(
TableName NVARCHAR(255),
TableRows INT,
ReservedSpaceKB VARCHAR(20),
DataSpaceKB VARCHAR(20),
IndexSizeKB VARCHAR(20),
UnusedSpaceKB VARCHAR(20)
)
INSERT INTO #TableSizes
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT * FROM #TableSizes
ORDER BY TableRows DESC