Hello all,
How can i write a sql statement that can give me the name of the fileds in a specified table.
Eg: if i have a table name "table1" , which has the fields "field1", "field2",........ How can i get these fields names using sql query.
any help.
I apologize for the newbie sort of question, but I could not find an answer in an SQL book nor via Google.
I wish to search for a text string in ALL fields of a table. This will be used to provide a simple search box in a web application.
So far, the only method I've found to accomplish this is follows:
SELECT *
FROM Inventory
WHERE SerialNumber LIKE '%searchstring%' OR UserName LIKE '%searchstring%' OR Location LIKE '%searchstring%' ... etc
My goal is to accomplish something like the following. This, of course, does not execute properly since * can only be used following SELECT, but you can get an idea of the target behavior:
SELECT *
FROM Inventory
WHERE * LIKE '%searchstring%'
I'm using MSDE with Visual Basic .Net. Any suggestions on how to accomplish this?
Thanks for all help,
Kieran
I am trying to write a simple search page that will search all the fields in a database to find all records that match a user input string. The string could happen anywhere in any of the fields. I have a dataset and can write a query but am unsure what the format is for this simple task. I figured it would look like this:
SELECT Table.*
FROM Table
WHERE * = @USERINPUT
But thats not working. Can someone help.? Thanks..
Hi all,
I'm using SQL Server 2005 along side Visual Studio 2005, using VB to create a web application at work.
Now I'd like to bunch together some child fields in a gridview when displayed. Have a look at the attached pic to show what I would like.
Is this possible via a query or would I need to set something up on the gridview to do this?
Cheers,
Paul.
I have tableX with columns colA, colB, colC, colD and there are 2256 rows in the tableX.
I would like to find out the percentages of colA, colB, colC, colD that hold data (where it is not an empty string or NULL value).
So out of 2256 rows in the table, the user has stored data in colA 1987 times, colB 2250 times, colC 2256 times and colD 17 times.
So the report would say:
colA: 88.07%
colB: 99.73%
colC: 100%
colD: 0.01%
We have an application that has a bunch of fields that we believe are not being used and would like to remove them, but we need to prove this by looking at the data.
I know I could run a query, one at a time and change the column name, but this would take a long time as there are a lot of columns in this table. I am hoping there is some way to do this in one query.
I have the following insert query which works great. The purpose of this query was to flatten out the Diagnosis codes (ex: SecDx1, SecDx2, etc.) [DX_Code field] in a table.
Code Snippet
INSERT INTO reports.Cardiology_Age55_Gender_ACUTEMI_ICD9
SELECT
Episode_Key,
SecDX1 = [1],
SecDX2 = [2],
SecDX3 = [3],
SecDX4 = [4],
SecDX5 = [5],
SecDX6 = [6],
SecDX7 = [7],
SecDX8 = [8],
SecDX9 = [9],
SecDX10 = [10],
SecDX11 = [11],
SecDX12 = [12],
SecDX13 = [13],
SecDX14 = [14],
SecDX15 = [15]
FROM (SELECT
Episode_Key, DX_Key,
ROW_NUMBER() OVER ( PARTITION BY Episode_Key ORDER BY DX_Key ) AS 'RowNumber', DX_Code
FROM srm.cdmab_dx_other
WHERE Episode_key is not null
) data
PIVOT
( max( DX_Code ) FOR RowNumber IN ( [1], [2], [3], [4], [5], [6],
[7], [8], [9], [10], [11], [12], [13], [14], [15] )) pvt
ORDER BY Episode_Key
The query below also works fine by itself. You may notice that the Episode_Key field appears in both the query above and below therefore providing a primary key / foreign key relationship. The srm.cdmab_dx_other table also appears in both queries. I would like to add the fields in the select statement below to the select statement above. Using the relationships in my FROM statements, can anyone help me figure this one out?
Code Snippet
SELECT
e.episode_key,
e.medrec_no,
e.account_number,
Isnull(ltrim(rtrim(p.patient_lname)) + ', ' ,'')
+
Isnull(ltrim(rtrim(p.patient_fname)) + ' ' ,'')
+
Isnull(ltrim(rtrim(p.patient_mname)) + ' ','')
+
Isnull(ltrim(rtrim(p.patient_sname)), '') AS PatientName,
CONVERT(CHAR(50), e.admission_date, 112) as Admit_Date,
CONVERT(CHAR(50), e.episode_date, 112) as Disch_Date,
e.episode_type as VisitTypeCode,
d.VisitTypeName,
convert(int, pm.PatientAge) as PatientAge,
pm.PatientAgeGroup,
pm.patientsex,
p.race
FROM srm.episodes e inner join
srm.cdmab_dx_other dxo on dxo.episode_key=e.episode_key inner join
srm.cdmab_base_info cbi on cbi.episode_key=e.episode_key inner join
srm.item_header ih on ih.item_key = e.episode_key inner join
srm.patients p on p.patient_key = ih.logical_parent_key inner join
ampfm.dct_VisitType d on d.VisitTypeCode=e.episode_type inner join
dbo.PtMstr pm on pm.AccountNumber = e.Account_Number
How can I create a Table whose one field will be 'tableid INT IDENTITY(1,1)' and other fields will be the fields from the table "ashu".
can this be possible in SQL Server without explicitly writing the"ashu" table's fields name.
I have a list of items in one table and a field (pageName) in another table that may contain one of the aforementioned items somewhere within that field. There is no fixed position within the field where the itemNo may be so I can't just use SUBSTRING(pageName,2,5) in(select itemNo from tblItem).
Logically, it's like I need to combine IN and LIKE: select pageName where pageName LIKE IN %select itemNo from tblitemNo%..LIKE can only handle one comparison string.
Hi,
I remember seeing a fancy query that checked for multiple fields in a table (I think using a select statement in the where clause but not sure), but can't remember how to do it... here is what I want to do (and maybe there is a much easier way). Thanks!
Table1
id item color
1 shoe red
2 shoe blue
3 coat green
4 coat black
Table2
item color
shoe red
coat green
I want everything in Table1 where item and color are not a match.
So my results should be:
2 shoe blue
4 coat black
I'm sorry if this is a dumb question... it's been that kind of a day!
Thanks!
I have 2 tables with 2 fields in common between them. How do I get a count of rows in table 1 but not in table 2 matching those 2 fields? Thanks.
Hello all:
Is it possible to creates fields of the table dynamically?. I have this situation in my project. This is just a small sample. I have row of length 140. I don't wan't to declare all this fields manually using the create table command.
The description of table is as, in this table all the field are of type varchar only, there are like 140 columns.
create dummy emp (
field1 VARCHAR(100), field2 varchar(200), field3 VARCHAR(100).... )
Table: Dummy
================================================== ==
field1 field2 field3..........
Empid Empname empaage1 sam 23...........
2 rai 22............
.
.
.
n raj 45.............
================================================== ==
Now I want to create another table as "EMP" , with proper data type
fields too..
create table emp (
empid int, empname varchar(100), empage int....)
The table should look like as:
Table: EMP
================================================== ==
Empid Empname empaage............
1 sam 23...............
2 rai 22................
.
.
.
n raj 45.................
================================================== ==
I want to do this dynamically.....
Some how I need to extract those field from table[dummy]; the first row acts as a column header for the table[Emp] and the subsequent row acts as a record for the table[Emp]
A small rough snippet of the code will be appreciated....
Waiting for replies........
saby
I want to update my Table but it has 30 fields so how can i modify my table i have to add some fields in it
I need that query for alter table
in it
reply me soon
All- Supposing I have table_a and table_b. Table_a has fields FIRST_A and LAST_A for people's first and last names. Similarly, table_b has FIRST_B and LAST_B for people's first and last names.
How would I create a record in table_b for each record in table_a, such that for each added record, the values of FIRST_A is copied to FIRST_B and LAST_A is copied to LAST_B. (Table_b will have other fields that are left unfilled.)
Thanks!
-Kurt
I am having trouble figuring out how to complete this any help is appreiciated.
I have Mon , Tues , Weds , Thurs , Fri as bit fields I need to write a query for a report to see if the value is true, and display M, T, W, Th, F in a column Days if they are scheduled that day
example:
Mon and Wed I got on the bus the checkboxes are set to True, I want one column in the report Days: to display M, W
how can i write a SQL query that will pull all records that are equal to NULL??
View 1 Replies View RelatedCan anyone please help me to find the diffence between two fields.
Field1 - 16:12:27:123
Field2 - 16:12:36:750
I need to find the difference between field2 and field1??
I am trying to do a select query like below...
SELECT INVENTORY_ITEM_TAB.ITEM_NO, INVENTORY_ITEM_TAB.DESCR, INVENTORY_ITEM_TAB.STATUS_FLG, INVENTORY_ITEM_TAB.PRICINGUOM,
INVENTORY_ITEM_TAB.PURCHUOM, INVENTORY_ITEM_TAB.ITEM_CATEGORY, INVENTORY_ITEM_TAB.ICINTERNALNOTES, INVENTORY_WHS.QTY_ON_HAND,
INVENTORY_WHS.QTY_ON_ORDER, INVENTORY_WHS.QTY_ALLOCATED
FROM INVENTORY_ITEM_TAB INNER JOIN
INVENTORY_WHS ON INVENTORY_ITEM_TAB.ITEM_NO = INVENTORY_WHS.ICWHSPCODE
WHERE (INVENTORY_ITEM_TAB.ITEM_CATEGORY = 'SS') AND QTY_ALLOCATED, QTY_ON_HAND, Qty_On_ORDER <> 0
ORDER BY INVENTORY_ITEM_TAB.DESCR
But I don't want to select records if all of these fields have a 0 in them - QTY_ALLOCATED and QTY_ON_HAND and QTY_ON_ORDER.
how to do this type of query. If any of those fields doesn't have a 0 then I would want to return it. I just don't want them if all three of those fields have 0.
I have 2 tables.
Clients
Contacts (multiple contacts for one client)
I'm trying to do a search that pulls a contacts where the search matches either the First Name, last Name or the clients name. If it does return clients, I'd like it to also return all the contacts associated with it.
I have two problems:
1. The query is not bringing up a lot of clients. In many cases a letter brings nothing back. Like G and H even though A and B return results.
2. If it finds a client it only returns one contact. I'd like it to return all contacts for the client.
Here's my query:
SELECT addressbook.clientid, clients.clientname, addressbook.addressid, addressbook.fname, addressbook.lname FROM clients, addressbook where clients.clientid = addressbook.addressid
AND (addressbook.fname LIKE
'".strtoupper($_GET['txtsearch'])."%' OR addressbook.lname LIKE
'".strtoupper($_GET['txtsearch'])."%' OR clients.clientname LIKE
'" . strtoupper($_GET['txtsearch'])."%')
I am trying to join two fields in a query in SQL 2000. For example.
Update myTable SET field_1 = @field_1_value , field_2 = @field_2_value, field_3 = @field_1_value + ' x ' + field_2_value
Is this even possible.
I want the user to input values for fields 1 and 2, then in the background combine the two and insert that value in field 3.
Thanks in advance,
Scotty_C
I have a table with 6 fields, and I will have all 6 parameters passed in - is there any way to write a query to give me rows based on matching ANY combination of 4 fields out of the 6 parameters passed in ? This is driving me crazy... short of doing an OR statement for all the different combinations - I have no idea how to do this....
This is what I have so far -
SELECT @nodeMachineType = nodeMachineType,
@nodePKID = NodePKID,
@biosVar = Bios,
@computerNameVar = ComputerName,
@diskVolumeVar = DiskVolume,
@guidVar = Guid,
@macAddressVar = MacAddress,
@motherboardVar = motherboard
FROM Nodes_Active
WHERE
case when Bios = @bios then 1 else 0 end +
case when ComputerName = @computerName then 1 else 0 end +
case when DiskVolume = @diskVolume then 1 else 0 end +
case when guid = @guid then 1 else 0 end +
case when macAddress = @macAddress then 1 else 0 end +
case when MotherBoard = @motherboard then 1 else 0 end >= 4
I want to let a user a fields and select the data type and size from a short list. For example: nvarchar(?), Money, Int, or Bit. These are most likely the only choices for now. There will be a web page that lets them enter a field name and select a data type.
Can someone suggest how the sproc might look? Here is what I have so far.
CREATE PROCEDURE [dbo].AddCustomField ASALTER TABLE MyDataSQL.dbo.tblCustomFields ADD @FieldName @FieldDataType NULLGO
Thank you,
I want to put fields from four tables into one table. I created a new table, but how do i get the same fields from the other tables to this table along with primary and foreign keys.
Table Fields
Tbl_Date_Dimension [Date_Dimension_Year], [Date_Dimension_Period], [Date_Dimension_Fiscal_Week]
Tbl_Report_Level Report_Level_Id
Tbl_Customer Customer_Code
[Sales Fact] [Gross Turnover] , Quantity, Consolidated_Sales_Tables_Id
The new table is called Tbl_Sales_Growth
Here's the information on the new table:
Code Block
[Date_Dimension_Year] [int] NOT NULL,
[Date_Dimension_Period] [int] NOT NULL,
[Date_Dimension_Fiscal_Week] [int] NULL,
[Report_Level_Id] [int] NOT NULL,
[Customer_code] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Consolidated_Sales_Tables_Id] [tinyint] NOT NULL,
[Quantity] [decimal](18, 0) NOT NULL,
[Gross turnover] [decimal](18, 0) NOT NULL
) ON [PRIMARY]
I'm not a T-SQL expert so I'm calling on the pros. I want to design a stored procedure that updates only fields in a given record for which a non-null parameter is specified.
Example:
Create dbo.MySproc
@CustomerID int,
@CustomerFirstName varchar(50)=NULL,
@CustomerLastName(50)=NULL
AS
UPDATE Customers
SET
CustomerFirstName= @CustomerFirstName , //update only if non-null
CustomerLastName= @CustomerLastName //update only if non-null
WHERE CustomerID = @CustomerID
RETURN
So say when calling sproc if @CustomerFirstName is specified but @CustomerLastName is not, the field CustomerFirstName would be updated but field CustomerLastName would retain original data.
Thanx in advance!
An ADODB error spawn everytime I query more than 15 fields in a specific SQL Table. My sql statement is something like this:
set rs=server.createobject("ADODB.Recordset")
rs.open "select * from mytbl",application("mycon"),1,3
And the error is something like this:
"you can't query more than the maximum field "
I have a couple columns in a table that have a data type of Text. In a query I need to concatenate these two columns into one result.
For example
Code:
SELECT (Table1.TextColumn1 + ' ' + Table1.TextColumn2) AS 'Text'
FROM Table1
However when I try this I get the error
Invalid operator for data type. Operator equals add, type equals text.
I've found out how to to the Insert into my table (col1, col2) Select (col1, col2...) from othertable where regId= @regId in my earlier question but do i have to name every column as i have about 80 in my table. Can't I use an asterisk or something....
View 4 Replies View RelatedI am trying to access the fields in a table using Visual Basic in Web Developer .NET
View 1 Replies View RelatedI need to get the field values of a table (name will be dynamic).Then assign those values to properties in a class.Let's say I will get the table name dynamically.dim tblName as string = "tablea"The 2 tables can each have 25 fields or so.I need a way to select the amt and email field values from tblName. Without saying "select job_amt, job_email from ...Is there someway to get the values based on the column name. So if the column name has amt and email, then give me those values.Maybe loop through the datatable - then for each column -- if col.ColumnName.IndexOf("Amt") = 0 or col.ColumnName.IndexOf("email") = 0 thenthen drop that column from the datatable.ex of table structure<u>tablea</u>job_idjob_amtjob_email<u>tableb</u>dance_idamt_dancedance_email
View 3 Replies View RelatedMy users login from the tbl_users table.
I would like to store all information on them in this table but that amounts to quite a lot of fields (about 50) and some of these will store a lot of HTML text in them.
Is it best to split the table up for performance reasons or will it make little difference?
Thanks,
JB
I'm new to SQL Server 7. Is there a limit to the number of fields that a table can have?
View 1 Replies View RelatedI have maked this little code but dont know the way to insertt new table and fileds in database DATABASE01...
Private Sub Command1_Click()
Dim objConn As ADODB.Connection
Set objConn = New ADODB.Connection
objConn.ConnectionString = "driver={SQL Server};server=DVD377-14D9E48CSQLEXPRESS;uid=;pwd=;database=master"
objConn.Open
objConn.Execute "CREATE DATABASE DATABASE01"
''''''''''''''''''
'"CREATE table Mytable"
''''''''''''''''''
''''''''''''''''''
'"CREATE filed1" in text format
'"CREATE filed2" in text format
'"CREATE filed3" in text format
'"CREATE filed4" in date format(DD/MM/YYYY)
''''''''''''''''''
objConn.Close
End Sub
I am new to SQL Server 2005 and I am trying to add two fields to an existing table. The table has 15 Million records in it and the save is not completing. How do I add the new fields?
View 14 Replies View RelatedI have two name columns in my table, NAME1 & NAME2 that I want to compare to see if they match. Only problem is that the order of the first, last, middle name can be either same or different between the two fields.
For example
NAME1 = JAY JOHN SMITH
NAME2 = JOHN SMITH JAY or SMITH JOHN JAY
Is there a way to somehow reorder these fields and then compare using SQL?