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
WikiDBA 