SQL Script Help
-
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.
- 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) -
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.
- 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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
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; -
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
; -
-
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) -
Ram, I can't thank you enough! Thanks!
-
that's right code
-
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...
-
thanks for highlighting the issue. i have fixed the code.
updated code available in below link.
- Ram wrote:
thanks for highlighting the issue. i have fixed the code.
updated code available in below link.
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) -
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 -
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.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply