SQL Script Help

Question

SQL Script Help

EMtwo

Hall of Fame

Points: 3498
More actions
August 10, 2020 at 7:35 pm

#3778739

I am looking for a script that can return the following values for each of the databases in my instance: Object ID, Object Type, Database Name, Table Name, Schema Name, Index Name, Table Create Date, Table Modified Date, Row Count, Total Pages, Used Pages, Data Pages, Total KB, Total MB, Total GB, Used KB, Used MB, Used GB.
Your help will be greatly appreciated.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004289 More actions · Answer 1

EMtwo wrote:

I am looking for a script that can return the following values for each of the databases in my instance: Object ID, Object Type, Database Name, Table Name, Schema Name, Index Name, Table Create Date, Table Modified Date, Row Count, Total Pages, Used Pages, Data Pages, Total KB, Total MB, Total GB, Used KB, Used MB, Used GB.
Your help will be greatly appreciated.

Do you know how to get those items for a single database or do you need help there, as well?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

EMtwo Hall of Fame Points: 3498 More actions · Answer 2

I am able to get some of the values for all of the database in my instances with a script I have, but not all of the ones I listed in my request.

Thank you.

Phil Parkin SSC Guru Points: 246941 More actions · Answer 3

EMtwo wrote:

I am able to get some of the values for all of the database in my instances with a script I have, but not all of the ones I listed in my request.
Thank you.

If you post the script you have already, people here will probably help extend it for you.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

EMtwo Hall of Fame Points: 3498 More actions · Answer 4

declare @sql nvarchar(max);

set @sql = N'select cast(''master'' as sysname) as db_name, name collate Latin1_General_CI_AI, object_id, schema_id, cast(1 as int) as database_id  from master.sys.tables ';

 

select @sql = @sql + N' union all select ' + quotename(name,'''')+ ', name collate Latin1_General_CI_AI, object_id, schema_id, ' + cast(database_id as nvarchar(10)) + N' from ' + quotename(name) + N'.sys.tables'

from sys.databases where database_id > 1

and state = 0

and user_access = 0;

 

exec sp_executesql @sql;

EMtwo Hall of Fame Points: 3498 More actions · Answer 5

The script below retrieves the following values for a database in my instance: ("OBJ_NAME" "OBJ_TYPE" RowCounts TotalPages UsedPages DataPages TotalSpaceMB UsedSpaceMB DataSpaceMB). I would however want to retrieve these values for all databases in my instance. Please see below the script.

SELECT distinct
    t.NAME AS OBJ_NAME,
    t.type_desc as OBJ_TYPE,
   -- i.name as indexName,
    sum(p.rows) as RowCounts,
    sum(a.total_pages) as TotalPages, 
    sum(a.used_pages) as UsedPages, 
    sum(a.data_pages) as DataPages,
    (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB, 
    (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB, 
    (sum(a.data_pages) * 8) / 1024 as DataSpaceMB
FROM 
    sys.objects t
INNER JOIN
    sys.schemas s ON t.SCHEMA_ID = s.SCHEMA_ID 
--INNER JOIN      
    --sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON p.object_id = t.object_id--p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
t.type = 'U' AND -- User Created Tables
    t.NAME NOT LIKE 'dt%' AND
   -- t.OBJECT_ID > 255 AND   
    p.index_id <= 1
GROUP BY 
    s.NAME, t.NAME, t.type_desc, t.object_id--, i.index_id, i.name 
ORDER BY
    t.name--SUM(a.total_pages) DESC
;

Ram Valued Member Points: 73 More actions · Answer 6

the code in below link should fulfill your requirement

https://pastebin.com/xUDYhF0r

Jeff Moden SSC Guru Points: 1004289 More actions · Answer 7

You won't find many folks that will click on a link on this site. Also, if that other site drops off the face of the planet, then the answer is lost.

Please post your code in a reply on this site. Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

EMtwo Hall of Fame Points: 3498 More actions · Answer 8

EMtwo

Hall of Fame

Points: 3498

August 22, 2020 at 12:06 am

#3782452

Ram, I can't thank you enough! Thanks!

HardissonHard SSC Rookie Points: 45 More actions · Answer 9

HardissonHard

SSC Rookie

Points: 45

September 4, 2020 at 9:27 am

#3786225

that's right code

EMtwo Hall of Fame Points: 3498 More actions · Answer 10

Ram,

The script seemed to have worked just fine but I spotted an issue with the results. The script is returning the objects in one of the databases for all of the databases. See the below image; you'll see that the object name is repeated...

SQLScreenshot

Ram Valued Member Points: 73 More actions · Answer 11

thanks for highlighting the issue. i have fixed the code.

updated code available in below link.

https://pastebin.com/xUDYhF0r

Jeff Moden SSC Guru Points: 1004289 More actions · Answer 12

Ram wrote:

thanks for highlighting the issue. i have fixed the code.
updated code available in below link.
https://pastebin.com/xUDYhF0r

Please post the code on this site, Ram... the content of pastebin could simply vanish like so many similar sites have.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

EMtwo Hall of Fame Points: 3498 More actions · Answer 13

Thank you Ram; this was very helpful. I went ahead and edited the script a little further - these are the values that are returned from the script: DatabaseName, SchemaName, TableName, TableID, NumberofRows, TotalSpaceKB, UsedSpaceKB, DataSpaceKB, TotalSpaceMB, UsedSpaceMB, DataSpaceMB, TotalSpaceGB, UsedSpaceGB, DataSpaceGB.

DROP TABLE IF EXISTS #t1;
 CREATE TABLE #t1 (
    [DatabaseName] [sysname] NOT NULL,
    [SchemaName] [sysname] NOT NULL,
    [TableName] [NVARCHAR](60) NULL,
    [TableID]  [BIGINT] NULL,
    [NumberOfRows] [BIGINT] NULL,
    [TotalPages] [BIGINT] NULL,
    [UsedPages] [BIGINT] NULL,
    [DataPages] [BIGINT] NULL,
    [TotalSpaceKB] [BIGINT] NULL,
    [UsedSpaceKB] [BIGINT] NULL,
    [DataSpaceKB] [BIGINT] NULL,
    [TotalSpaceMB] [BIGINT] NULL,
    [UsedSpaceMB] [BIGINT] NULL,
    [DataSpaceMB] [BIGINT] NULL,
    [TotalSpaceGB] [BIGINT] NULL,
    [UsedSpaceGB] [BIGINT] NULL,
    [DataSpaceGB] [BIGINT] NULL
) ON [PRIMARY]
GO

DECLARE @command1 varchar(max)
SELECT @command1 =' use ? INSERT INTO #t1 ([DatabaseName],[SchemaName],[TableName],[TableID],[NumberOfRows],[TotalPages],[UsedPages],[DataPages],[TotalSpaceKB],[UsedSpaceKB],[DataSpaceKB],[TotalSpaceMB],[UsedSpaceMB],[DataSpaceMB], [TotalSpaceGB], [UsedSpaceGB], [DataSpaceGB])
SELECT DISTINCT ''?'' AS DatabaseName,s.Name AS SchemaName, t.NAME AS TableName, t.Object_ID AS TableID, SUM( p.rows ) AS NumberOfRows, SUM( a.total_pages )  AS TotalPages, SUM( a.used_pages )  AS UsedPages, SUM( a.data_pages )  AS DataPages, SUM(a.total_pages) * 8 AS TotalSpaceKB, SUM(a.total_pages) * 8 AS UsedSpaceKB, SUM(a.total_pages) * 8 AS DataSpaceKB,
(SUM( a.total_pages ) *8
) /1024 AS TotalSpaceMB, (
SUM( a.used_pages ) *8
) /1024 AS UsedSpaceMB, (
SUM( a.data_pages ) *8
) /1024 AS DataSpaceMB
,(
SUM( a.total_pages ) *8
) /1024/1024 AS TotalSpaceGB, (
SUM( a.used_pages ) *8
) /1024/1024 AS UsedSpaceGB, (
SUM( a.data_pages ) *8
) /1024/1024 AS DataSpaceGB
' +
' FROM sys.objects t ' +
' INNER JOIN sys.schemas s ON t.SCHEMA_ID = s.SCHEMA_ID ' +
' INNER JOIN sys.partitions p ON p.object_id = t.object_id '+
' INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id '+
' WHERE t.type = ''U'' '+
' AND t.NAME NOT LIKE ''dt%'' '+
' AND p.index_id <=1 '+
' GROUP BY s.NAME, t.NAME, t.type_desc, t.object_id '+
' ORDER BY t.name; '
EXEC sp_MSforeachdb @command1
 
--INSERT INTO dbo.table_Name

SELECT *

FROM #t1

Ram Valued Member Points: 73 More actions · Answer 14

each one has there own opinion. it should be left to them. that idle for mutual respect.

i would like to maintain my code in pastebin.

is there any hard rule that in sqlservercentral forum that i should not add link.

what is the assurance that content in sql server central will also not vanish one day like other site.