need to dump table and search for data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

need to dump table and search for data

Author

Topic

jenam
Starting Member

13 Posts

Posted - 2007-08-14 : 10:00:16

We purchased a software package that has a built in e-mail package. We now find ourselves in a position to audit the e-mails that are flying around. But the vendor is not telling us the name of the table that contains the text/body of the e-mails in order to audit the e-mails. My boss would like me to dump the tables to a text file and search through them looking for key words. There has to be a way to do that without dumping the tables, isn't there? Any information would be appreciated. Thank you! Jena

pootle_flump

1064 Posts

Posted - 2007-08-14 : 10:03:45

I would search the system catalog first - this is the point of least resistance. e.g.

DECLARE	@t_name AS SYSNAME
		, @c_name AS SYSNAME

SELECT	@t_name = N'%%'
		, @c_name = N'%email%'

SELECT	o.name AS t_name
		, c.name AS c_name
		, st.name AS data_type
		, c.max_length
		, c.scale
		, c.precision
FROM	sys.columns AS c
INNER JOIN 
		sys.objects AS o
ON	c.object_id = o.object_id
INNER JOIN	
		sys.systypes AS st
ON	st.xtype = c.system_type_id
WHERE	o.name LIKE @t_name
		AND c.name LIKE @c_name
ORDER BY o.name
		, c.column_id

There are techniques to do what you want but try that first as the cost is very low comparred to searching every item of data in every table.

pootle_flump

1064 Posts

Posted - 2007-08-14 : 10:06:51

BTW - I would escalate this with your vendor and ask for an explanation regarding why they cannot disclose the schema. Ultimately it is self defeating as you should be able to reconstruct it but it will take time -> ill feeling on your part.

jenam
Starting Member

13 Posts

Posted - 2007-08-14 : 10:14:48

Thank you for the code. I have tried twice to convince the vendor that we need this information. Their answer is that the e-mail is not robust or like smtp. I have a third attempt on the table now to get such information. Again, thank you for the code, I'll give it a shot. Jena

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources