Checking Column Name From Multi Database's Table
Aug 18, 2006
hi
i have over 200 tables with all same column and data type locate all over the server(20)different database. so i have table call Tname to stored all the link like (databasename.dbo.tablename) so my job will call the Tname table to use cursor to insert records. but the problem is there's one or more table's column name K datatype has been modify by someone else. so is it cause the job fail.
if there a way or SQL statment that i can use Table Tname to see which one is missing column name K? and is there a way i can print out all the datatype and len for all the table column k? please help thanks
View 2 Replies
ADVERTISEMENT
Sep 18, 2007
Hi,
I have a table as follows
Table
{
Category1,
Category2,
Category3
}
I wanted to write a query to check whether the column with the name 'Category3' exists in the table.
Can anyone please let me know how to do this
Best Regards and Thanks
~Mohan Babu
View 4 Replies
View Related
Nov 12, 2012
I am trying to exclude records from a table where the ID column is the same but the Mail code Column is multi-valued.For Example: (the table looks like....)
ID Mail_code
111111 XNT
111111 N11
111111 XNC
222222 XNC
222222 XNL
333333 XNC
So, if there is any ID that has a value of XNC, I want to exclude the ID all together from my output regardless of the other values.
View 3 Replies
View Related
Feb 22, 2005
Hi all,
While cleaning up some code, I ran across the following statement in a stored proc - the purpose of which is to determine if a table exists in the local database: SELECT * FROM dbo.sysobjects where id = object_id(N'[dbo].[XML_PRINTDATE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1of course I removed it from the IF just for testing purposes, but my quandry is this...
Why chose that select (converting table name to object ID) rather than just doing THIS:SELECT * FROM dbo.sysobjects where name = N'XML_PRINTDATE' and OBJECTPROPERTY(id, N'IsUserTable') = 1
I first thought it was to gain access to the "id" column value (and that may yet be the purpose of it), but the second code seems to work just peachy (I assume because the id column is present in the sysobjects table itself).
A follow-on question is this:
When I try to do the same check from another server (i.e.SELECT * FROM APRECEIVE1.DailyProd.dbo.sysobjects where name = N'XML_PRINTDATE' and OBJECTPROPERTY(id, N'IsUserTable') = 1) it of course fails because OBJECTPROPERTY only looks for the id on the local database.
So, do I CARE if it is a user table? (I am reasonably sure it is, of course) and if so, is there a way to check on the remote server for the object type?
Bottom line is I Think I can just simplify things and check for the object name on the remote server, but just don't want to take away any "warm fuzzy feeling" generated by the original stored proc, if such a warm fuzzy is of any benefit (though don't get me started on the relativity of warm fuzzies, I wrote my Thesis on that ;) )
View 9 Replies
View Related
Jun 21, 2006
Hi all,A (possibly dumb) question, but I've had no luck finding a definitiveanswer to it. Suppose I have two tables, Employees and Employers, whichboth have a column named "Id":Employees-Id-FirstName-LastName-SSNetc.Employers-Id-Name-Addressetc.and now I perform the following join:SELECT Employees.*, Employers.*FROM Employees LEFT JOIN Employers ON (Employees.Id=Employers.Id)The result-set will contain two "Id" columns, so SQL Server willdisambiguate them; one column will still be called "Id", while theother will be called "Id1." My question is, how are you supposed toknow which "Id" column belongs to which table? My intuition tells me,and limited testing seems to indicate, that it depends on the order inwhich the table names show up in the query, so that in the aboveexample, "Id" would refer to Employees.Id, while "Id1" would refer toEmployers.Id. Is this order guaranteed?Also, why does SQL Server use such a IMO brain-damaged technique tohandle column name conflicts? In MS Access, it's much morestraightforward; after executing the above query, you can use"Employees.Id" and "Employers.Id" (and more generally,"TableNameOrTableAlias.ColumnName") to refer to the specific "Id"column you want, instead of "Id" and "Id1" -- the"just-tack-on-a-number" strategy is slightly annoying when dealing withcomplex queries.--Mike S
View 6 Replies
View Related
Sep 23, 2014
Disaster Recovery Options based on the following criteria.
--Currently running SQL 2012 standard edition
--We have 18000 databases (same schema across databases)- majority of databases are less than 2gb-- across 64 instances approximately
--Recovery needs to happen within 1 hour (Not sure that this is realistic
-- We are building a new data center and building dr from the ground up.
What I have looked into is:
1. Transactional Replication: Too Much Data Not viable
2. AlwaysOn Availability Groups (Need enterprise) Again too many databases and would have to upgrade all instances
3. Log Shipping is a viable option and the only one I can come up with that would work right now. Might be a management nightmare but with this many databases probably all options with be a nightmare.
View 1 Replies
View Related
Nov 29, 2007
Hi, this is my first week really trying to learn ASP.net (i'm currently working with 2.0).I have a page, that I am trying to set up that will allow the user to input data, now i'm sure that passing data from 1 webpage to 1 table in a SQL database is pretty streightforward, but how do you plan it out when you know that your web page is going to insert multiplte inserts into multiple tables, with the main table PK referencing all?Any ideas? Even just a starting point so I can get going with this? If I can help anymore, please let me know.
View 4 Replies
View Related
Mar 29, 2007
Hi
I am having a table called as status ,in that table one field is there i.e. currentstatus.
the rows which are having currentstatus as "ticket closed",i want to move those rows into other table called repository which is having same table structure as status table.
I can do programatically.
but is there any way for every 3 months system has to check and do this action means moving to repository table automatically?
Please help me.
Thanks.
View 1 Replies
View Related
Aug 12, 2014
Will this script check the existing values in a column or am i missing something?
if EXISTS(select 1
FROM inserted
INNER JOIN TESTTABLE M ON
inserted.ID= M.ID
WHERE inserted.RECORD_Id >= inserted.RECORD_Id)
Everything seems hundreds, i now just need it to check the the existing values in the table
View 4 Replies
View Related
Mar 1, 2006
Hi,I have a table which I want to alter to add the data from anothertable.Now both these tables have a few columns which are same. Now,I want the system to be such that only the unique columns can beadded to the first table.How can I accomplish this in SQL?
View 1 Replies
View Related
Jan 9, 2008
I have two tables, one a data table, the other a product table. I want to perform a join on the two tables with values distributed into columns based on the value in the month field.
data_table
product_code month value
350 1 10
350 2 20
350 3 30
product_table
product_code profit_center
350 4520
result_view
product_code profit_center mon1 mon2 mon3
350 4520 10 20 30
My current query gives the following result
result_view
product_code profit_center mon1 mon2 mon3
350 4520 10 0 0
350 4520 0 20 0
350 4520 0 0 30
Any direction toward a solution would be appreciated. Am using SS2005.
View 5 Replies
View Related
Jan 5, 2012
Is there a command I can use to tell me all the tables in my schema that have a column name "Id"?
View 5 Replies
View Related
Aug 14, 2007
Hi,Would like to know the performance differenece between Multi-columnIndex vs Single Column Indexes. Let's say I have a table with col1,col2, col3 along with a primary key column and non-indexed columns.In queries, I will use col1, col2, and col3 together and some timesjust one or two of these three columns. My questions is, should Icreate one index contains col1, col2, and col3, or create 3 seperatedcolumns. I.e. each column has its own index. Any performancedifference?Thanks a lot.
View 1 Replies
View Related
Mar 18, 2007
In my report i would have 2 groups.
The first group should cause a real page break, the secound group should cause a column break.
Any idea on how to realize this, i've been playing with quite some settings but .....
So, any help ...
View 1 Replies
View Related
Mar 27, 2007
I am new to Reporting Services and hope that what I am looking to do is within capabilities :-)
I have many identical schema databases residing on a number of data servers. These support individual clients accessing them via a web interface. What I need to be able to do is run reports across all of the databases. So the layout is:
Dataserver A
Database A1
Database A2
Database A3
Dataserver B
Database B1
Database B2
Dataserver C
Database C1
Database C2
Database C3
I would like to run a report that pulls table data from A1, A2, A3, B1, B2, C1, C2, C3
Now the actual number of servers is 7 and the number of databases is close to 1000. All servers are running SQL2005.
Is this something that Reporting Services is able to handle or do I need to look at some other solution?
Thanks,
Michael
View 5 Replies
View Related
Apr 13, 2012
Ihave 2 tables...they are basically the same except for the column name in one of them because they deal with 2 different names, though..the data i want is in columns that have the same name.pretty much what i want to do...is .they also need to be distinct so i dont count duplicates...i can get them as separate tables...but i cant get them together..I need them in 1 column because of how it is sent to the C3 code page and how it reads it...the structure has already been previously set..and there are about 5 other statments that are being executed in this one stored procedure like this (also i wasnt the one who set this up).
image 1 is what is currently set up
imgur: the simple image sharer
top part is what is stored in tables..bottom is more of the result
it basically runs this code to get the bottom
DECLARE @id INT;
DECLARE @invest nvarchar(50);
SET @id = '7633';
SET @invest = '';
SELECT 'a' + CONVERT(nvarchar, orderfindings.risk_rating) AS cat, COUNT(DISTINCT orderfindings.prnt_id) AS stat
FROM orderheader, orderaudits, orderfindings
WHERE orderheader.id = orderaudits.orderheader_id AND orderaudits.ID = orderfindings.prnt_id
AND orderheader.id = @id AND orderfindings.risk_rating > 0 AND orderaudits.Investor_Name LIKE '%' + @invest + '%'
GROUP BY orderfindings.risk_rating
If i want agencies instead of findings..just replace it..agencies and findings are the 2 tables..they are the pretty much identical column wise...but i want the result together..i've tried several ways..but i cant seem to get it.
image 2 - the table at the bottom is more what i'm looking for..it combines them both into 1
imgur: the simple image sharer
if an order has a finding or agency or both in it..then it gets marked as a 1 for that risk rating...if it doesnt..then 0 for that risk rating..and then sum them all up to see what i got..
I've been working with it...did this [SQL] compare2 - Pastebin.com ..got it to display 2 columns..but still not the right result...i'm getting a1 = 1...a2 = 1...so its not running through all the orders...or it needs a way to count it...i put a sum at beginning of case statement..erro because of counts...so i took counts out...
View 7 Replies
View Related
Oct 1, 2001
How do I copy a column(or colums) from a table in one database to another table in a different database
View 1 Replies
View Related
Jan 9, 2004
I need to check one table while updating another table
suppose i need to check table1's value is whether less then 0 or not
without if condition can i check it with update query?
thank you
View 5 Replies
View Related
Aug 6, 2014
how i can check for duplicate entries for example if a serial number has already been inputted and a user tries to input the same serial number.. how can i get a trigger or some sort to check for duplicates and then prompt that the number has already been entered.
View 7 Replies
View Related
Jan 7, 2008
Hai,
Can anybody help me to write a script to see whether the table have records, if records are available then delete all those records?
Thanks.
Regards
Kashif Chotu
View 5 Replies
View Related
Apr 22, 2008
I am very VERY new to using Visual Studios...
I have a SQL database with a table in it "table1" inside the table there is a field "ID"
I have made a new website project thing, and I have a textbox and a submit button
What I want to do is make a function that will check if what the user has typed into ID is valid, e.g. it exists in the database
Can anyone point me in the right direction about how I could do this?
Thanks very much
View 3 Replies
View Related
Jan 26, 2007
Which is the best way to check the size of a database from Transact SQL ? (Selecting from Sysfiles gives a totally different result compared to right-clicking a database and selecting Properties. It's the result from the latter than I want to obtain through Transact sQL.)
View 8 Replies
View Related
Sep 2, 2014
I have written some code that selects all the databases on our server then uses a while loop to execute some dynamic sql to query a table in each database. The problem is some of the databases dont have the correct permissions so the query breaks. Is it possible to check to see if the database has the correct permissions before I execute the dynamic SQL and if it doesn't move onto the next one in the list?
View 1 Replies
View Related
Sep 19, 2007
I've got two tables, one containing a list of company names(approx 10,000 records), the other containing a list of company employees (approx 30,000 records) joined by the CompanyID column.
I have a third table (approx 700 records) containing new employees to be added to the employee table. Each record in this table has the employees details plus the name of their company.
I want to write a query that will check each row in the third table to see if
a) the employee exists in the Employees table
b) the company exists in the Companies table and
c) the employee is listed under the correct company
The query should also handle any combination of the above. So if the company doesn't exist but the employee does, create the company on the companies table and update the appropriate record on the employees table with the new CompanyID etc. etc.
Oh, forgot to mention. The company names in the third table won't be exactly the same as the ones in the Company table so will need to use CharIndex.
Anybody got any ideas?
View 4 Replies
View Related
Sep 10, 2007
Hi,
I want to use something like select count(*) from table name = 0; to check whether a table is empty,
is this possible?
Thanks for any info.
Al.
View 4 Replies
View Related
Sep 23, 2007
Greetings!!
I have a MsAccess db containing a table called Employees which i am transforming to Sql server 2005. Everything is working fine. I am using Foreach File enumerator and uploading the files one by one.
However I now plan to validate the schema of MsAccess before uploading it. For eg: My employee table in msaccess is as follows :
Employees
empId int,
empName varchar(60),
empAge int
Since the files come from different vendor, while looping, i want to perform a check if the empid or empAge are not of type long or are not null. If they are of type smallint,i have no problem.
However if they are larger datatypes than the the ones kept in Sql server, then the file needs to be logged in the db with the reason and moved to the error folder. In short, if the datatypes in access tables are smaller than those in Sqlserver, allow it, otherwise reject it.
THe schema of Sqlserver table is same as of that of Employees in msaccess.
How do I do it.
Thanks ,
Lolsron
View 5 Replies
View Related
Apr 22, 2006
I would like to be able to check if a certain entry exists in a SQL table. Ideally, the output would be a boolean so I can use it in an IF statement which would tell it what to do depending on whether or not the entry exists. Thanks for anyone that helps. :)
View 6 Replies
View Related
Feb 23, 1999
Hi all,
We have been checking the database by dbcc checkdb,through a automated task
on our production server (nightly task run everday)d we always getting the message given below.
Msg 625, Level 20, state 1
Could not retrieve row from logical page 957298 via RID because the entry in the offset table (=0) for that RID (=17) is less than or equal to 0.
If anyone knows about this error message, can you kindly post the message.
Thanks a lot.
Jay.
View 1 Replies
View Related
May 20, 2002
I have 6 columns:
Column1a, Column1, Column2a, Column2, Column3a, Column3
I need a statement that will go through the whole table as follows:
For each row, if column3 is not null then return column3 as columnB and column3a as ColumnA.
If column3 is null then if column2 is not null then return column2 as columnB and column2a as columnA.
If column3 & column2 is null then return column1 as columnB and column1a as columnA.
Any ideas?
Thanks!
View 2 Replies
View Related
Apr 22, 2008
Hi there,
My 'where' clause contains the following...
startdate <= @Date AND enddate > @Date
Should I put one index on 'startDate' and another on 'endDate' or should I have one index that covers both columns?
I don't know what the difference would be.
Cheers, XF.
View 1 Replies
View Related
Jun 17, 2008
TABLE1
======================
PriceList
---------
1
2
3
1
2
3
1
2
3
Price
-----
777
888
999
777
888
999
777
888
999
(pretend these columns are side by side)
======================
I need to make a query to:
SELECT PRICE AS 'PRICE1' WHERE PRICELIST = 1
AND SELECT PRICE AS 'PRICE2' WHERE PRICELIST = 2
AND SELECT PRICE AS 'PRICE3' WHERE PRICELIST = 3
the output that i want is:
PRICE1
------
777
777
777
PRICE2
------
888
888
888
PRICE3
-----
999
999
999
(pretend these columns are also side by side)
View 6 Replies
View Related
Feb 8, 2008
Hi All,
I have the following Ex:
Table A
Col A <- Col that I need to check against
Col B
Col C <- Sum this col
So if Col A has the following values:
Col A Col C
2 10.00
4 15.00
2 25.00
4 15.00
3 10.00
3 5.00
7 4.00
9 20.00
I need to bring back the sums of 2, 4 and 3 in one resultset. How would I do this?
Thanks,
JJ
View 10 Replies
View Related
Nov 1, 2007
Hi,
I have a [TestTable] table with three rows. The pair of columns [Test1] and [Test2] are id, the [Test3] is a data column. First, I get a table variable with list of id pairs. Next, I would like to update the rows of that ids. However, I have not found the elegant way how to do it. For one column it is simple, just IN clause, which does not work (or I could not find how) for multi-columns. Does someone have a hint?
Thanks,
Martin
Note: The example bellow is dummy; on the other hand, I hope it shows the important points. Please, do not beat me on syntax errors.
Code Block
DECLARE @MyTableVar table(
Test1 int NOT NULL,
Test2 int NOT NULL
);
SELECT [Test1],[Test2] INTO @MyTableVar FROM [TestTable] WHERE [Test3] = '%dd%';
UPDATE [TestTable] SET [Test3] = [Test3] + 'ds'
WHERE ([Test1], [Test2]) IN (SELECT [Test1], [Test2] FROM @MyTableVar);
View 3 Replies
View Related
