if object_id('TableExplorer') is not null drop proc TableExplorer
go
/*
Stored procedure to query table metadata and produce windows explorer-like
information. The calling program can set the number and order of the columns,
the report sort order and set a filter on the table and schem name. There are
six available columns which each have a key character assigned to them. The
keys are used in the parameters for the procedure
Key Column
N Name
T Type (schema name)
S Size/Row Count
U last data update
C table created date
M table modified date
parameter:
@columnsIn Keys of columns to report in report order
@orderColumnsIn Keys sort columns
@orderDirectionIn for each sort column + for ascending - for descending
@nameFilterIn text to filter table name and schema name on
@alt 0 for normal report 1 to suppress the last-update column
(see article for explanation of this)
Examples:
exec TableExplorer 'N' -- name only sorted by name.
exec TableExplorer 'TN','TN' -- schema/name sorted in that order
exec TableExplorer 'NST','SN','-+' -- name/schema/rows sorted by rows high to low
exec TableExplorer 'NST','SN','-+','SALES' -- filter name and schema
exec TableExplorer 'NSTU',NULL,NULL,NULL,1 -- suppress update column
*/
create proc TableExplorer
@columnsIn varchar(6),
@orderColumnsIn varchar(6) = null,
@orderDirectionIn varchar(6) = null,
@nameFilterIn varchar(20) = null,
@alt int = 0
as begin
-- create fixed width holders for the input parameters to simplify processing
-- while setting these convert null to empty string or convert to upper case
declare @columns char(6), @orderColumns char(6), @orderDirection char(6), @nameFilter varchar(20)
set @columns = isnull(upper(@columnsIn),'')
set @orderColumns = isnull(upper(@orderColumnsIn),'')
set @orderDirection = isnull(upper(@orderDirectionIn),'')
set @nameFilter = isnull(@nameFilterIn, '')
-- Here is the template for the code described in the CodeProject article
-- tags are grouped as follows
-- %Cn report columns
-- %On sort columns
-- %S1 name search filter activator %S2 name search value %S3 schema search clause enabler
-- %Jn join enable/disable values (ie. either -- to disable or empty to enable joins
declare @sql varchar(max)
set @sql =
'select %C1%C2%C3%C4%C5%C6
from sys.objects o
%J1 left join sys.schemas sc on sc.schema_id = o.schema_id
%J2 left join (SELECT object_id, SUM(rows) as row_count FROM sys.partitions
%J2 WHERE index_id < 2
%J2 group by object_id) s on o.object_id = s.object_id
%J3 left join (select database_id, object_id, max(last_user_update) as last_user_update
%J3 from sys.dm_db_index_usage_stats
%J3 group by database_id,object_id) u on u.object_id = o.object_id and u.database_id = DB_ID()
where o.type = ''U''
%S1 and (o.name like ''%%S2%'' %S3 or sc.name like ''%%S2%'')
order by %O1%O2%O3%O4%O5%O6
'
-- associate keys to report columns: refer to the table aliases in the template above.
-- the keys are expected in the rreport configuration parameters.
--
declare @sourceColumns table (col char(1), columnDef varchar(30), alias varchar(20))
insert into @sourceColumns (col, columnDef, alias)
select 'N', 'o.name', 'TableName' union
select 'S', 's.row_count', 'Rows' union
select 'T', 'sc.name', 'Type' union
select 'U', 'u.last_user_update', 'Modified' union
select 'C', 'o.create_date', 'Created' union
select 'M', 'o.modify_date','LastSchemaChange'
-- create a working table:
-- inputs in.
-- working values computed for creating the values that replace the tags
-- template parameters and values to substitute out.
declare @paramList table (paramChar char(1), -- IN template parameter group C = report col, J = join etc.
paramOrder tinyint, -- IN column ordering
columnKey char(1), -- WV key to field selected for the parameter
descKey varchar(5), -- WV sort only: sets desc keyword where necessary
param varchar(20), -- OUT template tag
value varchar(max)) -- OUT value
-- prime the template parameter table. Set the components of the template tag
-- associate this to a column key from the input parameter and set default values.
insert into @paramList (paramChar, paramOrder, columnKey, value)
select 'C',1,substring(@columns, 1, 1),'' union
select 'C',2,substring(@columns, 2, 1),'' union
select 'C',3,substring(@columns, 3, 1),'' union
select 'C',4,substring(@columns, 4, 1),'' union
select 'C',5,substring(@columns, 5, 1),'' union
select 'C',6,substring(@columns, 6, 1),'' union
select 'O',1,substring(@orderColumns, 1, 1),'' union
select 'O',2,substring(@orderColumns, 2, 1),'' union
select 'O',3,substring(@orderColumns, 3, 1),'' union
select 'O',4,substring(@orderColumns, 4, 1),'' union
select 'O',5,substring(@orderColumns, 5, 1),'' union
select 'O',6,substring(@orderColumns, 6, 1),'' union
select 'J',1,null,'--' union select 'J',2,null,'--' union select 'J',3,null,'--' union
select 'S',1,null,'--' union select 'S',2,null,'' union select 'S',3,null,')--'
-- build template parameters eg. 'C' 1 -> %C1
update @paramList set param = '%' + paramChar + LTRIM(STR(paramOrder))
-- logic
-- Cater for the case where no sort order is given
-- sort by first column ie Name
if LEN(LTRIM(@orderColumns)) = 0
update @paramList set value = '1' where paramChar='O' and paramOrder=1
-- Cater for the case where no columns are given
-- sort by first column ie Name
if LEN(LTRIM(@columns)) = 0
update @paramList set columnKey = 'N' where paramChar='C' and paramOrder=1
-- assign report columns to template tags
update p set value = case paramOrder when 1 then '' else ',' end + columnDef+' as '+alias
from @paramList p
join @sourceColumns t on t.col = p.columnKey and p.paramChar = 'C'
-- determine ascending descending selection for sort columns.
-- activate the descending keyword when '-' is set in the input parameter
update p set descKey = case when substring(@orderDirection, p.paramOrder,1) = '-' then ' desc' else '' end
from @paramList p
where paramChar = 'O'
-- assign sort columns
update p set value = columnDef + descKey
from @paramList p
join @sourceColumns t on t.col = p.columnKey and p.paramChar = 'O'
-- enable join logic J parameters
-- check both the report and sort columns.
update @paramList set value = ''
where paramChar = 'J'
and ( (paramOrder = 1 and (charindex('T',@columns)> 0 or charindex('T',@orderColumns)> 0)) -- Type required
or (paramOrder = 2 and (charindex('S',@columns)> 0 or charindex('S',@orderColumns)> 0)) -- Size
or (paramOrder = 3 and (charindex('U',@columns)> 0 or charindex('U',@orderColumns)> 0)) ) -- Last data update
-- Name filter logic
-- take defaults if there is no filter
if (len(@nameFilter)>0)
begin
update @paramList set value = ''
where paramChar = 'S' and paramOrder = 1 -- S1: enable the name search clause
update @paramList set value = @nameFilter
where paramChar = 'S' and paramOrder = 2 -- S2: set the search value
update @paramList set value = case when charindex('T',@columns)> 0
then ''
else ')--' end
where paramChar = 'S' and paramOrder = 3 -- S3: activate the schema search clause
end
-- uncomment this to debug the script
-- select * from @paramList
-- disable if alt is set
if @alt = 1
begin
update @paramList set value = '--' where paramChar ='J' and paramOrder in (3)
update p set value = case paramOrder when 1 then '' else ',' end + '''n.a.''' +' as '+alias
from @paramList p
join @sourceColumns t on t.col = p.columnKey and p.paramChar = 'C' and p.columnKey in ('U')
update @paramList set value = '' where paramChar = 'O' and columnKey in ('U')
end
-- add commas where necessary
update @paramList set value = ','+value
where paramChar = 'O' and len(value) > 0
and paramOrder > (select min(paramOrder) from @paramList where paramChar = 'O' and len(value)>0)
-- add default sort order
if (select count(*) from @paramList where paramChar = 'O' and len(value) > 0) = 0
update @paramList set value = '1' where paramChar='O' and paramOrder=1
-- Apply parameters to the template
-- Note this is safe as there are no nested tags in any values.
select @sql = replace(@sql, param, value)
from @paramList
--uncomment this to see what has been built
--print @sql
exec (@sql)
end
go
Submit an article or tip