I have a task that requires me to pull a list of tables used in a select query and put them into a string array. For arguments sake, let's say this is my query:
select *
from table1 tb1, table2 tb2
where ...
I can easily extract the 'from 'clause, in this case: table1 tb1,table2 tb2, but I still have to split out the Alias before I can use it. Can anyone show me a regex that can do this instead?
I am using the following select statement to get the row count from SQL linked server table.
SELECT Count(*) FROM OPENQUERY (CMSPROD, 'Select * From MHDLIB.MHSERV0P')
MHDLIB is the library name in IBM DB2 database. The above query gives me only the row count of table MHSERV0P. However, I need to get the names, rowcounts, and sizes of all tables that exist in MHDLIB librray. Is it possible at all?
Hello everyone,sorry for my bad English. I have to create a query under mySQL for asmall research engine; I have to rate the results of a simple regularexpression matches inside a text field. I gave a look to the function REGEXPthat mySQL offers, but it seems do give just a boolean result, 1 or 0!!!What i need is that the regexp funtion gave back the number of the totalmatches occured inside a field of every line. Do you know if mySQL offersthis opportunity? Maybe using onother function instead REGEXP?Thank you all,Federico.
Hi guys,Sounds a bit strange, however, if we could put some calculation instored procedure it would be quite convenient, just... where can I finda REGEXP library for matching checking? thanks.yours,athos
********************alt.php.sql,compdatabases.ms-sqlservermicrosoft.public.sqlserver.programming***********************************Why doesn't this work:SELECT *FROM 'Events'WHERE dayofweekREGEXP 'monday' OR description REGEXP 'monday'When this does work:SELECT *FROM `Events`WHERE dayofweekREGEXP 'monday'
I am using SQL 2005 merge replication with a publisher managing about 45 articles(tables) with about 10 subscribers (remote servers). The problem is that we had to re-start replication from scratch and noticed that, although the publisher's tables have the default values, the subscribers did not get the default values with the initial snap shot, schema building..?!?
I now have to go over 450 tables (10 remotes SQl servers at 45 tables each) and 'reset or set' over 1,000 default values. Meanwhile, the system is down...omg...so not good.
Is there a script out there that automatically extracts the default values from a table and set it to another exact table with the same structure? any ideas?
Is there a quick way to extract a full dump of 50 tables to 50 corresponding text files?
i.e.
table_a has to be extracted to table_a.txt
table_b has to be extracted to table_b.txt
table_c has to be extracted to table_c.txt etc.
I don't want to have to add each one separately by hand in the DTSX package designer. I can't see any way to do it in a loop (because you have to do the field mapping). I can't seem to get the DTS Wizard to help - it only seems to be able to handle one table-to-text extract at any one time. And I've tried editing the DTXS file directly (in XML) but it looks like it's going to be rather complex, even if I only do it to define the connection managers. Feel free to suggest any better way to do this, though the specification has already been agreed, so I'm unlikely to be able to change it. Thanks
I am writing a program that transforms a generic MS SQL database to make it compatible with our application. In order to make the transformation, I have to access all the elements of the generic database programmatically.
I know that the Master database contains all this information. Is there a query that allows me to access the "non-system" tables, sps, views, and functions?
For Tables, I need to know the Name(s) of the tables, the column names, the column type, ALLOW Nulls, Primary Key, Identity Seed settings, and Triggers.
For SPs, I need to know the Name(s) and access the SP source code (assuming it's unencrypted).
For Views, I need to know the Name(s) and access the Views Source
For functions, I need to know the Name(s) and access the function source.
I can see various tables in the Master database from management studio, like sys.objects, sys.tables - which look like they have the info I need. However, when I run a query against master like:
select * from sys.objects .. I get an error:
Msg 208, Level 16, State 1, Line 1 Invalid object name 'sys.objects'.
I need to export multiple tables from a database to multiple csv files (one for each table).
Rather than use SSIS and have multiple OLEDB sources and destinations (one for each table), is there a way to have a generic package that will export all the tables in the database ?
One way I can see is to use BCP in a loop - with the loop powered by a select statement that links to something like sys.tables etc, (or another table that i prepped with just the tables I want if I dont want them all).
i.e I would use a stored procedure that uses BCP (called via XPcmdShell) - so not via SSIS - although I could wrap up the whole thing in SSIS - but there is no realy need.
Hi i have a query select 12 * int( "SQLUSER"."BTT120".yyyy) + int("SQLUSER"."BTT120".mm) as actual, 12*year(CURRENT DATE) + month(CURRENT DATE) as ending, 12*year(CURRENT DATE) + month(CURRENT DATE)-3 as starting from "SQLUSER"."BTT120" where "SQLUSER"."BTT120".yyyy ='2007' and "SQLUSER"."BTT120".mm in ('10','11','12')
which gives me ourput
Actual ending Starting 24094 24097 24094
My actual requirment is to get the data for the rolling 3 months for which i used the same logic as above in the WHERE clause.
SELECT "SQLUSER"."BTT120".YYYY CONCAT "SQLUSER"."BTT120".MM AS MO_YR FROM "SQLUSER"."BTT120" where (12 * int( "SQLUSER"."BTT120".yyyy)) + int("SQLUSER"."BTT120".mm) between (12*year(CURRENT DATE)) + month(CURRENT DATE)-3 and (12*year(CURRENT DATE)) + month(CURRENT DATE)
everything looks good but iam not getting output, there is data for the date range
Hello,Please see the original data below. I would like to extract the accountwith the earliest opening date only, and leave all the accounts openedafter that out. Could anybody help me with the query? Thanks a lot!Original Dataperson_idaccountopen_date1000111111115/15/20031000122222226/20/20041000133333332/16/2005Ideal Outputperson_idaccountopen_date1000111111115/15/2003
I have a table, three records of which look like this:
ID PersonID FirstName LastName PostCode 1 999 Barry White BW13 8GS 2 999 <null> <null> BW13 9GS 3 999 <null> Whites <null>
Both these records refer to the same "person". The records with ID of 2 and 3 represent updates to the record with an ID of 1. The problem is, only the updated data (along with the personID) is represented in records 2 and 3. I need to write query that will return a single record that looks like this:
PersonID FirstName LastName PostCode 999 Barry Whites BW13 9GS
in other words, the most recent information we have for that person.
Does anyone have any ideas? I'd be very grateful as this is proving to be a real pain in the butt!
I am having trouble coming up with a query to extract the min and max time for each Task.. I am using Teradata SQL. The data below shows two tasks which both have the same File and Task ID. Each task starts with a Submitted Status and ends with a Rejected Status. What I need is to get the min and max time for each one of those tasks.. Unfortuneately my data below consists of values that don't have a unique identifier that I can use to split the tasks.
Hi , is it possible extract file name from script executed in query analyser ?
Example steps
1. open QA 2. open c:mySuperScript.sql code in c:mySuperScript.sql use master select @filename as [file executed] select count(*) as xfrom syslogins 3. run
-- result file executed ------------ c:mySuperScript.sql
x -- 112
Why we need it. Each database update created using sql compare tool from red-gate Script saved in Vss When script executed in production using QA we save output as proof of deployment currently we add to each script
select 'script= put you file name'+ char(13)+ 'server = ' + cast(@@servername as char(20))+ char(13)+ 'dbname= ' + cast(DB_NAME()as char(20)) + char(13) + 'execution date= '+ cast(getdate()as char(24))+ char(13)+ 'executed by = '+ system_user
We want wrap everything in procedure that will extract executed file name and other values
this procedure should be first line of code in each script
We extract 10k tables every night and I have a table that keeps track of ETL tables that fail or succeed. I would like to know if a table fails during the night and nobody kicks off another job to fix it during the day.
Table_Name = varchar(20) Time_Start = DateTime Status varchar(7) = Success or Error Duration = Number Time_End = DateTime
Select Table_Name into #MyTempTable From ETL.STATS_Table Where Status = 'Error' AND Cast(Time_Start as Date) = GetDate()
How do I take the table names from #MyTempTable and find out if they where successful for the same date? Duration time and Time_End fields aren't needed.
I have a parameter value as shown below and this is dynamic and can grow
Example : 101-NY, 102-CA, 165-GA 116-NY, 258-NJ, 254-PA, 245-DC, 298-AL How do I get the values in the below format NY,CA,GA --- each state to be followed with comma and the next state NY,NJ,PA,DC,AL --- each state to be followed with comma and the next state
correct query that will fetch only state names and not the numbers.
I recently started working with a database that uses several views, none of which are indexed. I've compared the execution plans of querying against the view versus querying against the tables and as best I can tell from my limited knowledge the two seem to perform equally. It seems to me that having the view is just one more thing I need to keep track of.
I've done some google searches but haven't found anything that really tells me which performs better, querying the view or the tables directly. Generally speaking which is better?
hi,I have two tables, t1 t2col1 col1 col2 1 1 0 2 1 0 3 1 1 4 2 0 5 2 0 6 3 1 7 3 1 8 4 1 9 4 1 10 4 1 11 4 1t2.col1 is the key from t1.col1I want to retrieve all t1.col1 records which equal to t2.col1 and tb2.col2 has ONLY "1"the result should be: 3, 4I try:select tb1.col1 from t1 as tb1 where tb1.col1 in (select col1 from t2 where col1=tb1.col1 and col2=1 and ...???.)any help?
Hello all, If I write this query :SELECT [Client].[CLI_NAME], [Client].[CLI_PRENOM] FROM Client, Commandes the return is good.
But If I the query is :SELECT [Client].[CLI_NOM] & " " & [Commandes].[CMD_DATE_ORDER] As Ordered FROM Client, Commandes I have an error "Invalid column name.
The CLI_NOM field is on Client Table and CMD_DATE_ORDER is on the other Order table. How to make a good query with an aliased column without error ?
I have two tables: 1 with data, the other 3 columns from the first table with rows filled with data that needs to be ommitted when a query is made on the first table.
For example, say I have a column named "meat" and there are rows in it with "beef" as the data. In the other table there is also a column with meat and beef as the data. I would need to query both tables to first get the data to be ommitted and then use that result to query the first table. I hope I'm making sense, any help would be appreciated
I need to be able pull certain data from our database. I need to find all stockitems (itemid column) that are a T item (binname column) and the memo to be created before the 01/02/2007 (timeanddatecreated column)
To get the data I need - I need to query three tables.
Stockitem - This has the column "itemid" Stockitemmemo - This has the column "itemid" and "timeanddatecreated" Binitem - This has the column "itemid" and "binname"
My results must be based on the following criteria......
All the itemid's have a 'T' in binitem.binname and the memo must have been created before 01/02/2007.
I do have two questions based on the above....
1. Does it make sense what I need? 2. Is it possible
I want to get the list of tables which an SQL query is using. It could be through some query or parser written in jruby or R or any other language,any thrd party tool(freeware)or any scripts.
Hi, I know it is simple query but I am confused. I need to make a query from 3 tables in the same database. There are no relations between these tables. Here is result I need. KE LE JE 15 775.5 398 18 192.23 399
I'm doing a query from two tables. the table "pr" and the table "prre" with the following data: pr.no, pr.nome, pr.data, pr.recibo, pr.ettsuj, pr.ettdesc, prre.cr pr.eliquido, prre.rqtt , prre.ervu, prre.ere
I'm doing the following query:
select cast(pr.no as varchar) as 'Numero',pr.nome as 'Nome',pr.data as 'Data',cast(pr.recibo as varchar) as 'Recibo', cast(prre.rqtt as integer) as 'Qt Dias', prre.ervu as 'valor unitario',prre.ere as 'Total Subsidio', pr.ettsuj as 'Total iliquido', pr.ettdesc as 'Total Descontos', pr.eliquido as 'Total Liquido' from pr inner join prre on prre.prstamp=pr.prstamp
for this query is a list of salaries, with the fields, official number, expiration, name, date, receipt number, net total, the total gross, days subsidy amount, subsisidio.
from bB_posts as t1 join bB_comments as t2 on t2.postid = t1.postid (or t1.commentcount = 0)
This query prints out all the posts which have 1 or more complementary comments. But i would aslo like to print the posts which have 0 comments (seen in brackets).
i have 2 tables in a database. Table A contains: State City Employee_firstEmployee_LastAge KarnatakaBangaloreDavid Na 23 KarnatakaBangalorePrakash Na 25 KarnatakaMysore Naina Na 26 KarnatakaMysore David Na 35 MaharashtraMumbai Parneet Na 24 MaharashtraMumbai Vikas Na 33 MaharashtraPune Amit Na 25 MaharashtraPune Amit Na 19
[OpporID] [numeric](18, 0) IDENTITY (1000, 1) NOT NULL , [OpportunityID] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [OpportunityTypeID] [numeric](10, 0) NOT NULL , [SLABased] [int] NOT NULL , [LoginID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [DateCreated] [datetime] NOT NULL , [AccountID] [int] NOT NULL , [GeographyID] [int] NOT NULL , [VerticalID] [int] NOT NULL , [BDMID] [int] NOT NULL , [Probability] [int] NOT NULL , [PASStatus] [int] NULL , [InsertedDate] [datetime] NULL , [InsertedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [UpdatedDate] [datetime] NULL , [UpdatedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [UpdatedFlag] [int] NULL
and SKILL [SkillNo] [numeric](18, 0) IDENTITY (1, 1) NOT NULL , [OpportunityID] [numeric](18, 0) NOT NULL , [OrderId] [numeric](18, 0) NOT NULL , [PracticeID] [int] NULL , [SkillID] [int] NOT NULL , [NoOfPeople] [int] NOT NULL , [Clientinterview] [int] NOT NULL , [Location] [int] NOT NULL , [JDAttached] [int] NOT NULL , [JDFilePath] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Status] [int] NULL , [Experience] [int] NULL , [InsertedDate] [datetime] NULL , [InsertedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [UpdatedDate] [datetime] NULL , [UpdatedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [UpdatedFlag] [int] NULL , [GeoLocation] [int] NULL )
i want to make a stored procedure for custom search on these two tables with the following fields given to the user as an option to make his choice.. from opportunity table - OpportunityTypeID,SLABased,AccountID , GeographyID, VerticalID, BDMID, Probability
and from skill table SkillID, Location, GeoLocation
Hi, I have a problem which I thought it has a simple solution but now I'm not even sure it is possible.
I have 3 tables Clients <-oo ClientContacts oo-> Contacts (the <-oo means one to may relation between the tables)
A Client may have related none, one or many Contact records. The table ClientContacts is the link that stores that information. The field ClientContacts.Category represents the type of the contact and it will be used in queries. It may be owner, accountant, employee, etc.
The result should return values for Contacts.FirstName, Contacts.LastName, Contacts.[E-mailAddress] if the Client has attached Contact records filtered by category, and '','','' or <NULL>,<NULL>,<NULL> if the Client does not have any Contact records.
I tryed an INNER JOIN but it will return juts the records having contact information.
