Author |
Topic |
dwalker79
Yak Posting Veteran
54 Posts |
Posted - 2008-08-20 : 10:38:56
|
Is there anywhere within the actual database itself that lists the database name. I know that it's stored in Master but I wasn't sure if there was a way to pull it from a column in the database itself? |
|
dwalker79
Yak Posting Veteran
54 Posts |
Posted - 2008-08-20 : 10:45:31
|
Let me be more specific. This is a piece of a stored procedure I'm working on. I need to add a line that will grab the current database name that the stored proc is being run against. Later in the stored procedure I'm inserting this information into a table. Can anyone tell me what I can add to grab the current database name AS "Database Name"?[red]BEGIN SELECT @SELECT = N'USE ' + @DbName + N' SELECT ' + ISNULL(@TopClause, N' ') + N''''' + USER_NAME(o.uid) + ''.'' + OBJECT_NAME(i.id) + '''' AS ''ObjectName'', o.type AS ''Type'', MAX(i.[rows]) AS ''Rows'', CONVERT(dec(10,3), SUM(i.reserved * 8.000/1024)) AS ''Total(MB)'', CONVERT(dec(10,3), SUM((i.reserved - i.used) * 8.000/1024)) AS ''Unused(MB)'', CONVERT(dec(10,3), SUM(i.used * 8.000/1024)) AS ''Used(MB)'', CONVERT(dec(10,3), SUM((i.used - CASE WHEN indid <> 255 THEN i.dpages ELSE i.used END) * 8.000/1024)) AS ''Index(MB)'', CONVERT(dec(10,3), SUM(CASE WHEN indid <> 255 THEN i.dpages ELSE i.used END * 8.000/1024)) AS ''Data(MB)'' FROM dbo.sysindexes i WITH (NOLOCK) JOIN dbo.sysobjects o WITH (NOLOCK) ON i.id = o.id WHERE i.name NOT LIKE ''_WA_Sys_%'' AND i.indid IN (0, 1, 255) AND USER_NAME(o.uid) LIKE ''' + ISNULL(@SchemaName, N'%') + N''' ', -- SS 2000 calculation as below: -- "reserved" = total size; -- "dpages" = data used; -- "used" = used portion (contains data and index); -- text or image column: use "used" for data size -- Nonclustered index take tiny space, somehow it is not counted (see sp_spaceused). @WHERE_Final = N' AND OBJECT_NAME(i.id) LIKE ''' + ISNULL(@ObjectName, N'%') + N''' AND o.type IN (' + @ObjectType + N') ', @GroupBy = N' GROUP BY '''' + USER_NAME(o.uid) + ''.'' + OBJECT_NAME(i.id) + '''', o.type ', @String = @SELECT + @WHERE_Final + @GroupBy + @OrderBy ENDThanks!Dustin |
 |
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2008-08-20 : 12:26:59
|
What about the function DB_NAME()SELECT DB_NAME() -------------Charlie |
 |
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2008-08-20 : 12:32:58
|
BEGIN SELECT @SELECT = N'USE ' + @DbName + N' SELECT ' + ISNULL(@TopClause, N' ') + N''''' + USER_NAME(o.uid) + ''.'' + OBJECT_NAME(i.id) + '''' AS ''ObjectName'', o.type AS ''Type'', DB_NAME() AS [Database Name], MAX(i.[rows]) AS ''Rows'', CONVERT(dec(10,3), SUM(i.reserved * 8.000/1024)) AS ''Total(MB)'', CONVERT(dec(10,3), SUM((i.reserved - i.used) * 8.000/1024)) AS ''Unused(MB)'', CONVERT(dec(10,3), SUM(i.used * 8.000/1024)) AS ''Used(MB)'', CONVERT(dec(10,3), SUM((i.used - CASE WHEN indid <> 255 THEN i.dpages ELSE i.used END) * 8.000/1024)) AS ''Index(MB)'', CONVERT(dec(10,3), SUM(CASE WHEN indid <> 255 THEN i.dpages ELSE i.used END * 8.000/1024)) AS ''Data(MB)'' FROM dbo.sysindexes i WITH (NOLOCK) JOIN dbo.sysobjects o WITH (NOLOCK) ON i.id = o.id WHERE i.name NOT LIKE ''_WA_Sys_%'' AND i.indid IN (0, 1, 255) AND USER_NAME(o.uid) LIKE ''' + ISNULL(@SchemaName, N'%') + N''' ', -- SS 2000 calculation as below: -- "reserved" = total size; -- "dpages" = data used; -- "used" = used portion (contains data and index); -- text or image column: use "used" for data size -- Nonclustered index take tiny space, somehow it is not counted (see sp_spaceused). @WHERE_Final = N' AND OBJECT_NAME(i.id) LIKE ''' + ISNULL(@ObjectName, N'%') + N''' AND o.type IN (' + @ObjectType + N') ', @GroupBy = N' GROUP BY '''' + USER_NAME(o.uid) + ''.'' + OBJECT_NAME(i.id) + '''', o.type ', @String = @SELECT + @WHERE_Final + @GroupBy + @OrderBy END-------------Charlie |
 |
|
dwalker79
Yak Posting Veteran
54 Posts |
Posted - 2008-08-20 : 14:13:41
|
Thanks for the Info, however, now I'm receiving the error:String or binary data would be truncated. I've checked to make sure the columns on the table I'm doing the Insert in are large enough. Here is the entire stored procedure. Please help! CREATE PROCEDURE dbo.spdba_ObjectSizing @DbName sysname = NULL, @SchemaName sysname = NULL, @ObjectName sysname = N'%', @TopClause nvarchar(20) = NULL, @ObjectType nvarchar(50) = NULL, @ShowInternalTable nvarchar(3) = NULL, @OrderBy nvarchar(100) = NULL, @UpdateUsage bit = 0 ASSET XACT_ABORT ON;SET NOCOUNT ON;-- Input parameter validity checkingDECLARE @SELECT nvarchar(2500), @WHERE_Schema nvarchar(200), @WHERE_Object nvarchar(200), @WHERE_Type nvarchar(200), @WHERE_Final nvarchar(1000), @ID int, @Version nchar(2), @String nvarchar(4000), @Count bigint, @GroupBy nvarchar(450);IF ISNULL(@OrderBy, N'N') NOT IN (N'', N'N', N'R', N'T', N'U', N'I', N'D', N'F', N'Y') BEGIN RAISERROR (N'Incorrect value for @OrderBy. Valid parameters are: ''N'' --> Listing by object name ''R'' --> Listing by number of records ''T'' --> Listing by total size ''U'' --> Listing by used portion (excluding free space) ''I'' --> Listing by index size ''D'' --> Listing by data size ''F'' --> Listing by unused (free) space ''Y'' --> Listing by object type ', 16, 1) RETURN (-1) END;-- Object Type Validation and Clean upDECLARE @OTV nvarchar(10), @OTC nvarchar(10);SELECT @OTV = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(ISNULL(@ObjectType, N'S, U, V, SQ, IT'), N' ', N''), N',', N''), N';', N''), N'SQ', N''), N'U', N''), N'V', N''), N'IT', N''), N'S', N'');IF LEN(@OTV) <> 0 -- only allow comma, semi colon and space around S,U,V,SQ,IT BEGIN RAISERROR (N'Parameter error. Choose ''S'', ''U'', ''V'', ''SQ'', ''IT'' or any combination of them, separated by space, comma or semicolon. S -> System table; U -> User table; V -> Indexed view; SQ -> Service Queue; IT -> Internal Table', 16, 1) RETURN (-1) ENDELSE -- passed validation BEGIN SET @OTC = UPPER(REPLACE(REPLACE(REPLACE(ISNULL(@ObjectType,N'S,U,V,SQ,IT'),N' ',N''),N',',N''),N';',N'')) SELECT @ObjectType = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(ISNULL (@ObjectType, N'S,U,V,SQ,IT'),N',',N''),N';',N''),N'SQ',N'''QQ'''),N'IT',N'''IT'''),N'S', N'''S'''),N'U',N'''U'''),N'V',N'''V'''),N'QQ',N'SQ'),N' ',N''),N'''''',N''',''') END---- common ----SELECT @DbName = ISNULL(@DbName, DB_NAME()), @Version = SUBSTRING(CONVERT(nchar(20), SERVERPROPERTY (N'ProductVersion')), 1, CHARINDEX(N'.', CONVERT(nchar(20), SERVERPROPERTY (N'ProductVersion')))-1), @OrderBy = N'ORDER BY [' + CASE ISNULL(@OrderBy, N'N') WHEN N'N' THEN N'ObjectName] ASC ' WHEN N'R' THEN N'Rows] DESC, [ObjectName] ASC ' WHEN N'T' THEN N'Total(MB)] DESC, [ObjectName] ASC ' WHEN N'U' THEN N'Used(MB)] DESC, [ObjectName] ASC ' WHEN N'I' THEN N'Index(MB)] DESC, [ObjectName] ASC ' WHEN N'D' THEN N'Data(MB)] DESC, [ObjectName] ASC ' WHEN N'F' THEN N'Unused(MB)] DESC, [ObjectName] ASC ' WHEN N'Y' THEN N'Type] ASC, [ObjectName] ASC ' END;---------------------- SS 2000 -----------------------------------IF @Version = N'8' BEGIN SELECT @SELECT = N'USE ' + @DbName + N' SELECT ' + ISNULL(@TopClause, N' ') + N''''' + USER_NAME(o.uid) + ''.'' + OBJECT_NAME(i.id) + '''' AS ''ObjectName'', o.type AS ''Type'', DB_NAME() AS ''DatabaseName'', MAX(i.[rows]) AS ''Rows'', CONVERT(dec(10,3), SUM(i.reserved * 8.000/1024)) AS ''Total(MB)'', CONVERT(dec(10,3), SUM((i.reserved - i.used) * 8.000/1024)) AS ''Unused(MB)'', CONVERT(dec(10,3), SUM(i.used * 8.000/1024)) AS ''Used(MB)'', CONVERT(dec(10,3), SUM((i.used - CASE WHEN indid <> 255 THEN i.dpages ELSE i.used END) * 8.000/1024)) AS ''Index(MB)'', CONVERT(dec(10,3), SUM(CASE WHEN indid <> 255 THEN i.dpages ELSE i.used END * 8.000/1024)) AS ''Data(MB)'' FROM dbo.sysindexes i WITH (NOLOCK) JOIN dbo.sysobjects o WITH (NOLOCK) ON i.id = o.id WHERE i.name NOT LIKE ''_WA_Sys_%'' AND i.indid IN (0, 1, 255) AND USER_NAME(o.uid) LIKE ''' + ISNULL(@SchemaName, N'%') + N''' ', -- SS 2000 calculation as below: -- "reserved" = total size; -- "dpages" = data used; -- "used" = used portion (contains data and index); -- text or image column: use "used" for data size -- Nonclustered index take tiny space, somehow it is not counted (see sp_spaceused). @WHERE_Final = N' AND OBJECT_NAME(i.id) LIKE ''' + ISNULL(@ObjectName, N'%') + N''' AND o.type IN (' + @ObjectType + N') ', @GroupBy = N' GROUP BY '''' + USER_NAME(o.uid) + ''.'' + OBJECT_NAME(i.id) + '''', o.type ', @String = @SELECT + @WHERE_Final + @GroupBy + @OrderBy END------------------- ss 2k5 ------------------------------------------------------IF @Version IN (N'9', N'10') BEGIN SELECT @String = N' IF OBJECT_ID (''tempdb.dbo.##BO'', ''U'') IS NOT NULL DROP TABLE dbo.##BO CREATE TABLE dbo.##BO ( ID int identity, DOI bigint null, -- Daughter Object Id DON sysname null, -- Daughter ObjectName DSI int null, -- Daughter Schema Id DSN sysname null, -- Daughter Schema Name DOT varchar(10) null, -- Daughter Object Type DFN sysname null, -- Daughter Full Name POI bigint null, -- Parent Object Id PON sysname null, -- Parent ObjectName PSI bigint null, -- Parent Schema Id PSN sysname null, -- Parent Schema Name POT varchar(10) null, -- Parent Object Type PFN sysname null -- Parent Full Name) INSERT INTO dbo.##BO (DOI, DSI, DOT, POI) SELECT object_id, schema_id, type, Parent_object_id FROM ' + @DbName + N'.sys.objects o WHERE type IN (''S'',''U'',''V'',''SQ'',''IT'') USE ' + @DbName + N' UPDATE dbo.##BO SET DON = object_name(DOI), DSN = schema_name(DSI), POI = CASE POI WHEN 0 THEN DOI ELSE POI ENDUPDATE dbo.##BO SET PSI = o.schema_id, POT = o.type FROM sys.objects o JOIN dbo.##BO t ON o.object_id = t.POIUPDATE dbo.##BO SET PON = object_name(POI), PSN = schema_name(PSI), DFN = DSN + ''.'' + DON, PFN = schema_name(PSI)+ ''.'' + object_name(POI)'EXEC (@String)SELECT @WHERE_Type = CASE WHEN ISNULL(@ShowInternalTable, N'Yes') = N'Yes' THEN N't.DOT ' ELSE N't.POT ' END, @SELECT = N'USE ' + @DbName + N' SELECT ' + ISNULL(@TopClause, N'TOP 100 PERCENT ') + N' CASE WHEN ''' + isnull(@ShowInternalTable, N'Yes') + N''' = ''Yes'' THEN CASE t.DFN WHEN t.PFN THEN t.PFN ELSE t.DFN + '' (''+ t.PFN + '')'' END ELSE t.PFN END AS ''ObjectName'', ' + @WHERE_Type + N' AS ''Type'', SUM (CASE WHEN ''' + isnull(@ShowInternalTable, N'Yes') + N''' = ''Yes'' THEN CASE WHEN (ps.index_id < 2 ) THEN ps.row_count ELSE 0 END ELSE CASE WHEN (ps.index_id < 2 and t.DON = t.PON) THEN ps.row_count ELSE 0 END END) AS ''Rows'', SUM (CASE WHEN t.DON NOT LIKE ''fulltext%'' OR t.DON LIKE ''fulltext_index_map%'' THEN ps.reserved_page_count ELSE 0 END)* 8.000/1024 AS ''Total(MB)'', SUM (CASE WHEN t.DON NOT LIKE ''fulltext%'' OR t.DON LIKE ''fulltext_index_map%'' THEN ps.reserved_page_count ELSE 0 END - CASE WHEN t.DON NOT LIKE ''fulltext%'' OR t.DON LIKE ''fulltext_index_map%'' THEN ps.used_page_count ELSE 0 END)* 8.000/1024 AS ''Unused(MB)'', SUM (CASE WHEN t.DON NOT LIKE ''fulltext%'' OR t.DON LIKE ''fulltext_index_map%'' THEN ps.used_page_count ELSE 0 END)* 8.000/1024 AS ''Used(MB)'', SUM (CASE WHEN t.DON NOT LIKE ''fulltext%'' OR t.DON LIKE ''fulltext_index_map%'' THEN ps.used_page_count ELSE 0 END - CASE WHEN t.POT NOT IN (''SQ'',''IT'') AND t.DOT IN (''IT'') and ''' + isnull(@ShowInternalTable, N'Yes') + N''' = ''No'' THEN 0 ELSE CASE WHEN (ps.index_id<2) THEN (ps.in_row_data_page_count+ps.lob_used_page_count+ps.row_overflow_used_page_count) ELSE ps.lob_used_page_count + ps.row_overflow_used_page_count END END) * 8.000/1024 AS ''Index(MB)'', SUM (CASE WHEN t.POT NOT IN (''SQ'',''IT'') AND t.DOT IN (''IT'') and ''' + isnull(@ShowInternalTable, N'Yes') + N''' = ''No'' THEN 0 ELSE CASE WHEN (ps.index_id<2) THEN (ps.in_row_data_page_count+ps.lob_used_page_count+ps.row_overflow_used_page_count) ELSE ps.lob_used_page_count + ps.row_overflow_used_page_count END END) * 8.000/1024 AS ''Data(MB)'' FROM sys.dm_db_partition_stats ps INNER JOIN dbo.##BO t ON ps.object_id = t.DOI ',@ObjectType = CASE WHEN ISNULL(@ShowInternalTable, N'Yes') = N'Yes' THEN N'''IT'',' + ISNULL(@ObjectType, N'''S'',''U'', ''V'', ''SQ'', ''IT''') ELSE ISNULL(@ObjectType, N'''S'', ''U'', ''V'', ''SQ'', ''IT''') END,@WHERE_Schema = CASE WHEN ISNULL(@ShowInternalTable, N'Yes') = N'Yes' THEN N' t.DSN ' ELSE N' t.PSN ' END, -- DSN or PSN@WHERE_Object = CASE WHEN ISNULL(@ShowInternalTable, N'Yes') = N'Yes' THEN N' t.DON LIKE ''' + ISNULL(@ObjectName, N'%') + ''' OR t.PON LIKE ''' + ISNULL(@ObjectName, N'%') + N''' ' ELSE N' t.pon LIKE ''' + ISNULL(@ObjectName, N'%') + N''' ' END, -- DON or PON@WHERE_Final = N' WHERE (' + @WHERE_Schema + N' LIKE ''' + ISNULL(@SchemaName, N'%') + N''' OR ' + @WHERE_Schema + N' = ''sys'') AND (' + @WHERE_Object + N' ) AND ' + @WHERE_Type + N' IN (' + @ObjectType + N') ',@GroupBy = N'GROUP BY CASE WHEN ''' + ISNULL(@ShowInternalTable, N'Yes') + N''' = ''Yes'' THEN CASE t.DFN WHEN t.PFN THEN t.PFN ELSE t.DFN + '' (''+ t.PFN + '')'' END ELSE t.PFN END, ' + @WHERE_Type + N''SELECT @String = @SELECT + @WHERE_Final + @GroupBy + @OrderBy -- SELECT @String AS 'STRING'END----- common ------INSERT INTO IT100VSQL.DBMaster.dbo.ObjectSizing ([ObjectName], [DatabaseName], [Type], [Rows], [Total(MB)],[Unused(MB)],[Used(MB)],[Index(MB)],[Data(MB)]) EXEC (@String);SELECT @Count = COUNT(*) FROM IT100VSQL.DBMaster.dbo.ObjectSizing;IF @Count = 0 BEGIN RAISERROR (N'No records were found macthcing your criteria.', 16, 1) RETURN (-1) ENDELSE -- There're at least one records BEGIN INSERT INTO IT100VSQL.DBMaster.dbo.ObjectSizing ([ObjectName], [DatabaseName], [Type], [Rows], [Total(MB)],[Unused(MB)], [Used(MB)],[Index(MB)],[Data(MB)]) EXEC (@String) END IF @Count = 1 -- when only 1 row, no need to sum up total SELECT [ObjectName], [DatabaseName], [Type], [Rows], [Total(MB)], [Unused(MB)], [Used(MB)], [Index(MB)], [Data(MB)] FROM IT100VSQL.DBMaster.dbo.ObjectSizing ORDER BY [ObjectName] ASC ELSE BEGIN SELECT [ObjectName], [DatabaseName], [Type], [Rows], [Total(MB)], [Unused(MB)], [Used(MB)], [Index(MB)], [Data(MB)] FROM IT100VSQL.DBMaster.dbo.ObjectSizing ORDER BY [ObjectName] ASC --COMPUTE SUM([Total(MB)]), SUM([Unused(MB)]), SUM([Used(MB)]), SUM([Index(MB)]), SUM([Data(MB)]) END RETURN (0)GO |
 |
|
dwalker79
Yak Posting Veteran
54 Posts |
Posted - 2008-08-20 : 14:14:57
|
Oh, I forgot to mention I'm running this in SQL Server 2000 so the "SS 2000" section is the one I'm using. |
 |
|
dwalker79
Yak Posting Veteran
54 Posts |
Posted - 2008-08-20 : 14:21:16
|
Nevermind, I figured that out. Now I just need to know how to add a current Date/Time field and have it pull the current date time?Does anyone know how best to do this?Thanks so much!! |
 |
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2008-08-21 : 04:11:46
|
GETDATE()-------------Charlie |
 |
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2008-08-21 : 04:14:31
|
same as before really..SELECT getDate() returns current (database server) date and time-------------Charlie |
 |
|
|
|
|