All Database User objects information

Posted: March 1, 2012 by Virendra Yaduvanshi in Database Administrator
Tags: , , ,

Hi Guys, One Day I have to find out all Databases objects like User Table, Procedure & Views.

Below are the code to find out objects information like ObjectName,Type,Create_date Modify_date, Rows,Column_Count,Data_Size,index_size etc.

/* Create Table for Collect Data from All DATABASES */

createtable#VirendraTest(

TableNamevarchar(50),

ObjectTypevarchar(50),

CreateDatedatetime,

ModifyDatedatetime,

RowsCountbigint,

ColumnCounttint,

DataSizevarchar(50),

IndexSizevarchar(50))

/* Process Start to get data from Indivisual Databases */

Declare@sqltxtvarchar(50)

Declare@dbnamevarchar(50)

DECLAREVirendraCurCURSORFORSELECTNamefromSys.databaseswheredatabase_id>4

openvirendraCur

fetchnextfromvirendracurinto@dbname

WHILE@@FETCH_STATUS= 0

begin

set@sqltxt=‘USE ‘+‘[‘+@dbname+‘]’

print@sqltxt

exec (@sqltxt)

CREATETABLE#Virendratemp(

table_namesysname,

row_countINT,

reserved_sizeVARCHAR(50),

data_sizeVARCHAR(50),

index_sizeVARCHAR(50),

unused_sizeVARCHAR(50))

INSERT#Virendratemp

EXECsp_msforeachtable‘sp_spaceused “?”‘

insert#Virendratest

Selectaa.*,bb.col_count,bb.Data_Size,bb.index_sizefrom (

Selectob.Name,ob.type_descType,ob.create_date,ob.modify_date,pt.rowsfromsys.objectsob

leftjoinsys.partitionsptonob.object_id=pt.object_id

whereob.typein(‘U’,‘V’,‘P’))aa

leftjoin(SELECTa.Table_Name,

a.Row_count,

COUNT(*)ASCol_count,

a.Data_size,a.index_size

FROM#Virendratempa

INNERJOINinformation_schema.columnsb

ONa.table_namecollatedatabase_default

=b.table_namecollatedatabase_default

GROUPBYa.table_name,a.row_count,a.data_size,a.index_size)bb

onaa.name=bb.table_name

droptable#Virendratemp

set@sqltxt=‘USE ‘+‘[‘+@dbname+‘]’

exec (@sqltxt)

fetchnextfromvirendracurinto@dbname

end

closevirendracur

deallocatevirendracur

/* Display Collected Data */

Select*from#VirendraTest

/* Drop Temp Table */

DropTable#VirendraTest

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s