Finding Two Columns In All Tables

Oct 29, 2013

I have several tables that have the POLICY_NUMBER and POLICY_DATE_TIME in them.. All the tables with these two columns should have a POLICY_ NUMBER and a corresponding POLICY_DATE_TIME.I would like to find all tables that have POLICY_NUMBER = 123456 but do not have the corresponding POLICY_DATE_TIME..

View 1 Replies


ADVERTISEMENT

Finding Nullable Columns In All The Tables

Nov 21, 2013

I have 4 particular columns (crt_dt,upd_dt,entity_active and user_idn) in many of the tables in my database. Now i have to find all the tables having four columns mentioned above and cases are

1) if the column is nullable., then it should result 'Y'
2) if the column is not nullable., then it should result 'N'
3)if column is not present., then it should display '-'

View 5 Replies View Related

Finding Max Of 3 Max Date Columns?

May 13, 2014

how I would work out the max value of 3 max date values. Note the max date values may be null.

Here is my code :

SELECT TD.sro_num, SRO.description, SRO.cust_num, custad.name, SRO.sro_type, TD.whse, TD.CostCode, TD.stat_code, TD.[No of days in Status],
SRO.total_cost_lbr, SRO.total_cost_matl, SRO.total_cost_misc, ((SRO.total_cost_lbr) + (SRO.total_cost_matl) + (SRO.total_cost_misc)) AS total,
convert(varchar,MAX(mt.trans_date), 103)as max_matl_transdate, convert(varchar,MAX(lbr.trans_date), 103)as max_lbr_transdate, convert(varchar,MAX(misc.trans_date), 103) as max_misc_transdate

[code]...

View 2 Replies View Related

T-SQL (SS2K8) :: Finding Maximum Value Out Of 5 Different Columns?

Jun 2, 2011

How can we find maximum value on column level? Suppose we have table A with four columns col1,col2,col3,col4, Now my query look likes this:

Select col1, col2,col3,col4,
(col1 + col2) as addcol1,
(col2 + col3) as addcol2,
(col3 + col4) as addcol3,
Max(addcol1,addcol2,addcol3) as maxvalue
from Table A

I am getting error as max accepts one argument, I cannot use case statement as table is already bulky and it will make my query more expensive.

View 9 Replies View Related

Finding Difference Between Two Date Columns And Deleting It

Apr 14, 2008

I have two columns in my table. Both the columns contains datetime datatypes. I need to write a stored procedure which will calculate the date difference between the two columns and if it exceeds more than 5 days then that record should get deleted. How to do it?Thanx 

View 2 Replies View Related

Finding Columns That Are Intended For Foreign Keys

Apr 24, 2006

Hi Friends,

me again. I am trying to find out if all of my intended foreign keys are actually set as foreign keys programmatically.

Ive stuffed something up, please find my error

USE RIQDB1
SELECT DISTINCT 'Alter table '+ table_name +
' ADD CONSTRAINT DF_'+ table_name + '_' + column_name +
' DEFAULT ' + ''''' FOR '+ column_name FROM Information_schema.columns
WHERE ((column_name Like '%fk%') AND (FROM Information_schema.type = F))
go

thanks
Cm

View 9 Replies View Related

Finding Some Tables

Feb 8, 2008

I have many tables in one database, and some of them are empty. How can I list / find (for eg. names) only non-empty tables?

View 2 Replies View Related

Finding Certain Value From Several Tables

Dec 16, 2014

We have about 60 several tables with order id as a column in all of them . Every day I have to check if certain order id’s exist in the 60 tables. I just need to get the name of the table if it exists. Is there a way to dynamically keep changing the name of the table instead of having all 45 tables in the script?

View 2 Replies View Related

Finding Updated Tables

Nov 29, 2005

I have a front end inteface that adds data to tables in a SQL database.My question is:Is there an easy way of finding out what tables are affected by the update?  Is there a sp_ or DBCC command that would give me the update or altered tables?

View 2 Replies View Related

Finding Tables Having A Particular Column

May 19, 2008

Hi All,
How to find all the tables of a database containing a column . for example,how list all the tables of employee database having employeeid ?

Cheers,
Mathi
India.

View 6 Replies View Related

Finding Affected Tables

Jul 20, 2005

I am attempting to document a sql server 2000 based accounting system.Is there any way to see what tables a stored procedure affectswithout diving into the code?Regards,Ty

View 1 Replies View Related

Help Finding And Updating Dupes In 2 Tables

Oct 13, 2005

Being fairly new to SQL and SQL scripting, I am at a loss on how to proceed on my issue.

I have a MSDE database with 2 tables that need to modified. I am changing to a standard 12 digit code in my PATIENTS table for the field sChartCode nvarchar). That code will be in the form of 110012345678. 1100 will preceed the actual 8 digit chartcode

In the PATIENTS table, the same person may be duplicated many times using vaiations such as 123456, 12345678, 012345678, 12345678 SMITH, 012345678 SMITH. For each of these records, they are linked to the RECORDS db using the field lPatientId (int).

I have already manually updated about 20K records in the RECORDS db which
takes way to many hours of time. New records will be imported at about 10K a week or so and will be over 100K soon. By the way, the SQL server is on the way.

What I am looking for is an easier way to find the records that have not been
converted in the PATIENTS db and see if they match one that has already been converted. If it has, it would need to update all records in the RECORDS db with the correct updated lPatientId and then delete the duplicate record(s) from the PATENTS db. If not, it would only need to add '1100...' to the lPatientId field.

Any help or guidance that anybody can give will be most appreciated.

Dale

View 2 Replies View Related

Finding The Top 20 Most Used/Active Tables In A Sql 2000 DB

Jul 11, 2007

Hi,

I have a legacy sql 2000 database with numerous stored procedure and tables.

I need to find out the top 20 most used/accessed tables in the database

Any one know how i could do this type of trace with profiler?

View 3 Replies View Related

Finding Out All The User Tables Being Used By An Application

Jul 23, 2005

Here's the situation, developers inherit a web app from someone,backend SQL db has about 120 user tables and the db is also being usedby other apps. Developers don't have a list of user tables being usedby this app, now, I need to create a new db based on this one, whichwould be used by this app only. So, I intend to find all the usertables being used by this app, then copy its schema and possibly dataas well to a new db. FYI, current ERD is not this app.One option to find out all the user tables being used by this app is todo recursive search for FROM and JOIN against the full spectrum of theuncompiled source code, then, weed through such an extracted list whenin doubt about a particular table, one caveat is a portion of code withsql stmt could have already been commented (no longer being used thoughstill in the code).Better option?TIA.

View 3 Replies View Related

Finding If A Record Exists In Either Of Two Tables

Mar 17, 2008



Hello,

I have two tables with the same field layout, and they both have the same field as the Primary Key. They just contain different data. I would like to know if a record exists in one, or both, tables.

The tables are InvTemp1 and SalesTemp1. The key for both is stock_number.

Here is the command so far:


SELECT COUNT(*)

FROM InvTemp1 INNER JOIN SalesTemp1 ON InvTemp1.Stock_number = SalesTemp1.Stock_number

WHERE (InvTemp1.Stock_number = '101053')

Thank you for any ideas,
Tom

View 3 Replies View Related

SQL Server - Finding The Different Records In Two Identical Tables

Jul 30, 2007

Does anyone have a good query that would return records from two tables that are found in one, but not it the other table?  In my situation I have 2 tables that are duplicate tables and I need to find an additional 3000 records that were added to one of the tables.  I also have a composite key so the query would have col1, col2 and col3 as the composite key.  So far I have tried concatenating the 3 columns and giving the result an alias and then trying to show the ones that were not in both tables, but have been struggling.  Thanks.. 

View 4 Replies View Related

SQL Server 2008 :: Finding Column Within All Tables In DB

May 1, 2015

I am trying to find a way where I can search for a column that is associated in all tables of the database. I have created a query but is not executing correctly.

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t8
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%Status%'
ORDER BY schema_name, table_name;

View 3 Replies View Related

SQL Server 2008 :: Finding Tables With No Createdate?

May 4, 2015

In our Production db we have all most all tables have the column created or createdate.

I need to find out all tables without the created or createdate column

SELECT t8.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t8
INNER JOIN sys.columns c
ON t8.OBJECT_ID = c.OBJECT_ID

[code]....

View 2 Replies View Related

SQL Server 2008 :: Finding All Relations Between Tables?

Sep 1, 2015

Below I have a query which list the relations (constraints) between tables.

I want to list all the relations which are visible in the Database Diagrams.

The list is not complete. How do I get a complete list ?

--
-- Query to show the relations (constraints) between tables.
-- This does not show the complete list
--
SELECT A.constraint_name,
B.table_name AS Child,
C.table_name AS Parent,

[Code] ...

View 4 Replies View Related

Correct Way Of Finding A Tables Primary Keys??

Aug 7, 2007

Hope this is in the right thread, sorry if not!

I have run into a problem, i need to find out that column(s) in a table that makes the primary key.
I thought that this code did the trick.
***
DECLARE @c varchar(4000), @t varchar(128)
SET @c = ''
SET @t='contact_pmc_contact_relations'
Select @c = @c + c.name + ',' FROM syscolumns c INNER JOIN sysobjects o ON o.id = c.id inner join sysindexkeys k on o.id = k.id WHERE o.name = @t and k.colid = c.colid ORDER BY c.colid
SELECT Substring(@c, 1, Datalength(@c) - 1)
***

This works in most of my cases. But i have encounterd tabels where this code doesn't work.
Here is a dump from one of the tabels where it doesn't work.
SELECT *
FROM sysindexkeys
WHERE (id = 933578364) <--id of the table
***
id indid colid keyno
933578364 1 1 1
933578364 1 2 2
933578364 2 1 1
933578364 3 2 1
933578364 4 3 1
933578364 5 4 1
933578364 6 5 1
933578364 7 6 1
933578364 8 7 1

Not sure if that dump made any sense, but i hope it did.
If i look at the table in SQL Enterprise manager there is no relations, no indexes only my primarykey made up with 2 columns (column id 1 and 2).

So, anyone know how i could solve this problem?


Regards
/Anders

View 8 Replies View Related

SQL Server 2008 :: Finding Only Fields That Don't Match In Two Different Tables

Feb 12, 2015

I have two table People and Employee, both have firstname and lastname as fields

I want to display only the names that don't match on firstname and lastname

View 3 Replies View Related

T-SQL (SS2K8) :: Finding Last Records Inserted Into All Tables In A Database

Jul 27, 2015

I have a CRM database that has a lot of tables and would like to be able to extract the last 'x' records in descending order from each table based on a common a field 'modifiedon' that is in every table and is auto populated by the system.

View 4 Replies View Related

Finding Mismatched Rows Between Identical Tables Based On 2 Or More Cols

Jun 8, 2007

CREATE TABLE [RS_A] ([ColA] [varchar] (10)[ColB] [int] NULL)CREATE TABLE [RS_B] ([ColA] [varchar] (10)[ColB] [int] NULL)INSERT INTO RS_AVALUES ('hemingway' , 1)INSERT INTO RS_AVALUES ('vidal' , 2)INSERT INTO RS_AVALUES ('dickens' , 3)INSERT INTO RS_AVALUES ('rushdie' , 4)INSERT INTO RS_BVALUES ('hemingway' , 1)INSERT INTO RS_BVALUES ('vidal' , 2)I need to find all the rows in A which do not exist in Bby matching on both ColA and ColBso the output should bedickens 3rushdie 4So if i write a query like this , I dont get the right result setSELECT A.ColA, A.ColBFROMRS_A AINNERJOIN RS_B BONA.ColA <B.ColAORB.ColB <B.ColBBut if i do the following, i do get the right result, but followingseems convoluted.SELECT A.ColA, A.ColBFROMRS_A AWHERE ColA + CAST(ColB AS VARCHAR)NOT IN (SELECT ColA+CAST(ColB AS VARCHAR) FROMRS_B B)

View 6 Replies View Related

SQL Search :: Query For Finding Two Columns Data In Third Column And Getting Data From It?

Jul 6, 2015

getting result as given below.

Input:

id Name Data

101 AA PQ102BBRAJAKIRANBUBLU

102 BB RS101AAEROJUCHALABAGUNDI

103 CC TU104GGANDICANKILLANYONE

OUTPUT:

id Name Data

101 AA 101AAEROJUCHALABAGUNDI

102 BB PQ102BBRAJAKIRANBUBLU

103 CC

View 4 Replies View Related

SQL 2012 :: Calculated Columns Conditional On Calculated Columns Multiple Tables

Apr 20, 2014

I have 4 tables involved here. The priority table is TABLE1:

NAMEID TRANDATE TRANAMT RMPROPID TOTBAL
000001235 04/14/2014 335 A0A00 605
000001234 04/14/2014 243 A0A01 243
000001236 04/14/2014 425 A0A02 500

TRANAMT being the amount paid & TOTBAL being the balance due per the NAMEID & RMPROPID specified.The other table includes a breakdown of the total balance, in a manner of speaking, by charge code (thru a SUM(OPENAMT) query of DISTINCT CHGCODE

TABLE2
NAMEID TRANDATE TRANAMT RMPROPID CHGCODE OPENAMT
000001234 04/01/2014 400 A0A01 ARC 0
000001234 04/05/2014 -142 A0A01 ARC 228
000001234 04/10/2014 15 A0A01 ALT 15

[code]...

Also with a remaining balance (per CHGCODE) column. Any alternative solution that would effectively split the TABLE1.TRANAMT up into the respective TABLE2.CHGCODE balances? Either way, I can't figure out how to word the queries.

View 0 Replies View Related

To Many Columns Or To Many Tables?

Feb 6, 2007

my supervisor is trying to help move our existing database in IBM U2 into SQL Sever  he has a file/table that as 12001 columns, 1 column is the key300 columns are different field errors that can be checked true or falseand for each of those 300 columns there are three extra columns where extra information can be stored   is there a size limitation when it comes to columns in a SQL table?what is an efficient way of doing this?create one giant table with 12000 columns?create 300 tables, each table associated with a error association? which will then need to be joined?any suggestions? comments? tips?    

View 3 Replies View Related

How To Add Tables Name With 3 Columns

Aug 6, 2015

When I run below query I get 3 columns, but when I try to add table name ind.object_name (object_id) it's giving me an error "Ambiguous column name 'object_id".How do I add tables name with 3 columns?

SELECT ind.name, ic.index_id , ind.type_desc from sys.indexes ind
INNER JOIN
sys.index_columns ic ON ind.object_id = ic.object_id and ind.index_id = ic.index_id
INNER JOIN
sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id
INNER JOIN
sys.tables t ON ind.object_id = t.object_id

View 4 Replies View Related

Name Of Tables And Columns

Aug 2, 2007

Hi,
i just migrated an database from oracle to sql server 2005 with the migration tool from microsoft (v3). the migration tool works only with uppercase table and column names, but i need them in lower case. is there a way to modify the names of tables and columns with t-sql to lower case?
Thx
Frank

View 7 Replies View Related

How To Index Through A Tables Columns

May 8, 2005

I am trying to index through the columns of MyTable so I can do the same work on all columns. I know how to get the column names from MyTable but when I use @MyColName in the SELECT statement to get MyTable Column 0 Row values I get a table with the column name in each row cell. I can't get the syntax correct to return the value in each cell for that column.
This is a extremely simplified example !!!!!!DECLARE @MyColName nvarchar(30)
--Get the MyTable Column 0 NameSELECT @MyColName = Col_Name(Object_ID('MyTable'), 0)
--Display the MyTable Column 0 Row valuesSELECT @MyColName FROM MyTable --This is the syntax I can not get correct
 
Can anyone help ?
Thanks

View 2 Replies View Related

Cant Add New Columns To Data Tables

Sep 19, 2005

Hi All,I am having  a problem for which i dont find any reasons ..I hope to get a solution from here.I have 2 tables ..1 with around 150 columns and the other with around 80 columns.I have view based on these tables.The problem i m facing from yesterday is that i cant add /delete/change on these tables.If i make a change on these tables and hit the save button the sql server enterprise just hangs..i also tried to add the columns through the query but no results.I cant even drop this view.Please any help on this??Thanks

View 1 Replies View Related

Loop Through All Tables And Columns

Oct 26, 2005

Is there a way to change the collation against all the tables and columns within a database?

View 2 Replies View Related

Search Tables And Get Columns

Nov 10, 2005

I drew short straw on some reports work and am spending an eternity searching through catalogs looking for tables and columns. I'm a little new to SQL. Is there a way to search a catalog for table column names?

View 2 Replies View Related

Listing All Tables And Their Columns?

Jun 14, 2006

Hey all. I apologize, but I'm a developer, not a DBA. I need to run a query that will list each table in a DB as well as the columns i nthose tables.

I know that you can use: EXEC sp_help 'table_name' to get a description, but I'm not sure how to set up a cursor to substitute the table names, or where to get the tables names.

Any help would be greatly appreciated. Thanks!

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved