SQL Server 2008 :: Tools To Search Database For Values
Nov 2, 2015
I tried to find a forum for "tools" with SQL Server 2008, but couldn't find anything, so hoping this is the right forum for my question.
I have recently installed RedGate SQL Search. Although I can use this tool for a lot of stuff, I mis-understood the details and thought it not only searched database objects, but would search inside individual rows inside tables for a value, but it does not do this.
I have been trying to search for a comparable product. I have found a bunch of stored procedures to do this, but I do not want to create a bunch of stored procedures for all the various databases we have, nor do I have the permission to do this.
Are there any tools like SQL Search that integrate (not a requirement, but definitely a nice bonus) with SSMS and will search the tables for a value in any given row, listing the table and column (it would be nice to list the entire row, but not required as with table and column, I can do a search for the row) for the given value?
I have found this product: [URL] ...., but I do not have any information on the company to know if they are reputable.
how best to approach a problem involving two tables across two different servers.
Table 1: Contains IP Address along with assessment findings. Lets say the fields are IPADDRESSSTR, FINDING
Table 2: Contains Subnet information stored in integer format. The fields are SITE_ID, LOW, and HIGH
What I'd like to do is load the IP range information into memory and then return the findings from table 1 where the IPADDRESSSTR is between the LOW and HIGH integer value.
1) Is there a way to load all of the ranges from table 2 into an array and then compare all the IP addresses (IPADDRESSSTR) from table 1?
2) How do I convert IPADDRESSSTR (a string) to an integer to perform the comparison.
I've installed the release of SQL Server 2008 but i couldnot find dundas control's.
I open SQL Server Business Intelligence but it still shows sql server 2005, why?....I know that 2008 have dundas control included, but i do not know how. If you can tell me how to refeer them I''ll be pleasure.
I downloaded and performed a full install of SQL Server Express 2008 Nov CTP. It was installed on a fresh installation of Windows Vista Enterprise x64.
During setup, I checked the 'Client Tools' box which has the description: "Includes management and development tools: SQL Server Management Studio, SQL Server Configuration Manager, SQL Server Profiler, and Database Engine Tuning Advisor"
The installation ran fine with no errors. After install I looked for the Management Studio but couldnt find it anywhere. No SQL Server Profiler or Database Tuning Advisor either. The only tool that appears to have installed is the SQL Server Configuration Manager.
If I check my Start Menu, the only 2008 items that exist are Microsoft SQL Server 2008 >
Configuration Tools >
SQL Server Configuration Manager SQL Server Error and Usage Reporting SQL Server Installation Center
I checked the summary log and everythign PASSED: Package results: Passed Execution statistics: Exit code: 0x00000000 Exit message: Passed Package start time: 12/13/2007 14:05:39 Package end time: 12/13/2007 14:09:06 Package install location: c:Program Files (x86)Microsoft SQL Server100Setup Bootstrap Package initial action: Install Media source location: Install Media version: 10.0.1075.23 Machine properties: Machine name: xxx Machine processor count: 2 OS version: Windows Vista OS service pack: OS language: English (United States) OS architecture: x64 Process architecture: 32 Bit Properties provided in package.xml file: LegalProductName: SQL Server Database Services 2008 Description: SQL Server Database Services 2008 Details: Install for SQL Server Database Services 2008 ProductName: SQL2008 Version: 10 SPLevel: 0 KBArticle: KB876234 KBArticleHyperlink: http://support.microsoft.com/?kbid=876234 Command line arguments provided: ACTION: Install INSTANCEIDSUFFIX: Product features discovered: Product: SQL2005 Product: SQL2008 Feature status after execution: Database Engine Services: Passed Replication: Passed Client Tools: Passed Windows Installer logs generated during execution: C:Program Files (x86)Microsoft SQL Server100Setup BootstrapLog20071213_1405T60-l3ac207_20071213_1405_Snac_Cpu64_1.log C:Program Files (x86)Microsoft SQL Server100Setup BootstrapLog20071213_1405T60-l3ac207_20071213_1405_Msxml6_Cpu64_1.log C:Program Files (x86)Microsoft SQL Server100Setup BootstrapLog20071213_1405T60-l3ac207_20071213_1405_Tools_Cpu32_1.log C:Program Files (x86)Microsoft SQL Server100Setup BootstrapLog20071213_1405T60-l3ac207_20071213_1405_SqlWriter_Cpu64_1.log C:Program Files (x86)Microsoft SQL Server100Setup BootstrapLog20071213_1405T60-l3ac207_20071213_1405_SqlBrowser_Cpu32_1.log C:Program Files (x86)Microsoft SQL Server100Setup BootstrapLog20071213_1405T60-l3ac207_20071213_1405_Sql_Cpu32_1.log
Interest rate has been stored in comments column along with other information ( e.g. mike's student loan is 5% and car payment is $ 150). I need to extract 5% using Contains .. Why Contains? because it's a 1.7 m rows dataset and searching for fours specific interest rate values (e.g. 3%, 9%, 12% and 15%)
DECLARE @search VARCHAR(10); SET @search = 'dog,COW'; WITH T(C) AS ( SELECT 'Cat,Dog,Sparrow,Trout,Cow,Seahorse' UNION ALL SELECT 'Cat,Dog,Sparrow,Trout,Cow,Seahorse' ) SELECT * FROM T WHERE C LIKE '%' + @search + '%'
I have a string in a cell 'Cat,Dog,Sparrow,Trout,Cow,Seahorse'
I want to search any of the two names from the string.
Search string:'cat,cow' Result:no result (not working),[size="7"][/size] Search string:'cat,dog' result :given string(working fine )
I would like to search for a particular stored procedure written by a developer. I know the name of the procedure but in which db is it residing in. There are 40 databases in this SQL 2008 instance. I search on the name column in sys.all_objects table and it does not return anything. I end up querying sys.procedures on each database to locate the procedure. Is there a system table/view that I can query to look for a procedure, instead of querying sys.procedures on each database one by one?
Say I want to search for a range of account numbers but only which are active. After I set my field for A (active) this field shows in my results, I dont want it to.
In Access you can easily just uncheck that field in design view, but how do I do it in sql?
I'm creating Indexed view by JOINING multiple tables and trying to create FULL TEXT search index. Unique column is generated by concatenating to ID columns from different table. I can successfully able to create unique index however when trying to create FULLTEXT INDEX getting below error.
"A full-text search key must be a unique, non-nullable, single-column index which is not offline, is not defined on a non-deterministic or imprecise nonpersisted computed column, does not have a filter, and has maximum size of 900 bytes. Choose another index for the full-text key."
The message clearly says the column should be single-column index, non-deterministic.
I am searching for the key word 'Platform Customer Support' using full text search. My code is as below
Set @KeywordSearch = 'Platform Customer Support'
Select AA, BB, CC, DD from SM9..TableName A Right Outer Join SM9_Experiment..TableName C On A.IncdTouchedGSF like '%' + C.SM9GroupName + '%' Where ( Contains(A.[Description], @KeyWordSearch) And A.OpenTime Between @StartDate and @EndDate And C.Classification = @GroupNameClassification )
The code is throwing:
Msg 7630, Level 15, State 3, Line 46 Syntax error near 'Customer' in the full-text search condition 'Platform Customer Support.
I have a query below which filters detail field in the #TempLogins table. The details field is a text field which contains many types of text strings, some containing urls that have parts like "ResultID=5" which is what is contained in the ResultIDSearch and ResultSetIDSearch fields. The records with entries like "ResultID=5" are the ones I'm trying to filter for.
The problem I have is that the query takes way too long to run. The TempLogin table has around 200 K records and the TempSearch table has around 80 K records.
select * from #TempLogins a where exists (select 1 from #TempSearch t1 where a.detail like '%' + t1.ResultIDSearch + '%' or a.detail like '%' + t1.ResultSetIDSearch + '%')
The tables share the common columns id and deptno. To get the above result, the emp1.edate must be between emp.sdate and emp.edate.
If that condition is met, we need to retrieve the refid and refid1 values corresponding to the lowest edate as last_refid and last_refid1. Then we need the refid and refid1 values corresponding to the emp1.edate between emp.stdate and emp.edate to be retrieved as the current_refid and current_ refid1.
select a.id,a.deptno,a.locid,b.refid,b.refid1 from emp b
I am new in SQL and i need do a query where I need sum values from 2 tables, when i do it the Sum values are not correct. this is my query
SELECT D.Line AS Line, D.ProductionLine AS ProductionLine, D.Shift AS Shift, SUM(CAST(D.DownTime AS INT)) AS DownTime, R.Category, SUM(Cast(R.Downtime AS INT)) AS AssignedDowntime, CONVERT(VARCHAR(10), D.DatePacked,101) AS DatePacked FROM Production.DownTimeReason R left JOIN Production.DownTimeHistory D
I have a table Tbl1 which has 7 columns.This table will be my base table.By using our current application version ,i'll be creating record for Client1. Col1 will have value that application will generate(id).Then i'll be creating Tbl2 with same columns.Then i'll be creating same record for Client1 again ,using our new application version .Col1 will have different (id)value.I would like to compare the rest of the columns if there is any discrepancy caused by new version(columns Col2 -Col7).If there are same ,don't show me anything.
I have a table of raw data with supplier names, and i need to join it to our supplier database and pull the supplier numbers.
The issue is that the raw data does not match our database entries for these suppliers; sometimes there are extra periods, commas, or abbreviations (i.e. FedEx, FederalExpress, FedEx, inc.) etc. I'm trying to create a query that will search for entries that are similar.
I tried setting a variable to be equal to the raw data field, and then using a LIKE '%@Variable%' to try and return anything that would contain it, but it didnt return any rows.
I want to concatenate the column_names with the respective values.
Create table #test1 (Id int,Name varchar(10),Country varchar(10))
insert into #test1 values ( 1,'JOHN','USA'), (2,'SAM','CANADA'), (3,'HO','CHINA'), (4,'RAM','INDIA')
select * from #test1
I have temp table with columns (Id,Name, Country). I want to concatenate column_name with their respective values; means i want column_header with every value for the column and then i want to concatenate multiple columns. I am looking for something like below:
ID values 1,NAME-john-COUNTRY-USA 2,NAME-SAM-COUNTRY-CANADA 3,NAME-HO-COUNTRY-CHINA 4,NAME-RAM-COUNTRY-INDIA
Note: This is just a sample .i am looking something dynamic because i have around 50 tables.i can do with query below but since i don't have static columns and table i am looking for something dynamic
select ID, (a+'-'+B) as Value from ( select ID,'NAME'+'-'+NAME as a,'Country'+'-'+Country as b from #test1
I've a table that has salescode(124!080) and salesamount(125.65!19.25) and I need to split the columns. Salesman(124) has commission(125.65). Here is the DDL:
USE tempdb; GO DECLARE @TEST_DATA TABLE ( DT_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED , InvNoVARCHAR(10) NOT NULL , SalesCode NCHAR(80) NOT NULL , Amount NCHAR(80) NOT NULL
I need to split the amount equally into 12 months from Jan 2015 through Dec 2015.There is no date column in the table and the total amount has to be splitted equally.Guess I can't use Pivot here because the date column is not there ...How can I achieve this ?
CREATE TABLE #tbl_data ( Region Varchar(25), Amount FLOAT,
I have imported a txtfile to access and have some problem making the data appear in my wanted way. I want to shift the cells in the first row one step to the left, i.e to overwrite the zeros with the left value. Then I want to assign the first row values as field names.
Current data
Field1 Field2 Field3 Field4 Field5 0 Name Major Sector Hi karl per anna
Each item will have 3 tags. I am having trouble on how to filter the data. For example if i chose TagID 1, 8, and 62, the result set should return only one result. If I do an IN clause, it acts like an OR and I need something to act like an AND.It seems like the only option is to do a dynamic where clause, but there are thousands of items and that might hinder performance of the database. Is there any other option?
I am trying to deploy the SQL Server 2008 R2 Native Client via group policy to a bunch of clients. But i am unable to find any documentation regarding if this is even supported?
I have the two files: sqlncli(x86).msi and sqlncli(x64).msi that i have attached to a GPO as en assigned application. But the x64 package fails with this error:
Product: Microsoft SQL Server 2008 R2 Native Client -- The required IACCEPTSQLCLILICENSETERMS=YES command-line parameter is missing. By specifying this parameter, you acknowkledge that you accept the end user license terms for the Microsoft SQL Server 2008 R2 Native Client.
32 bit installed without this error. But if there is any documentation on this type of deployment and how to pass the parameters to a normal GPO deployment.
I can't find any info on supported deployment options.
We had a requirement that need to sum the data based on quater we will be having 12 months data in the system for an year suppose we have 12 records for 2014 year. jan month sales data should be same when we were in feb month it should sum jan+feb sales and should show in sales column whereas we were in march month it should sum jan+feb+mar sales, then same for next quater also apr month it wil be same value in may it should be apr+may in may sales value etc ....
We will be having date column values as 201401,201402,.....
How can we implement in sql sever performance should be good.
I am stuck on a query where I need to display all the month year values even if the corresponding count_booking values are NULL. The requirement is to display the 13 month year period from current date.
For e.g. if the date exists in the current month then starting from May 15 go all the way back to Apr 14.
My current query works in terms of displaying all the 13 months if the count_booking values exist for all the months. However, if some months are missing the values then those months don't show up.how to display all the months even if the values are NULL ? Query below:
SELECT COUNT(m.BOOKING_ID) AS count_booking, LEFT(DATENAME(MONTH, m.CREATE_DT), 3) + ' ' + RIGHT('00' + CAST(YEAR(m.CREATE_DT) AS VARCHAR), 2) AS month_name FROM MG_BOOKING AS m WHERE (m.CREATE_DT >= DATEADD(m, - 13, GETDATE()))
select * from sys.master_files - size column value here is 1024 for .mdf,size here for .ldf is 64 select * from tempdb.sys.database_files - size column value here is 3576 for .mdf,size here for .ldf is 224
Why is there a difference and not the same. size columns in the above 2 tables for temp db's do they represent different values ?
I have created the below function and apply it on a column in a table to replace the below identified values with Blank. It works fine but i have so many different varieties of values i need to add to ths list. Is there any way i insert these values in a table and call the values from that table instead of writing separate SET Statements.
CREATE FUNCTION [dbo].[sv_ReplaceChar] (@badString varchar(8000)) RETURNS VARCHAR(8000) AS BEGIN
I have basic knowledge of T-SQL and I am using Cursors to get the first value, the last value and the peak value and some other values from other tables. I found some examples on google but the code I am using is mixed up. I am using multiple Cursors. I need to join three tables to get the result set into the Cursor. The first example uses 2 tables.
The above code seems totally inefficient but it gives the correct result. Now I want to pull some more value and join a third table (TABLE z) in the above CURSORS and not sure how to make it working using CURSORS.I would like to use the following in the CURSORS above.
SELECT x.publishdate, y.firstname, y.lastname, y.age, z.initialValue AS FirstValue, z.HighestValue AS Highest, z.LastValue AS Last FROM TABLE x LEFT OUTER JOIN TABLE y ON x.id = y.id INNER JOIN TABLE z ON x.id = z.id
I have a table with constantly changing data - stock.
I want to start monitoring the value of stock at the end of each month. I can do this with a simple query, export the results to Excel and store on the network.
My question is:
Are there better ways to do this with SQL Server?
I thought of a monthly "job" that does the query and outputs to a file. (Need to be able to look at each month separately though for trend monitoring.)
Then wondered, if I should have an extra table to store the data and write queries on that in the future?
