 |
SoftTree Technologies
Technical Support Forums
|
|
| Author |
Message |
gemisigo
Joined: 11 Mar 2010
Posts: 2165
|
|
 Messing with DB Queries, need feedback |
 |
After successful modifications to one of the DB Queries yesterday I took the liberty of tinkering with some others as well. During the process I noticed that while we can (hmm, I don't even know what their name is: inner variable maybe?) use :OBJECT_NAME and :OBJECT_ID, the very handy OBJECT_SCHEMA_NAME is missing its coloned counterpart. Also, there is no reference to it in either DB Options -> Custom Assistance or in Code Formatting -> Keywords.
I made some slight refinements to Object Info (MSSQL) and I'd like to ask your opinion about it. This one only affects SQL Server versions >= 2k5. It was "legoed" together quite fast using duct tape and nail file and no optimization has been performed on the code, so it might be of poor quality, unreliable and fail here and there but it works (at least on our server). Here's the code:
 |
 |
-- WARNING: This query works only for objects in the current database
IF :DB_NAME IS NOT NULL
IF :DB_NAME != DB_NAME()
BEGIN
SELECT 'WARNING: This query works only for objects in the current database'
RETURN
END
if @@version like 'Microsoft SQL Server 2005%'
or @@version like 'Microsoft SQL Server 2008%'
or @@version like '%SQL Server 2012%'
or @@version like '%Denali%'
begin
CREATE TABLE #space_used
(
[name] SYSNAME NOT NULL
,[rows] INT NOT NULL
,[reserved_c] VARCHAR( 32 ) NOT NULL
,[reserved_kb] AS CAST( SUBSTRING( [reserved_c] ,1 ,LEN( [reserved_c] ) - 3 ) AS DECIMAL( 18 ,2 ) )
,[reserved_mb] AS CAST( CAST( SUBSTRING( [reserved_c] ,1 ,LEN( [reserved_c] ) - 3 ) AS DECIMAL( 18 ,2 ) ) / POWER( 10 ,3 ) AS DECIMAL( 18 ,2 ) )
,[reserved_gb] AS CAST( CAST( SUBSTRING( [reserved_c] ,1 ,LEN( [reserved_c] ) - 3 ) AS DECIMAL( 18 ,2 ) ) / POWER( 10 ,6 ) AS DECIMAL( 18 ,2 ) )
,[data_c] VARCHAR( 32 ) NOT NULL
,[data_kb] AS CAST( SUBSTRING( [data_c] ,1 ,LEN( [data_c] ) - 3 ) AS DECIMAL( 18 ,2 ) )
,[data_mb] AS CAST( CAST( SUBSTRING( [data_c] ,1 ,LEN( [data_c] ) - 3 ) AS DECIMAL( 18 ,2 ) ) / POWER( 10 ,3 ) AS DECIMAL( 18 ,2 ) )
,[data_gb] AS CAST( CAST( SUBSTRING( [data_c] ,1 ,LEN( [data_c] ) - 3 ) AS DECIMAL( 18 ,2 ) ) / POWER( 10 ,6 ) AS DECIMAL( 18 ,2 ) )
,[index_size_c] VARCHAR( 32 ) NOT NULL
,[index_size_kb] AS CAST( SUBSTRING( [index_size_c] ,1 ,LEN( [index_size_c] ) - 3 ) AS DECIMAL( 18 ,2 ) )
,[index_size_mb] AS CAST( CAST( SUBSTRING( [index_size_c] ,1 ,LEN( [index_size_c] ) - 3 ) AS DECIMAL( 18 ,2 ) ) / POWER( 10 ,3 ) AS DECIMAL( 18 ,2 ) )
,[index_size_gb] AS CAST( CAST( SUBSTRING( [index_size_c] ,1 ,LEN( [index_size_c] ) - 3 ) AS DECIMAL( 18 ,2 ) ) / POWER( 10 ,6 ) AS DECIMAL( 18 ,2 ) )
,[unused_c] VARCHAR( 32 ) NOT NULL
,[unused_kb] AS CAST( SUBSTRING( [unused_c] ,1 ,LEN( [unused_c] ) - 3 ) AS DECIMAL( 18 ,2 ) )
,[unused_mb] AS CAST( CAST( SUBSTRING( [unused_c] ,1 ,LEN( [unused_c] ) - 3 ) AS DECIMAL( 18 ,2 ) ) / POWER( 10 ,3 ) AS DECIMAL( 18 ,2 ) )
,[unused_gb] AS CAST( CAST( SUBSTRING( [unused_c] ,1 ,LEN( [unused_c] ) - 3 ) AS DECIMAL( 18 ,2 ) ) / POWER( 10 ,6 ) AS DECIMAL( 18 ,2 ) )
)
DECLARE @object_schema_name sysname
SET @object_schema_name = OBJECT_SCHEMA_NAME(:OBJECT_ID) + '.' + :OBJECT_NAME --(:OBJECT_ID)
INSERT INTO #space_used
(
[name]
,[rows]
,[reserved_c]
,[data_c]
,[index_size_c]
,[unused_c]
)
EXECUTE sp_spaceused @object_schema_name
SELECT '* Table info*' + ' - ' + @object_schema_name + CHAR(10) +
'Object created: ' + CHAR(9) + convert(varchar, o.create_date) + CHAR(10) +
'Last modified: ' + CHAR(9) + CASE WHEN o.modify_date IS NULL THEN 'none' ELSE convert(varchar, o.modify_date) END + CHAR(10) +
CASE WHEN s.auto_created = 1
THEN 'Stats mode: ' + CHAR(9) + 'automatically updated'
ELSE 'Stats mode: ' + CHAR(9) + 'user updated'
END + CHAR(10) +
'Stats time: ' + CHAR(9) + isnull(convert(VARCHAR, STATS_DATE(o.[object_id], s.stats_id)), 'never')
FROM sys.objects o
LEFT JOIN sys.stats s
ON s.[object_id] = o.[object_id]
AND s.stats_id = 1
WHERE o.[object_id] = :OBJECT_ID
UNION ALL
SELECT CHAR(10) + '* Table comment*' + CHAR(10) + convert(varchar(4000), [value])
FROM fn_listextendedproperty (default, 'schema', :SCHEMA_NAME, 'table', :OBJECT_NAME, default, default)
UNION ALL
SELECT
ISNULL(
CHAR( 10 ) + '* Table usage*' + CHAR( 10 ) +
'Rows: ' + CHAR( 9 ) + CAST( su.[rows] AS VARCHAR( 256 ) ) + CHAR( 10 ) +
'Reserved: ' + CHAR( 9 ) + CASE
WHEN su.reserved_kb < 1000 THEN CAST( su.reserved_kb AS VARCHAR( 256 ) ) + ' KB'
WHEN su.reserved_mb < 1000 THEN CAST( su.reserved_mb AS VARCHAR( 256 ) ) + ' MB'
ELSE CAST( su.reserved_gb AS VARCHAR( 256 ) ) + ' GB'
END + CHAR( 10 ) +
'Data: ' + CHAR( 9 ) + CASE
WHEN su.data_kb < 1000 THEN CAST( su.data_kb AS VARCHAR( 256 ) ) + ' KB'
WHEN su.data_mb < 1000 THEN CAST( su.data_mb AS VARCHAR( 256 ) ) + ' MB'
ELSE CAST( su.data_gb AS VARCHAR( 256 ) ) + ' GB'
END + CHAR( 10 ) +
'Index size: ' + CHAR( 9 ) + CASE
WHEN su.index_size_kb < 1000 THEN CAST( su.index_size_kb AS VARCHAR( 256 ) ) + ' KB'
WHEN su.index_size_mb < 1000 THEN CAST( su.index_size_mb AS VARCHAR( 256 ) ) + ' MB'
ELSE CAST( su.index_size_gb AS VARCHAR( 256 ) ) + ' GB'
END + CHAR( 10 ) +
'Unused: ' + CHAR( 9 ) + CASE
WHEN su.unused_kb < 1000 THEN CAST( su.unused_kb AS VARCHAR( 256 ) ) + ' KB'
WHEN su.unused_mb < 1000 THEN CAST( su.unused_mb AS VARCHAR( 256 ) ) + ' MB'
ELSE CAST( su.unused_gb AS VARCHAR( 256 ) ) + ' GB'
END + CHAR( 10 )
,'ehm... error'
)
FROM
#space_used AS su
UNION ALL
SELECT CHAR(10) + '* Columns*'
UNION ALL
SELECT CHAR(10) + isnull(c.objname collate Latin1_General_CI_AS, ut.colname collate Latin1_General_CI_AS) + CHAR(9) + CASE WHEN ut.basetype IS NOT NULL THEN 'Base data-type: ' + ut.basetype collate Latin1_General_CI_AS + '; ' ELSE '' END
+ convert(varchar(4000), c.[value])
FROM fn_listextendedproperty(default, 'schema', :SCHEMA_NAME, 'table', :OBJECT_NAME, 'column', default) c
FULL JOIN
(SELECT c.name AS colname,
bt.name + case
when bt.name in ('varchar', 'char', 'binary', 'varbinary')
then '(' + case c.max_length when -1 then 'max' else convert(varchar, c.max_length) end + ')'
when bt.name in ('nvarchar', 'nchar')
then '(' + case c.max_length when -1 then 'max' else convert(varchar, c.max_length / 2) end + ')'
when bt.name in ('decimal', 'numeric')
then '(' + convert(varchar, c.[precision]) + ',' + convert(varchar, c.scale) + ')'
else ''
end
AS basetype
FROM
sys.all_columns c
JOIN sys.types bt
ON bt.user_type_id = c.system_type_id
AND bt.system_type_id = c.system_type_id
WHERE c.object_id = :OBJECT_ID
) ut
ON ut.colname collate Latin1_General_CI_AS = c.objname collate Latin1_General_CI_AS
WHERE ut.colname IS NOT NULL
OR c.objname IS NOT NULL
DROP TABLE #space_used
END
ELSE
SELECT CHAR(10) + '* Table comment*' + CHAR(10) + convert(varchar(4000), [value])
FROM ::fn_listextendedproperty (default, 'schema', :SCHEMA_NAME, 'table', :OBJECT_NAME, default, default)
UNION ALL
SELECT CHAR(10) + '* Columns*'
UNION ALL
SELECT CHAR(10) + isnull(c.objname collate Latin1_General_CI_AS, ut.colname collate Latin1_General_CI_AS) + CHAR(9) + CASE WHEN ut.basetype IS NOT NULL THEN 'Base data-type: ' + ut.basetype collate Latin1_General_CI_AS + '; ' ELSE '' END
+ convert(varchar(4000), c.[value])
FROM ::fn_listextendedproperty(default, 'schema', :SCHEMA_NAME, 'table', :OBJECT_NAME, 'column', default) c
FULL JOIN
(SELECT c.name AS colname,
bt.name + case
when bt.name in ('varchar', 'char', 'binary', 'varbinary')
then '(' + case c.length when -1 then 'max' else convert(varchar, c.length) end + ')'
when bt.name in ('nvarchar', 'nchar')
then '(' + case c.length when -1 then 'max' else convert(varchar, c.length / 2) end + ')'
when bt.name in ('decimal', 'numeric')
then '(' + convert(varchar, c.prec) + ',' + convert(varchar, c.scale) + ')'
else ''
end
AS basetype
FROM
dbo.syscolumns c
JOIN dbo.systypes bt
ON c.xtype = bt.xtype
AND c.xusertype = bt.xusertype
AND c.xusertype != c.xtype
WHERE c.id = :OBJECT_ID
) ut
ON ut.colname collate Latin1_General_CI_AS = c.objname collate Latin1_General_CI_AS
WHERE ut.colname IS NOT NULL
OR c.objname IS NOT NULL
|
|
|
| Thu Sep 06, 2012 6:12 am |
  |
|
SysOp
Site Admin
Joined: 26 Nov 2006
Posts: 7948
|
|
| |
|
Another outstanding work. Thank you.
I have tried it with SQL 2008 Express and SQL 20012 and it worked just fine, even tried with a relatively large table (>1M rows)
For the schema name you can use :SCHEMA_NAME bind variable. Bind variable names are the same across all supported environments, please check queries for Object Info (Oracle)or Object Info (DB2 UDB) to see how that variable can be used.
|
|
| Thu Sep 06, 2012 10:47 am |
|
|
SysOp
Site Admin
Joined: 26 Nov 2006
Posts: 7948
|
|
| |
|
In case anyone is interested, here is an updated query that supports all versions of SQL Server including 2005
|
|
-- WARNING: This query works only for objects in the current database
IF :DB_NAME IS NOT NULL
IF :DB_NAME != DB_NAME()
BEGIN
SELECT 'WARNING: This query works only for objects in the current database'
RETURN
END
if @@version like 'Microsoft SQL Server 2005%'
or @@version like 'Microsoft SQL Server 2008%'
or @@version like '%SQL Server 2012%'
or @@version like '%Denali%'
begin
CREATE TABLE #space_used
(
[name] SYSNAME NOT NULL
,[rows] INT NOT NULL
,[reserved_c] VARCHAR( 32 ) NOT NULL
,[reserved_kb] AS CAST( SUBSTRING( [reserved_c] ,1 ,LEN( [reserved_c] ) - 3 ) AS DECIMAL( 18 ,2 ) )
,[reserved_mb] AS CAST( CAST( SUBSTRING( [reserved_c] ,1 ,LEN( [reserved_c] ) - 3 ) AS DECIMAL( 18 ,2 ) ) / POWER( 10 ,3 ) AS DECIMAL( 18 ,2 ) )
,[reserved_gb] AS CAST( CAST( SUBSTRING( [reserved_c] ,1 ,LEN( [reserved_c] ) - 3 ) AS DECIMAL( 18 ,2 ) ) / POWER( 10 ,6 ) AS DECIMAL( 18 ,2 ) )
,[data_c] VARCHAR( 32 ) NOT NULL
,[data_kb] AS CAST( SUBSTRING( [data_c] ,1 ,LEN( [data_c] ) - 3 ) AS DECIMAL( 18 ,2 ) )
,[data_mb] AS CAST( CAST( SUBSTRING( [data_c] ,1 ,LEN( [data_c] ) - 3 ) AS DECIMAL( 18 ,2 ) ) / POWER( 10 ,3 ) AS DECIMAL( 18 ,2 ) )
,[data_gb] AS CAST( CAST( SUBSTRING( [data_c] ,1 ,LEN( [data_c] ) - 3 ) AS DECIMAL( 18 ,2 ) ) / POWER( 10 ,6 ) AS DECIMAL( 18 ,2 ) )
,[index_size_c] VARCHAR( 32 ) NOT NULL
,[index_size_kb] AS CAST( SUBSTRING( [index_size_c] ,1 ,LEN( [index_size_c] ) - 3 ) AS DECIMAL( 18 ,2 ) )
,[index_size_mb] AS CAST( CAST( SUBSTRING( [index_size_c] ,1 ,LEN( [index_size_c] ) - 3 ) AS DECIMAL( 18 ,2 ) ) / POWER( 10 ,3 ) AS DECIMAL( 18 ,2 ) )
,[index_size_gb] AS CAST( CAST( SUBSTRING( [index_size_c] ,1 ,LEN( [index_size_c] ) - 3 ) AS DECIMAL( 18 ,2 ) ) / POWER( 10 ,6 ) AS DECIMAL( 18 ,2 ) )
,[unused_c] VARCHAR( 32 ) NOT NULL
,[unused_kb] AS CAST( SUBSTRING( [unused_c] ,1 ,LEN( [unused_c] ) - 3 ) AS DECIMAL( 18 ,2 ) )
,[unused_mb] AS CAST( CAST( SUBSTRING( [unused_c] ,1 ,LEN( [unused_c] ) - 3 ) AS DECIMAL( 18 ,2 ) ) / POWER( 10 ,3 ) AS DECIMAL( 18 ,2 ) )
,[unused_gb] AS CAST( CAST( SUBSTRING( [unused_c] ,1 ,LEN( [unused_c] ) - 3 ) AS DECIMAL( 18 ,2 ) ) / POWER( 10 ,6 ) AS DECIMAL( 18 ,2 ) )
)
DECLARE @object_schema_name sysname
SET @object_schema_name = :SCHEMA_NAME + '.' + :OBJECT_NAME
INSERT INTO #space_used
(
[name]
,[rows]
,[reserved_c]
,[data_c]
,[index_size_c]
,[unused_c]
)
EXECUTE sp_spaceused @object_schema_name
SELECT '* Table info*' + ' - ' + @object_schema_name + CHAR(10) +
'Object created: ' + CHAR(9) + convert(varchar, o.create_date) + CHAR(10) +
'Last modified: ' + CHAR(9) + CASE WHEN o.modify_date IS NULL THEN 'none' ELSE convert(varchar, o.modify_date) END + CHAR(10) +
CASE WHEN s.auto_created = 1
THEN 'Stats mode: ' + CHAR(9) + 'automatically updated'
ELSE 'Stats mode: ' + CHAR(9) + 'user updated'
END + CHAR(10) +
'Stats time: ' + CHAR(9) + isnull(convert(VARCHAR, STATS_DATE(o.[object_id], s.stats_id)), 'never') + CHAR( 10 ) +
(
SELECT
ISNULL( max(
'Rows: ' + CHAR( 9 ) + CAST( su.[rows] AS VARCHAR( 256 ) ) + CHAR( 10 ) +
'Reserved: ' + CHAR( 9 ) + CASE
WHEN su.reserved_kb < 1000 THEN CAST( su.reserved_kb AS VARCHAR( 256 ) ) + ' KB'
WHEN su.reserved_mb < 1000 THEN CAST( su.reserved_mb AS VARCHAR( 256 ) ) + ' MB'
ELSE CAST( su.reserved_gb AS VARCHAR( 256 ) ) + ' GB'
END + CHAR( 10 ) +
'Data: ' + CHAR( 9 ) + CASE
WHEN su.data_kb < 1000 THEN CAST( su.data_kb AS VARCHAR( 256 ) ) + ' KB'
WHEN su.data_mb < 1000 THEN CAST( su.data_mb AS VARCHAR( 256 ) ) + ' MB'
ELSE CAST( su.data_gb AS VARCHAR( 256 ) ) + ' GB'
END + CHAR( 10 ) +
'Index size: ' + CHAR( 9 ) + CASE
WHEN su.index_size_kb < 1000 THEN CAST( su.index_size_kb AS VARCHAR( 256 ) ) + ' KB'
WHEN su.index_size_mb < 1000 THEN CAST( su.index_size_mb AS VARCHAR( 256 ) ) + ' MB'
ELSE CAST( su.index_size_gb AS VARCHAR( 256 ) ) + ' GB'
END + CHAR( 10 ) +
'Unused: ' + CHAR( 9 ) + CASE
WHEN su.unused_kb < 1000 THEN CAST( su.unused_kb AS VARCHAR( 256 ) ) + ' KB'
WHEN su.unused_mb < 1000 THEN CAST( su.unused_mb AS VARCHAR( 256 ) ) + ' MB'
ELSE CAST( su.unused_gb AS VARCHAR( 256 ) ) + ' GB'
END)
,'Warning: Space usage stats not available'
)
FROM #space_used AS su
)
FROM sys.objects o
LEFT JOIN sys.stats s
ON s.[object_id] = o.[object_id]
AND s.stats_id = 1
WHERE o.[object_id] = :OBJECT_ID
UNION ALL
SELECT CHAR(10) + '* Table comment*' + CHAR(10) + convert(varchar(4000), [value])
FROM fn_listextendedproperty (default, 'schema', :SCHEMA_NAME, 'table', :OBJECT_NAME, default, default)
UNION ALL
SELECT CHAR(10) + '* Columns*'
UNION ALL
SELECT CHAR(10) + isnull(c.objname collate Latin1_General_CI_AS, ut.colname collate Latin1_General_CI_AS) + CHAR(9) + CASE WHEN ut.basetype IS NOT NULL THEN 'Base data-type: ' + ut.basetype collate Latin1_General_CI_AS + '; ' ELSE '' END
+ convert(varchar(4000), c.[value])
FROM fn_listextendedproperty(default, 'schema', :SCHEMA_NAME, 'table', :OBJECT_NAME, 'column', default) c
FULL JOIN
(SELECT c.name AS colname,
bt.name + case
when bt.name in ('varchar', 'char', 'binary', 'varbinary')
then '(' + case c.max_length when -1 then 'max' else convert(varchar, c.max_length) end + ')'
when bt.name in ('nvarchar', 'nchar')
then '(' + case c.max_length when -1 then 'max' else convert(varchar, c.max_length / 2) end + ')'
when bt.name in ('decimal', 'numeric')
then '(' + convert(varchar, c.[precision]) + ',' + convert(varchar, c.scale) + ')'
else ''
end
AS basetype
FROM
sys.all_columns c
JOIN sys.types bt
ON bt.user_type_id = c.system_type_id
AND bt.system_type_id = c.system_type_id
WHERE c.object_id = :OBJECT_ID
) ut
ON ut.colname collate Latin1_General_CI_AS = c.objname collate Latin1_General_CI_AS
WHERE ut.colname IS NOT NULL
OR c.objname IS NOT NULL
DROP TABLE #space_used
END
ELSE
SELECT CHAR(10) + '* Table comment*' + CHAR(10) + convert(varchar(4000), [value])
FROM ::fn_listextendedproperty (default, 'schema', :SCHEMA_NAME, 'table', :OBJECT_NAME, default, default)
UNION ALL
SELECT CHAR(10) + '* Columns*'
UNION ALL
SELECT CHAR(10) + isnull(c.objname collate Latin1_General_CI_AS, ut.colname collate Latin1_General_CI_AS) + CHAR(9) + CASE WHEN ut.basetype IS NOT NULL THEN 'Base data-type: ' + ut.basetype collate Latin1_General_CI_AS + '; ' ELSE '' END
+ convert(varchar(4000), c.[value])
FROM ::fn_listextendedproperty(default, 'schema', :SCHEMA_NAME, 'table', :OBJECT_NAME, 'column', default) c
FULL JOIN
(SELECT c.name AS colname,
bt.name + case
when bt.name in ('varchar', 'char', 'binary', 'varbinary')
then '(' + case c.length when -1 then 'max' else convert(varchar, c.length) end + ')'
when bt.name in ('nvarchar', 'nchar')
then '(' + case c.length when -1 then 'max' else convert(varchar, c.length / 2) end + ')'
when bt.name in ('decimal', 'numeric')
then '(' + convert(varchar, c.prec) + ',' + convert(varchar, c.scale) + ')'
else ''
end
AS basetype
FROM
dbo.syscolumns c
JOIN dbo.systypes bt
ON c.xtype = bt.xtype
AND c.xusertype = bt.xusertype
AND c.xusertype != c.xtype
WHERE c.id = :OBJECT_ID
) ut
ON ut.colname collate Latin1_General_CI_AS = c.objname collate Latin1_General_CI_AS
WHERE ut.colname IS NOT NULL
OR c.objname IS NOT NULL
|
[/code]
|
|
| Thu Sep 06, 2012 4:16 pm |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
