Seaching a value in a Table

The below stored procedure is used for searching a particular string in a table, if the string exists it will return the column name and correct search value.

Stored Procedure :

create PROC SearchTable
(@SearchStr nvarchar(100),
@TableName nvarchar(256))
AS
BEGIN
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
DECLARE @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
SET @ColumnName = ''
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
SELECT ColumnName, ColumnValue FROM #Results
END

Output

Suppose we have a table called dbo.airways and the data it contains



here I would like to search the string 'amm', so my execute statement is like

EXEC SearchTable 'amm','dbo.airways'

Result :

Display all the table names from all the databases if they contains specific column name

The following query will display all the table names from all the databases if they contains specific column name. Here my column name is 'Y', so I want retrived all the table names from all the databases which are having 'Y' as one of the column. I am taking column name 'Y' in to one variable @Column_Val, so it will give all the database names and their corresponding table names which consists 'Y' as a column.


DECLARE @CMD VARCHAR(1000)

DECLARE @Column_Val VARCHAR(1000)

set @Column_Val='Y'

create table #TableNames ( DatabaseName sysname, TableName sysname)

SET @CMD = 'USE [?]; SELECT DB_NAME()DATABASE_NAME,A.name as Tablename from
( (select * from SYS.Tables) A join (SELECT * FROM SYS.columns where name like''' +@Column_Val+ ''') B on A.object_id=B.object_id)'

insert into #TableNames EXEC SP_MSFOREACHDB @CMD
SELECT * FROM #TableNames Where DatabaseName not in ( 'master','msdb', 'tempdb', 'model' )
drop table #TableNames

Extracting Cube data from SSAS using SSIS (SqlServer 2005)

Cube data can be extracted through MDX queries, we have three approaches for getting the results of an MDX query into an SSIS data stream. Out of these three approaches only last two approaches are working fine.

1. To setup a connection using the OLE DB provider for MSOLAP 9.0 and then put an MDX query in place of the SQL text. This seems to work when you Preview, but crashes SSIS when you run the job.

2. Use a linked server and an OpenQuery command. First we need to define linked server for our source catalog, then we can easily retrieve data by using open query method.
USE master
GO
/* Add new linked server */
EXEC sp_addlinkedserver
@server='LINKED_OLAP', -- local SQL name given to the linked server
@provider='MSOLAP.3', -- OLE DB provider (the .2 means the SQL2K version)
@datasrc='localhost', -- analysis server name (machine name)
@catalog='Adventure Works DW' -- default catalog/database
And then I wrapped my MDX statement in an openquery call.
Ex:
SELECT *
FROM OpenQuery (linked_olap,'SELECT --measures.members
{Measures.[Internet Sales Amount]} ON COLUMNS,
[Date].[Month].members ON ROWS
FROM [Adventure Works]')
Once we prepared this openquery then put this command in place of SQL Query. It works like a normal sql query without any errors.
3. Create a new ADO.Net Connection and from the .Net Providers\SQLClient Data Providers list choose the OLE DB Provider for AS9.0, then create a DataReader Source adapter connected to the new AS connection manager. Put the MDX into the SQLCommand property (be sure to map the external and output columns). This obviously flattens the cell set, but it doesn't limit what you put on the axes. The DataReader Source adapter turns all the columns into DT_NTEXT, which you then have to convert to DT_WSTR to be able to convert to anything else, but that's standard SSIS data type manipulation. Seems a lot slicker than creating a linked server.