-------------------------------------------------------------------- Stored Procedure: sp_FindColumn -------------------------------------------------------------------- USE master GO CREATE PROCEDURE dbo.sp_FindColumn @DBIntra varchar(8000) = NULL, @DBExtra varchar(8000) = NULL, @PCIntra varchar(100) = NULL, @PCExtra varchar(100) = NULL, @PCUltra bit = 0 AS SET NOCOUNT ON DECLARE @Return int DECLARE @Retain int DECLARE @Status int SET @Status = 0 DECLARE @TPre varchar(10) DECLARE @TDo3 tinyint DECLARE @TDo4 tinyint SET @TPre = '' SET @TDo3 = LEN(@TPre) SET @TDo4 = LEN(@TPre) + 1 CREATE TABLE #DBAH (TName varchar(100), CName varchar(100), CList smallint, CKind varchar(20), CSize int, CWide smallint, CMore smallint) INSERT #DBAH SELECT O.name , C.name , C.colid , T.name , C.length , C.prec , C.scale FROM sysobjects AS O JOIN syscolumns AS C ON O.id = C.id JOIN systypes AS T ON C.xusertype = T.xusertype WHERE ISNULL(OBJECTPROPERTY(O.id,'IsMSShipped'),1) = 0 AND RTRIM(O.type) = 'U' AND LEFT(O.name,@TDo3) = @TPre AND O.name NOT LIKE 'adt%' AND O.name NOT LIKE '%dtproper%' AND O.name NOT LIKE 'dt[_]%' AND (@DBIntra IS NULL OR CHARINDEX('|'+C.name+'|','|'+(@DBIntra)+'|') > 0) AND (@DBExtra IS NULL OR CHARINDEX('|'+C.name+'|','|'+(@DBExtra)+'|') = 0) AND (@PCIntra IS NULL OR C.name LIKE @PCIntra) AND (@PCExtra IS NULL OR C.name NOT LIKE @PCExtra) SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain SELECT CASE WHEN @PCUltra = 0 THEN SUBSTRING(TName,@TDo4,100) ELSE TName END AS TName, CName, CList, CKind, CSize, CWide, CMore FROM #DBAH ORDER BY TName, CList DROP TABLE #DBAH SET NOCOUNT OFF RETURN (@Status) GO -------------------------------------------------------------------- Stored Procedure: sp_AnalyzeColumn -------------------------------------------------------------------- USE master GO CREATE PROCEDURE dbo.sp_AnalyzeColumn @DBFetch varchar(4000), @DBField varchar(100) AS SET NOCOUNT ON DECLARE @Return int DECLARE @Retain int DECLARE @Status int SET @Status = 0 DECLARE @TPre varchar(10) DECLARE @TDo3 tinyint DECLARE @TDo4 tinyint SET @TPre = '' SET @TDo3 = LEN(@TPre) SET @TDo4 = LEN(@TPre) + 1 DECLARE @Task varchar(8000) DECLARE @Bank varchar(4000) DECLARE @Wish varchar(100) SET @Bank = @TPre + @DBFetch IF NOT EXISTS (SELECT * FROM sysobjects WHERE RTRIM(type) = 'U' AND name = @Bank) BEGIN SET @Bank = CASE WHEN LEFT(@DBFetch,6) = 'SELECT' THEN '(' + @DBFetch + ')' ELSE @DBFetch END SET @Bank = REPLACE(@Bank, CHAR(94),CHAR(39)) SET @Bank = REPLACE(@Bank,CHAR(45)+CHAR(45),CHAR(32)) SET @Bank = REPLACE(@Bank,CHAR(47)+CHAR(42),CHAR(32)) END SET @Wish = REPLACE(@DBField,CHAR(32),CHAR(95)) SET @Task = ' SELECT T.' + @Wish + ' AS [Value], COUNT(*) AS [Records],' + ' CONVERT(decimal(5,2),(COUNT(*)*100.00)/CONVERT(decimal(12,2),MAX(Z.Rows))) AS [Percent]' + ' FROM ' + @Bank + ' AS T, (SELECT COUNT(*) AS Rows FROM ' + @Bank + ' AS I) AS Z' + ' GROUP BY T.' + @Wish + ' ORDER BY [Records] DESC, [Value]' IF @Status = 0 EXECUTE (@Task) SET @Return = @@ERROR IF @Status = 0 SET @Status = @Return SET NOCOUNT OFF RETURN (@Status) GO --------------------------------------------------------------------