There is a stored procedure to retrieve the information on the creation and modification dates and on the size usage of the every table of a database. This takes the input as the table name.

The syntax for this as below,

EXEC sp_spaceused “db_table_name”

But to revieve the meta data information of every table of a single database we can make use of the iteration procedure ‘sp_msForEachTable’.

And so we can give the query as below which gives the metadata for every table of the selected database but as multiple table results and not as a single view.

EXEC sp_msForEachTable ‘EXEC sp_spaceused ”?””
To achieve the result as a single view we can follow the following steps.

First create a temporary table,

CREATE TABLE temp_table
(
[name] NVARCHAR(128),
[rows] CHAR(11),
reserved VARCHAR(18),
data VARCHAR(18),
index_size VARCHAR(18),
unused VARCHAR(18)
)

Then insert the result of the above defined procedures,

INSERT #t EXEC sp_msForEachTable ‘EXEC sp_spaceused ”?””

Now we can view the result of all the tables in a single view as and can drop the table after use to avoid use of disk space,

Select * from temp_table
DROP TABLE temp_table

So we can have the required result of all the meta details of the single database as a single shot view. Thanks for reading this post. Feel free to share your views and comments.

Advertisements

How to enable a select copy of headers?

Initially i was struggling to get this option done, and found it hard to type the headers manually where ever i copied my query results. So here i have the solution for you.

Its a simple flow of steps to follow to enable this to work. As usual on your SQL server just follow the below steps and you get your headers from the query result to be copied into your clipboard for a paste to any where.

Select the Tools menu on the top of your window. Then select the Options from tools menu.

Now appears the Options window, where on the left we have the menu Query Results -> SQL Server -> Results to Grid.

Click the ‘Results to Grid’ option and on right pane of the Options window we have the option ‘Include column headers when copying or saving the results’.
Just check this option and we are done, to enable the copying of headers along with the query result datas.

Thanks for reading tis post. Feel free to share your comments and views on the same. Have fun learning 🙂

Hello all, its been months since i have written a blog. Its never late than never. So let me start it right away with a post on SQL. This is my first post on SQL and hope you all find it useful.

I am today giving a post on getting the size of the tables in your database. Its very simple and we have a system defined procedure that makes your job easier. The syntax to get this is,

exec sp_spaceused ‘TABLE NAME’

You shall get the metdata information on your table as you query the above.

Lets try to get the size information on all the tables of your whole DB. Wondering how? This is even simpler, as usual to make the job easy we have got a time saving procedure to do it for us. Lets make a query to get the size of all tables,

EXEC sp_msForEachTable ‘EXEC sp_spaceused ”?”’

The question mark replaces the table names of our DB. This query returns mutiple tables of the metadata like size of every table in our database. To get a view of the metadata as a single table the following set of query can be run together,

/* CREATE A TABLE TO STORE THE INFORMATION */

CREATE TABLE #t
(
[name] NVARCHAR(128),
[rows] CHAR(11),
reserved VARCHAR(18),
data VARCHAR(18),
index_size VARCHAR(18),
unused VARCHAR(18)
)

/*INSERTING THE INFORMATION RETRIEVED TO THE TABLE*/

INSERT #t EXEC sp_msForEachTable ‘EXEC sp_spaceused ”?”’

/*VIEW THE INFORMATION FROM THE TABLE & DROPPING THE TABLE*/

SELECT * FROM #t

DROP TABLE #t /*OPTIONAL TO DROP*/

Hope this post was useful to you all. Thanks and feel free to comment and share your ideas.

%d bloggers like this: