Is there a way to delete from multiple tables/views a column with a specific
name? For example, a database has 50 tables and 25 views all have a column
named ColumnA. Is it possible to write a simple script that will delete
every column named ColumnA from the database?
Seems to be it would be possible and I can somewhat vision it using
sysobjects but without wanting to spend too much time generating the script
(when I could in shorter time manually delete) thought I'd pose the question.
I am facing a problem in writing the stored procedure for multiple search criteria.
I am trying to write the query in the Procedure as follows
Select * from Car where Price=@Price1 or Price=@price2 or Price=@price=3 and where Manufacture=@Manufacture1 or Manufacture=@Manufacture2 or Manufacture=@Manufacture3 and where Model=@Model1 or Model=@Model2 or Model=@Model3 and where City=@City1 or City=@City2 or City=@City3
I am Not sure of the query but am trying to get the list of cars that are to be filtered based on the user input.
I concatenate multiple rows from one table in multiple columns like this:
--Create Table CREATE TABLE [Person].[Person_1]( [BusinessEntityID] [int] NOT NULL, [PersonType] [nchar](2) NOT NULL, [FirstName] [varchar](100) NOT NULL, CONSTRAINT [PK_Person_BusinessEntityID_1] PRIMARY KEY CLUSTERED
[Code] ....
This works very well, but I want to concatenate more rows with different [PersonType]-Values in different columns and I don't like the overhead, of using the same table in every subquery ([Person_1]). Is there a more elegant way to do this, without using a temp table or something else?
I am rather new to reporting on SQL Server 2005 so please be patient with me.
I need to create a report that will generate system information for a server, the issue im having is that the table I am having to gather the information from seems to only allow me to pull off data from only one row.
For example,. Each row contains a different system part (I.e. RAM) this would be represented by an identifier (1), but I to list each system part as a column in a report
The table (System Info) looks like:-
ID | System part | 1 | RAM 2 | Disk Drive 10| CPU 11| CD ROM |
Which
So basically I need it to look like this.
Name | IP | RAM | Disk Drive| ---------------------------------------------- A | 127.0.0.1 | 512MB | Floppy
So Far my SQL code looks like this for 1 item SELECT SYSTEM PART FROM System Info WHERE System.ID = 1
How would I go about displaying the other system parts as columns with info
I have created a single FULLTEXT on col2 & col3. suppose i want to search col2='engine' and col3='toyota' i write query as
SELECT
TBL.col2,TBL.col3 FROM
TBL INNER JOIN
CONTAINSTABLE(TBL,col2,'engine') TBL1 ON
TBL.col1=TBL1.[key] INNER JOIN
CONTAINSTABLE(TBL,col3,'toyota') TBL2 ON
TBL.col1=TBL2.[key]
Every thing works well if database is small. But now i have 20 million records in my database. Taking an exmaple there are 5million record with col2='engine' and only 1 record with col3='toyota', it take substantial time to find 1 record.
I was thinking this i can address this issue if i merge both columns in a Single column, but i cannot figure out what format i save it in single column that i can use query to extract correct information. for e.g.; i was thinking to concatinate both fields like col4= ABengineBA + ABBToyotaBBA and in search i use SELECT
TBL.col4 FROM
TBL INNER JOIN
CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABBToyotaBBA"') TBL1 ON
TBL.col1=TBL1.[key] Result = 1 row
But it don't work in following scenario col4= ABengineBA + ABBCorola ToyotaBBA
SELECT
TBL.col4 FROM
TBL INNER JOIN
CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABB*ToyotaBBA"') TBL1 ON
TBL.col1=TBL1.[key]
Result=0 Row Any idea how i can write second query to get result?
I want to search in fulltextindexes for multiple searchterms in multiple columns. The difficulty is: I don't want only the records with columns that contains both searchterms. I also want the records of which one column contains one of the searchterm ans another column contains one of the searchterms.
For example I search for NETWORK and PERFORMANCE in two columns. Jobdescr_________________________|Jobtext Bad NETWORK PERFORMANCE________|Slow NETWORK browsing in Windows XP Bad application PERFORMANCE_______|Because of slow NETWORK browsing, the application runs slow.
I only get the first record because JobDescr contains both searchterms I don't get the second record because none of the columns contains both searchterms
I managed to find a workaround:
SELECT T3.jobid, T3.jobdescr FROM (SELECT jobid FROM dba.job WHERE contains(jobdescr, 'network*') or CONTAINS(jobtext, 'network*') ) T1 INNER JOIN (SELECT jobid FROM dba.job WHERE contains(jobdescr, 'performance*') or CONTAINS(jobtext, 'performance*')) T2 ON T2.Jobid = T1.Jobid INNER JOIN (SELECT jobid, jobdescr FROM dba.job) T3 ON T3.Jobid = T1.Jobid OR T3.Jobid = T2.JobId It works but i guess this will result in a heavy database load when the number of searchterms and columns will increase.
I have an Parent table (Parentid, LastName, FirstName) and Kids table (Parentid, KidName, Age, Grade, Gender, KidTypeID) , each parent will have multiple kids, I need the result as below:
I previously posted a problem with result set bindings but I have not been able to resolve my problem. I guess all this comes with being new to programming in this environment! Anyway, I am trying to figure out how to process from an ADO.NET connection multiple rows with multiple columns. I have to read and manipulate each row. I was originally looking at using a foreach loop but have not been able to get it to work. One reply to my previous thought I should be using a data task to accomplish this. Could someone tell me the best way to handle this situation? As a note, I am new to programming in SSIS and basically trying to learn it as I go so please bear with me! Thanks in advance!
Hi, I want to convert multiple rows to one row and multiple columns. I saw some examples with PIVOT but i could not get them to work. Heres what i want to do:
This is the how the table is: EmpID Designation
678 CFA
679 CFA
680 CFA
685 CFP
685 CIMA
685 IMCA
I want it to display as: EmpID Designation1 Designation2 Designation3 678 CFA 679 CFA 680 CFA 685 CFP CIMA IMCA
I need to delete multiple sql logins on one of my database servers that are no longer needed. Most of these logins are not attached to any database on the server because the database has been deleted from this server. Is there any way to delete logins that are no longer needed without having to review every login to verify it is not attached to a database before deleting?
I have a table with a record that looks like the attached TXT.
I need to keep the most recently entered value where flag_out = 1 and delete those duplicate records, and this should only apply to records where there's also an flag_in value of 1.
I've tried a bunch of delete statements without avail....
We have a folder in SSIS called ETL and there are about 25 SSIS packages in it. Now we need to update it. I tried to delete the folder but you get the message that the folder is not empty. So I renamed the folder to ETL_old and my deployement works fine.
But now I want to get rid of all the old folders. Delete a folder didn't work. Selecting multiple packages doesn't work.
To delete a single one you select the package, right click and select delete, click yes. But then I have to do it 25 times. I was unable to set a short cut with the keyboard for that action.
I have 3 tables . iwant to delete rows from all the three tables at same time using single statement.All the 3 tables have a unique column which will be supplied ny the user. DELETE FROM T1,T2,T3 WHERE column1='1' how do i do it.
I know very simple SQL queries but I need help with this one. I have multiple lines in a SQL database that I need to run. Basically, I need to run this (the bracketed text and the XXX are place holders):
DELETE FROM [tableName] WHERE [columnName] = 'XXXXX'
But I need to run it around 90 times where XXXXX is a unique variable each time. I could create 90 lines similar to this one but that would take way too much time to run. Any suggestions for a noob?
Thanks,
- MT
-=<>=-=<>=-=<>=-=<>=-=<>=- Matt Torbin President Center City Philadelphia Macintosh Users Group http://www.ccpmug.org/
I want to delete a user on local but in order to do that I apparently need to delete the schema. The schema has many objects in it. Can I and what is the syntax to move multiple objects from let's say XXX to dbo so I can delete the schema XXX
I'm using ASP with a JScript variant and MSSQL Server 2000. I would like to write a script that basically erases all data except for a few things.
Is there a way to update multiple tables at once without having to write lines and lines of code? I tried UPDATE tbl1,tbl2 SET uid='asc', but to no avail. It gave me a syntax error. My thinking behind it is something like... UPDATE dbo.* SET uid='mferguson' and after that I can delete stuff like DELETE dbo.*... Any ideas?
I know the above is ASP, I've tried this thread in the ASP forum with no avail... they referred me to this forum.
I'm new to sql server. I have built simple database apps using MFC CRecordset over MS Access. I'm tying to learn about SQL server by building a simple app using MFC CRecordset in Visual Studio 2005.
The problem I have is within SQL Server Management studio experess. I have a table called OriginalDrawings that looks like this
CREATE TABLE [dbo].[OriginalDrawings](
[DrawingID] [int] IDENTITY(1,1) NOT NULL,
[OriginalFileName] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[PartNumber] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
I've loaded it with 40,000 records from my test app. If I open it in SSMSE hit Ctrl-A to select all and then press delete SSMSE appears to hang - it freezes for 10 mins+ (after which I restart my PC. I can delete 1000 records at a time OK, I can delete all quite quickly from my test app by walking through the recordset and deleting each record.
Does anyone know why it appears to hang when I try the delete all?
Thanks
Alec
SQL Server 2005 Express 9.00.3042.00
Microsoft SQL Server Management Studio Express 9.00.2047.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158) Microsoft MSXML 2.6 3.0 4.0 5.0 6.0 Microsoft Internet Explorer 6.0.2900.2180 Microsoft .NET Framework 2.0.50727.42 Operating System 5.1.2600
I'm new to relational database concepts and designs, but what i've learned so far has been helpful. I now know how to select certain records from multiple tables using joins, etc. Now I need info on how to do complete deletes. I've tried reading articles on cascading deletes, but the people writing them are so verbose that they are confusing to understand for a beginner. I hope someone could help me with this problem.
I have sql server 2005. I use visual studio 2005. In the database I've created the following tables(with their column names):
Table 1: Classes --Columns: ClassID, ClassName
Table 2: Roster--Columns: ClassID, StudentID, Student Name
What I can't seem to figure out is how can I delete a class (ClassID) from Classes and as a result of this one deletion, delete all students in the Roster table associated with that class, delete all assignments associated with that class, delete all scores associated with all assignments associated with that class in one DELETE sql statement.
What I tried to do in sql server management studio is set the ClassID in Classes as a primary key, then set foreign keys to the other three tables. However, also set AssignmentID in Table 4 as a foreign key to Table 3.
The stored procedure I created was
DELETE FROM Classes WHERE ClassID=@classid
I thought, since I established ClassID as a primary key in Classes, that by deleting it, it would also delete all other rows in the foreign tables that have the same value in their ClassID columns. But I get errors when I run the query. The error said:
The DELETE statement conflicted with the REFERENCE constraint "FK_Roster_Classes1". The conflict occurred in database "database", table "dbo.Roster", column 'ClassID'. The statement has been terminated.
What are reference constraints? What are they talking about? Plus is the query correct? If not, how would I go about solving my problem. Would I have to do joins while deleting?
I thought I was doing a cascade delete. The articles I read kept insisting that cascade deletes are deletes where if you delete a record from a parent table, then the rows in the child table will also be deleted, but I get the error.
Did I approach this right? If not, please show me how, and please, please explain it like I'm a four year old.
Further, is there something else I need to do besides assigning primary keys and foreign keys?
I am planning to use three databases on the local SQL Server. DB1 will be for App1, DB2 for App2, and DB3 will hold tables, that both, App1 and App2 need to access.
Obviously, I need in App1 and App2 at least two SqlConnection and SqlCommand objects, one that access their respective databases, and one that will access DB3. Will this result in a worse performance? (The using of two SqlConnection objects in one aspx page as example, and switching them) Should I for better performance get rid of DB3 and add the tables there to DB1 and DB2 ? (Thus multiplying data, but, if the performance will be much better, it will be fine with me)
Hi, I have a relational database with the primary table, table01. And 2 child/foreign tables, table02 and table03. All 3 tables shared the same key - [ID]. I am not sure if this is the correct approach but I am trying to create a stored procedure where if I were to delete a the row in table01 (primary), the procedure will automatically delete the common row in both table02 and table03. I have come up with something like that but it does not seems to be correct. CREATE PROCEDURE [sp_delete_test01_1] (@id [int]) AS DELETE [test01] DELETE [test02] DELETE [test03] WHERE ( [id] = @id)GO Your advise please. Many Thanks.
I have a sql server project where I have added 1 stored procedure (named StoredProcedure1) and 1 class (named MyClass). MyClass has two functions Func1() and Func2(). The stored procedure simply instantiates MyClass and calls Func1().
Func1() creates a SqlConnection object using "context connection=true" - opens the connection and then calls Func2(). Func2() also creates a SqlConnection object using "context connection=true" - and attempts to open the connection. However when attempting to the Open() call inside Func2(), the code just exits. What gives? The code is pasted below, thanks!
*******THE STORED PROCEDURE************** using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; using SqlServerProject1;
public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void StoredProcedure1() { // Put your code here MyClass obj = new MyClass(); obj.Func1(); obj = null; } };
*******THE CLASS "MYCLASS"************** using System; using System.Collections.Generic; using System.Text; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server;
public class MyClass { public MyClass() {
}
public void Func1() { using ( SqlConnection conn = new SqlConnection("context connection=true") ) { conn.Open();
Func2(); conn.Close(); } }
public void Func2() { using ( SqlConnection conn = new SqlConnection("context connection=true") ) { if ( conn.State != ConnectionState.Open ) conn.Open();
if ( conn.State == ConnectionState.Open ) conn.Close(); } } }
I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:
I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.
1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.* FROM PRODUCT A CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B
If updated my query (see below) and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.
2. My second question: How to i get around this error?
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.* FROM PRODUCT A CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B, fnSplitJson2(A.ITEM6,NULL) C
I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.
