looking for a table name in text field  
Author Message
TBattheoffice





PostPosted: Tue Sep 20 05:37:28 CDT 2005 Top

SQL Server Developer >> looking for a table name in text field

I have table in one of applications that contains a text field that store sql
statements. I also have a list of tables. What I want to be able to query is
which statements these tables are referenced in.

SQL Server286  
 
 
Uri





PostPosted: Tue Sep 20 05:37:28 CDT 2005 Top

SQL Server Developer >> looking for a table name in text field Take a look at this SP written by Vyas Kondreddi
\

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


nvarchar(110)




BEGIN


(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)


BEGIN

(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS


AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')

)


BEGIN
INSERT INTO #Results
EXEC
(

@ColumnName + ', 3630)


)
END
END
END

SELECT ColumnName, ColumnValue FROM #Results
END



>I have table in one of applications that contains a text field that store
>sql
> statements. I also have a list of tables. What I want to be able to query
> is
> which statements these tables are referenced in.


 
 
Mike





PostPosted: Tue Sep 20 05:41:59 CDT 2005 Top

SQL Server Developer >> looking for a table name in text field This is a multi-part message in MIME format.
--------------020304050905020603000904
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Without seeing any schema it's a bit hard to advise, but I suspect your
query will look something like (not tested):

select * from dbo.StatementTable st
where exists
(
select * from dbo.TableList tl
where st.TextCol like '%' + tl.TableName + '%'
)

or

select st.StatementName, tl.TableName
from dbo.StatementTable st
inner join dbo.TableList tl
on st.TextCol like '%' + tl.TableName + '%'

Perhaps you could post some DDL and we could help a bit more.

--
*mike hodgson*
blog: http://sqlnerd.blogspot.com





>I have table in one of applications that contains a text field that store sql
>statements. I also have a list of tables. What I want to be able to query is
>which statements these tables are referenced in.
>
>

--------------020304050905020603000904
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Without seeing any schema it's a bit hard to advise, but I suspect
your query will look something like (not tested):<br>
</tt>
<blockquote><tt>select * from dbo.StatementTable st<br>
where exists<br>
    (<br>
    select * from dbo.TableList tl<br>
    where st.TextCol like '%' + tl.TableName + '%'<br>
    )<br>
</tt></blockquote>
<tt>or</tt><br>
<blockquote><tt>select st.StatementName, tl.TableName<br>
from dbo.StatementTable st<br>
    inner join dbo.TableList tl<br>
        on st.TextCol like '%' + tl.TableName + '%'<br>
</tt></blockquote>
<tt>Perhaps you could post some DDL and we could help a bit more.</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2">blog:</font><font face="Tahoma" size="2"> <a
href="http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>


type="cite">
<pre wrap="">I have table in one of applications that contains a text field that store sql
statements. I also have a list of tables. What I want to be able to query is
which statements these tables are referenced in.
</pre>
</blockquote>
</body>
</html>

--------------020304050905020603000904--
 
 
Russell





PostPosted: Tue Sep 20 07:15:03 CDT 2005 Top

SQL Server Developer >> looking for a table name in text field The table that has the sql String is the u_actions table in the ms_sql column
and the list of table names is tablesinuse.tablename. The ddl for both tables
is below:

CREATE TABLE [dbo].[U_ACTIONS] (
[U_ACTION_REF] [int] IDENTITY (1, 1) NOT NULL ,
[ACTION_NAME] [varchar] (40) NOT NULL ,
[ACTION_DESC] [varchar] (80) NULL ,
[ACTION_CLASS] [varchar] (40) NOT NULL ,
[UALIAS] [varchar] (40) NOT NULL ,
[VALIDATE] [tinyint] NOT NULL ,
[ENABLED] [bit] NOT NULL ,
[MS_SQL] [text] NULL ,
[ORACLE_SQL] [text] NULL ,
[FTYPES] [text] NULL ,
[KEYFIELD] [varchar] (30) NULL ,
[HTMLTEMPLATE] [varchar] (80) NULL ,
[HTMLHEADER] [varchar] (80) NULL ,
[HTMLFOOTER] [varchar] (80) NULL ,
[HTMLUPDATESUCCESS] [varchar] (80) NULL ,
[MAXROWS] [int] NULL ,
[CAPTION] [varchar] (100) NULL ,
[ROWTEXT] [text] NULL ,
[SUBQUERY] [text] NULL ,
[URIUPDATE] [varchar] (40) NULL ,
[ACTION_LEVEL] [tinyint] NULL ,
[ACTION_DEFAULT] [tinyint] NULL ,
[SUBMIT_TITLE] [varchar] (40) NULL ,
[SUBMIT_FILE_PATH] [varchar] (80) NULL ,
[SUBMIT_LEVEL] [tinyint] NULL ,
[SUBMIT_DEFAULT] [tinyint] NULL ,
[GENUNIQ_REF] [varchar] (30) NULL ,
[GENUNIQ_TABLE] [varchar] (30) NULL ,
[HTMLUPDATEFAILURE] [varchar] (80) NULL ,
[BOOLFIELD] [varchar] (30) NULL ,
[BOOLTEMPLATE0] [varchar] (80) NULL ,
[BOOLTEMPLATE1] [varchar] (80) NULL ,
[ROWTITLE] [text] NULL ,
[INSERTSQL] [text] NULL ,
[UPDATESQL] [text] NULL ,
[ALTACTION] [varchar] (40) NULL ,
[CUSTOMISED] [bit] NOT NULL ,
[SYS_CREATED_BY] [varchar] (30) NULL ,
[SYS_CREATED_ON] [datetime] NULL ,
[SYS_EDITED_BY] [varchar] (30) NULL ,
[SYS_EDITED_ON] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[tablesinuse] (
[tablename] [nvarchar] (50) NULL ,
[empref] [char] (2) NULL ,
[jobref] [char] (2) NULL ,
[row_count] [int] NULL
) ON [PRIMARY]
GO



> Without seeing any schema it's a bit hard to advise, but I suspect your
> query will look something like (not tested):
>
> select * from dbo.StatementTable st
> where exists
> (
> select * from dbo.TableList tl
> where st.TextCol like '%' + tl.TableName + '%'
> )
>
> or
>
> select st.StatementName, tl.TableName
> from dbo.StatementTable st
> inner join dbo.TableList tl
> on st.TextCol like '%' + tl.TableName + '%'
>
> Perhaps you could post some DDL and we could help a bit more.
>
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
>
>

>
> >I have table in one of applications that contains a text field that store sql
> >statements. I also have a list of tables. What I want to be able to query is
> >which statements these tables are referenced in.
> >
> >
>