So I use this to search all fields for data:
GO
EXEC SearchAllTables '318956'
GO
That finds everything with 318956 in it. How do ut do it to the have it return only things with that only. I tried SearchAllTables = '318956', and that errors out.
Thanks
that is not a standard system stored procedure in SQL Server. Not sure what do you want to do. Perhaps you need to modify that stored procedure to gives you want you need
I think you should read this article:
Search all string columns in all SQL Server databases - MSSQLTips.com
hi
hope this helps
EXEC Searchalltables 'Billing'
CREATE PROC [dbo].[searchalltables] ( @SearchStr NVARCHAR(100) )
AS
BEGIN
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 distinct columnname,
columnvalue
FROM #results
END
GO
On the left hand side of the SQL Server Management studio, in the Object Explorer, Find the database, drill down into "Programmability", "Stored Procedures", and right click on the Stored Procedure , SearchAllTables. Select "Script Stored Procedure as Create To:. Post that code so we can see whats going on.
ah, your using the Adventureworks test database.... I couldnt see the code from the store procedure you posted. However, that doesnt matter. Your better off just using ad-hock queries. If you use one query a lot, you can make it a Stored procedure later.
Here is what you got....
The database is Adventureworks2022, the table name is person.address (which is the schema "Person" with the table "address", You dont normally see it that way).
So you could do these examples:
USE Adventureworks -- The using declaration sets the database, so you dont have to include it in every query
SELECT *
FROM Person.Address
WHERE [columnName] = '318956'
Its all about the WHERE clause.
WHERE Person.addressline1 LIKE '318956%' --Street addresses that starts with 318956 (% is a wildcard)
WHERE Person.Postalcode = '318956' Zip codes that equal 318956
Hope this helps.
Nope not their database. It's just our own. Mainly I would like to to look through several different columns, and tables. Not just one.
