March 14, 2011 at 3:26 pm
I have a table that has create statements in a column in my table. What I would like to do is test for a certain value in the string & only bring back a specific number of characters in the string.
In example; A create procedure statment which contains dbname.dbo.xx_blah_tbl.
What sort of Sql query could I use to both test & retrieve %dbname.dbo.xx_blah_tbl%
Sql server 2005
March 14, 2011 at 3:33 pm
Look up LIKE in books online. This is how the link comes up for me:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/581fb289-29f9-412b-869c-18d33a9e93d5.htm
select * from mytable where mycolumn LIKE '%db__name%'
if the column is text/ntext then us a variation on CAST(mycolumn as varchar(2000)
March 14, 2011 at 3:40 pm
jude-32296 (3/14/2011)
I have a table that has create statements in a column in my table. What I would like to do is test for a certain value in the string & only bring back a specific number of characters in the string.In example; A create procedure statment which contains dbname.dbo.xx_blah_tbl.
What sort of Sql query could I use to both test & retrieve %dbname.dbo.xx_blah_tbl%
Sql server 2005
DECLARE @TableName VARCHAR(500)
SELECT @TableName from <sometable> WHERE <somecolumn> LIKE '%' + @TableName + '%'
Now, as to more explicit characters from the string, you're going to have to be more specific if you want more then just what the table name you're looking for is.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 15, 2011 at 11:20 am
thanx. When i do a like statment it brings back the entire string, which I know is correct. What I am hoping to do is just pull out the table name such as like '%dbname.Schema.table%'
I am attempting to put a script together to get cross database references & it seems an impossilble task (Not allowed to buy any 3rd party software either)
March 15, 2011 at 11:30 am
jude-32296 (3/15/2011)
thanx. When i do a like statment it brings back the entire string, which I know is correct. What I am hoping to do is just pull out the table name such as like '%dbname.Schema.table%'I am attempting to put a script together to get cross database references & it seems an impossilble task (Not allowed to buy any 3rd party software either)
Can you post some sample data? We might be able to see something.
Hint - read the first link in my signature first for how you can help us help you!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
March 15, 2011 at 12:41 pm
My table has 2 columns;
Name & text
Name = the proc name
Text = the create procedure script - so in the mess below (text column) I would like to extract all the dbname.schema.table names from the text column
Name = dbo.xxx_xxx_1_PROC
CREATE procedure dbo.xxx_xxx_1_PROC
AS DECLARE @i int , @Currdate datetime, @d_inv float, @h_inv float, @q_inv float, @z_inv float,
@co varchar(2), @whs varchar(4), @item varchar(20), @pack varchar(6), @pack_fill float
BEGIN
SELECT @Currdate = getdate() TRUNCATE TABLE dbo.xxx_TBL_USR
insert dbo.xxx_TBL_USR( Co , R_T_type , Itgrp , Item , Descr , Pack , Pack_fill ,
Req_Date , Net_Bulk_D , Net_Bulk_H , Net_Bulk_Q , Net_Bulk_Z , Net_alloc , Whs ,
Whs_type , Data_Type , Data_subtype , Data_ID , Suffix , Sort1 , Sort2 )
SELECT a.gl_cmp_key, right(d.en_item_key,1), substring(d.en_itgrp_key,1,2), d.en_item_key,
d.en_item_desc, '', 1, @currdate, sum(a.in_srl_qoh), 0, 0, 0, 0, a.in_whs_key,
f.en_whs_type, 'IN', 'D', 'On Hand', g.en_itqsp_tot, '', ''
FROM dbname.dbo.in_xxx_tbl a(nolock)
INNER JOIN dbname.dbo.xx_lot_tbl b(nolock) ON a.in_lot_key = b.en_lot_key
AND a.in_item_key = b.en_item_key INNER JOIN dbname.dbo.xx_item_tbl d(nolock)
ON a.in_item_key = d.en_item_key INNER JOIN dbname.dbo.xx_stats_tbl e(nolock)
ON d.en_stats_key = e.en_stats_key INNER JOIN dbname.dbo.xx_whs_tbl f(nolock)
ON a.in_whs_key = f.en_whs_key INNER JOIN dbname.dbo.xx_qc_tbl_usr g(nolock)
ON a.in_lot_key = g.en_lot_key AND a.in_item_key = g.en_item_key WHERE a.in_srl_qoh > 40.0
AND (d.en_item_key like '%R' or d.en_item_key like '%T') AND substring(d.en_itgrp_key,1,1) = 'R'
AND e.en_stats_type = '1' AND substring(b.en_grade_key,1,1) = 'D' group by a.gl_cmp_key,
d.en_item_key, d.en_itgrp_key, d.en_item_desc, a.in_whs_key, f.en_whs_type, g.en_itqsp_tot
insert dbo.xxx_TBL_USR ( Co , R_T_type , Itgrp , Item , Descr , Pack , Pack_fill ,
Req_Date , Net_Bulk_D , Net_Bulk_H , Net_Bulk_Q , Net_Bulk_Z , Net_alloc , Whs ,
Whs_type , Data_Type , Data_subtype , Data_ID , Suffix , Sort1 , Sort2 )
SELECT a.gl_cmp_key, right(d.en_item_key,1), substring(d.en_itgrp_key,1,2), d.en_item_key,
d.en_item_desc, '', 1, @currdate, 0, sum(a.in_srl_qoh), 0, 0, 0, a.in_whs_key,
f.en_whs_type, 'IN', 'H', 'On Hand', '', '', '' FROM dbname.dbo.xx_srl_tbl a(nolock)
INNER JOIN dbname.dbo.xx_lot_tbl b(nolock) ON a.in_lot_key = b.en_lot_key AND a.in_item_key = b.en_item_key INNER JOIN dbname.dbo.xx_item_tbl d(nolock)
ON a.in_item_key = d.en_item_key
INNER JOIN dbname.dbo.xx_stats_tbl e(nolock) ON d.en_stats_key = e.en_stats_key
March 15, 2011 at 12:43 pm
btw, sorry if the format of my post isn't good, but I wanted to replicate what the big string looks like in my table. Also, if you know of a good way to get references that cross databases, I would appreciate any suggestions.
thx
March 15, 2011 at 3:55 pm
I think you are going to have to look for it with the PATINDEX function - specify " %.%.%" as the pattern. This will return the position where the space is just before the table name.
The trick will be that you will then have to perform this in a loop to keep moving further down the string to find the next occurrence. Do you know how to use a Tally table to replace loops with set-based code? Check out this article: The "Numbers" or "Tally" Table: What it is and how it replaces a loop.[/url]
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
March 15, 2011 at 3:55 pm
jude-32296 (3/15/2011)
Also, if you know of a good way to get references that cross databases, I would appreciate any suggestions.thx
I'm not sure what you mean by this... can you elaborate on it please?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
March 16, 2011 at 7:13 am
dependencies across databases; xxx_xxx_proc references 1 table in the database it lives in and als refers to a table in another database. How can i list all dependencies of an object without a 3rd party tool (no money)
thx
March 16, 2011 at 8:16 am
Using DelimitedSplit8K code and a Tally table
WITH split (ItemNumber,Item) AS (
SELECT ROW_NUMBER() OVER (ORDER BY t.N) AS [ItemNumber],
SUBSTRING(tbl.[Text], t.N, CHARINDEX(' ', tbl.[Text] + ' ', t.N) - t.N) AS [Item]
FROM (SELECT x.Name,CAST(x.[Text] as varchar(max)) AS [Text] FROM [tablename] x) tbl
JOIN dbo.Tally t ON t.N < LEN(tbl.[Text]) + 2 AND SUBSTRING(' ' + tbl.[Text], t.N, 1) = ' '
)
SELECT s2.Item
FROM split s1
JOIN split s2 ON s2.ItemNumber=s1.ItemNumber+1
WHERE s1.Item IN ('JOIN','FROM')
Note this will only do the first 8K chars, I think there is a varchar(max) solution on this site somewhere I just can't seem to find it
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply