search database for a value

 
 

CREATE PROC SearchAllTables  
 (  
      @SearchStr nvarchar(100)  
 )  
 AS  
 BEGIN  
      -- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.  
      -- Purpose: To search all columns of all tables for a given search string  
      -- Written by: Narayana Vyas Kondreddi  
      -- Site: http://vyaskn.tripod.com  
      -- Tested on: SQL Server 7.0 and SQL Server 2000  
      -- Date modified: 28th July 2002 22:50 GMT  
      CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))  
      SET NOCOUNT ON  
      DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)  
      SET @TableName = ''  
      SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')  
      WHILE @TableName IS NOT NULL  
      BEGIN  
           SET @ColumnName = ''  
           SET @TableName =   
           (  
                SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))  
                FROM      INFORMATION_SCHEMA.TABLES  
                WHERE           TABLE_TYPE = 'BASE TABLE'  
                     AND     QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName  
                     AND     OBJECTPROPERTY(  
                               OBJECT_ID(  
                                    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)  
                                     ), 'IsMSShipped'  
                                   ) = 0  
           )  
           WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)  
           BEGIN  
                SET @ColumnName =  
                (  
                     SELECT MIN(QUOTENAME(COLUMN_NAME))  
                     FROM      INFORMATION_SCHEMA.COLUMNS  
                     WHERE           TABLE_SCHEMA     = PARSENAME(@TableName, 2)  
                          AND     TABLE_NAME     = PARSENAME(@TableName, 1)  
                          AND     DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')  
                          AND     QUOTENAME(COLUMN_NAME) > @ColumnName  
                )  
                IF @ColumnName IS NOT NULL  
                BEGIN  
                     INSERT INTO #Results  
                     EXEC  
                     (  
                          'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)   
                          FROM ' + @TableName + ' (NOLOCK) ' +  
                          ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2  
                     )  
                END  
           END       
      END  
      SELECT ColumnName, ColumnValue FROM #Results  
 END  

Dynamic Cross-Tabs/Pivot Tables

PROCEDURE:

 
CREATE PROCEDURE crosstab
@select varchar(8000),
@sumfunc varchar(100),
@pivot varchar(100),
@table varchar(100)
AS

DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE '
+ @pivot + ' Is Not Null')

SELECT @sql='',  @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )

SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )
WHEN 0 THEN '' ELSE '''' END
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pivot'

SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' +
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN '
+ @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM ##pivot

DROP TABLE ##pivot

SELECT @sql=left(@sql, len(@sql)-1)
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')

EXEC (@select)


USAGE:
execute crosstab
'select ItemNo from vtbl_ModelStock Group By ItemNo', 'sum(ModelStockQty)', 'CompanyID', 'vtbl_ModelStock'
SET ANSI_WARNINGS ON
SOURSE:
http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables

microsoft office access was unable to create an mde database

MS Access Error :
"microsoft office access was unable to create an mde database"
Solution:
- Run MSACCESS.EXE /decompile
- Edit a form that contains a code/event
- On Microsoft VB code click Debug then Compile the project - this will remove all the unnecessary codes.
- Save it back then recreate an MDE