Get_Object_Info_SQL_Server.sql

	CREATE TABLE #TEMP1 (RowNo SMALLINT IDENTITY(1,1), SchemaName VARCHAR(100), ObjectName VARCHAR(100), ObjectType VARCHAR(100), Row_Count BIGINT, Table_Size varchar(50), IndexSize varchar(50), CreateDate DATETIME, ModifyDate DATETIME, ReportGeneratedOn DATETIME)
	CREATE TABLE #TEMP2 (NAME VARCHAR(500), ROWS VARCHAR(500), RESERVED VARCHAR(500), DATA VARCHAR(500), IndexSize VARCHAR(500), UNUSED VARCHAR(500))

	INSERT INTO #TEMP1 (SchemaName, ObjectName, ObjectType, CreateDate, ModifyDate)
	SELECT	'[' + S.Name +']', '['+ O.Name +']', O.type_desc AS OBJECT_TYPE, O.CREATE_DATE, O.MODIFY_DATE 
	FROM	SYS.ALL_OBJECTS O
	JOIN	SYS.SCHEMAS S
		ON	O.SCHEMA_ID = S.SCHEMA_ID
		AND	O.IS_MS_SHIPPED <> 1
		AND	O.Type IN ('P', 'U', 'V')
	ORDER BY S.Name, O.Type_desc, O.NAME
	
	DECLARE @Id SMALLINT = 1
	DECLARE @Loop SMALLINT, @SchemaName VARCHAR(100), @TableName VARCHAR(100), @SQL_String VARCHAR(4000)
		SET @loop = (SELECT COUNT(1) FROM #TEMP1)
		WHILE @Id <= @Loop
			BEGIN
			   SELECT	@SchemaName = SchemaName
					,	@TableName = LTRIM(RTRIM(ObjectName))
			   FROM		#TEMP1 
			   WHERE	RowNo = @Id
					AND	ObjectType = 'USER_TABLE'
           
			   SET @SQL_String = 'INSERT INTO #TEMP2
				EXEC SP_SPACEUSED '''+@SchemaName+'.'+@TableName+''''

			   PRINT @SQL_String
			   EXEC (@SQL_String)

			   UPDATE	T
			   SET		T.ROW_COUNT = D.ROWS
					,   T.Table_Size = D.DATA
					,   T.IndexSize = D.IndexSize
				FROM    #TEMP1 T
				JOIN    #TEMP2 D
					ON  T.ObjectName = '['+ D.NAME + ']'
					AND '['+ D.NAME + ']' = @TableName

			   SET @Id = @Id + 1
			END

		UPDATE  #TEMP1
		SET    TABLE_SIZE = REPLACE(TABLE_SIZE, ' KB', '')
			,  IndexSize = REPLACE(IndexSize, ' KB', '')

		SELECT  @@SERVERNAME ServerName, DB_NAME() DatabaseName, SchemaName, ObjectName, ObjectType, Row_Count
			,   CAST((CAST(TABLE_SIZE AS BIGINT) + CAST(IndexSize AS BIGINT))/ 1024.00 AS NUMERIC(18, 2)) AS TableSize_MB
			,   CreateDate, ModifyDate, GETDATE() ReportGeneratedOn
		FROM    #TEMP1
		--WHERE    ROW_COUNT > 0
		ORDER BY SchemaName, ObjectType, ROW_COUNT DESC

	DROP TABLE #TEMP1
	DROP TABLE #TEMP2