Find Tables With Column Name
Oct 16, 2006How can I find all the tables with a specific column name?
View 3 Replies
ADVERTISEMENT
Find Common Value In Column For 30 Tables
Mar 21, 2006Hi,
I am trying to query for a common value in a column called "file_auth_nbr" in 30 different tables. I was going to try something like this (see below) but wasn't sure if this was the most efficient, fastest, or correct, way to get what I'm looking for:
Select distinct a.file_auth_nbr from table1 as a
join table2 as b
on a.file_auth_nbr = b.file_auth_nbr
join table3 as c
on a.file_auth_nbr = c.file_auth_nbr
join table4 as d
on a.file_auth_nbr = d.file_auth_nbr
join table5 as e
on a.file_auth_nbr = e.file_auth_nbr
......etc., etc.
Any suggestions would be much appreciated,
Jeff
Find Out What Tables Contain A Specific Column
Jul 28, 2006I want to write SQL that will search the tables in a database for a specific column, like this. For instance, I have a column "Unique_ID" that is in many of our tables (hundreds) but not in others and want to find out the tables it is in. It is always the first column.
I tried to find a system stored procdure to do this but couldn't and tried to create a script using the sysobjects and syscolumns tables in the Master db, but came to a roadblock because they don't seem to be related at all.
I would surely appreciate if someone else has already done this!
Thanks!
How To Find All Tables That Have An Identity Column As A Primary Key
Mar 6, 2008How do i find all the tables that have an Identity Column as a primary key in a database. Thanks.
View 8 Replies View RelatedT-SQL (SS2K8) :: Find All Rows From All Tables In A DB For Specific Column?
Mar 28, 2014I have a DB with some tables and, on certain tables, i've a column named "ID_COMPUTER".
I need to find all the rows where id_computer=<specific_value> from all the tables of my database, when column "ID_COMPUTER" exists.
Pseudo-code would be: select * from db.* where id_computer=<specific_value>
how to write this ?
Need To Find Instances Of Duplicates Within A Column; Joining 2 Tables.
Aug 22, 2007My basic situation is this - I ONLY want duplicates, so the oppositeof DISTINCT:I have two tables. Ordinarily, Table1ColumnA corresponds in a one toone ratio with Table2ColumnB through a shared variable. So if I queryTableB using the shared variable, there really should only be onrecord returned. In essence, if I run this and return TWO rows, it isvery bad:select * from TableB where SharedVariable = 1234I know how to join the tables on a single record to see if this is thecase with one record, but I need to find out how many, among possiblymillions of records this affects.Every record in Table1ColumnA (and also the shared variable) will beunique. There is another column in Table1 (I'll call itTable1ColumnC) that will be duplicated if the record in Table2 is aduplicate, so I am trying to use that to filter my results in Table1.I am looking to see how many from Table1 map to DUPLICATE instances inTable2.I need to be able to say, in effect, "how many unique records inTable1ColumnA that have a duplicate in Table1ColumnC also have aduplicate in Table2ColumnB?"Thanks if anyone can help!-- aknoch
View 1 Replies View RelatedNeed To Find An Easy Way To Split A Column In Table Without Using Cursor Or Temp Tables
Sep 5, 2007Hi ,
I have two tables within a SQL database. The 1st table has an identified column and column which lists one of more email identifers for a second table,
e.g.
ID Email
-- ----------
1 AS1 AS11
2 AS2 AS3 AS4 AS5
3 AS6 AS7
The second table has a column which has an email identifier and another column which lists one email address for that particular identifier, e.g.
ID EmailAddress
--- ------------------
AS1 abcstu@emc.com
AS2 abcstu2@emc.com
AS3 abcstu3@emc.com
AS4 abcstu4@em.com
AS5 abcstu5@emc.com
AS6 abcstu6@emc.com
AS7 abcstu7@emc.com
AS11 abcstu8@emc.com
I need to create a stored procedure or function that:
1. Selects an Email from the first table, based on a valid ID,
2. Splits the Email field of the first table (using the space separator) so that there is an array of Emails and then,
3. Selects the relevant EmailAddress value from the second table, based on a valid Email stored in the array
Is there any way that this can be done directly within SQL Server using a stored procedure/function without having to use cursors?
Many Thanks,
probetatester@yahoo.com
How To Find Differences In Column Data Types Between Tables In Two Different Databases Using TSQL
Apr 21, 2008
I have Two Database that exist on Two seperate servers. The two database contain same schema and contains tables and columns of same name. Some tables have slight differences in terms of data types or Data type lenght.
For example if a Table on ServerA has a column named - CustomerSale with Varchar (100, Null) and a table on ServerB has a column named CustomerSale with Varchar (60, Null), how can i find if other columns have similar differences in all tables with the same name and columns in the two servers.
I am using SQL Server 2005. And the Two Servers are Linked Servers
What Script can i use to accomplish this task. Thanks
Transact SQL :: Find Unmatching Columns Between Two Tables While Applying A Change Column Prefix Table
Nov 16, 2015For our ETL process, we maintain a TransformationList table that has the source view and the destination table. Data is copied from the view into the table (INSERT INTO). I am trying to find column names in the Views that are not column names in the associated Table.
In the below example, want to end up with three records:
1, View1, Column4
2, View2, Column4
2, View2, Column5
I have it almost working, except that there is a table, ChangeColPrefix table, that is used by the ETL process to change some of the view's column name prefixes. Some of the source views have column names with prefixes that do not match the destination table column names. Say view SouthBase has all the column names prefixed with SB - like SBAcct, SBName. And the Destination table of Area District has ADAcct, ADName. There would be a row in the ChangeColPrefix for SouthBase, SB, AD, 1, 2 that would be used by the ETL process to create the INSERT INTO Area District From SouthBase.
I need to use this ChangeColPreifx to find my unmatching columns between my source views and destination tables. With out that table SBAcct and SBName from SouthBase will not appear to match the columns of ADAcct and ADName, but they do match.
I want to end up with these three records as non-matching:
View1, Column4
View2, Column4
View2, Column5
View1 has Salumn2 and View2 has Salumn5, and they must be changed to Column2 and Column5 as per the ChangeColPrefix table before running the Select from INFORMATION_SCHEMA.COLUMNS EXCEPT Select from INFORMATION_SCHEMA.COLUMNS looking for unmatched columns.
/***** Set Up Test Data *****/
-- Create 2 test views
IF EXISTS(SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[View1]'))
DROP VIEW dbo.[View1]
GO
CREATE VIEW View1
AS SELECT '1' AS Column1 , '2' AS Salumn2 , '4' AS Column4;
[Code] ....
Query Server To Find All Partitioned Tables, Partition Name, Column Used, Partitioned By
Dec 17, 2007I want to find a way to get partition info for all the tables in all the databases for a server. Showing database name, table name, schema name, partition by (maybe; year, month, day, number, alpha), column used in partition, current active partition, last partition (for date partitions I want to know if the partition goes untill 2007, so I can add 2008)
all I've come up with so far is:
Code Block
SELECT distinct o.name From sys.partitions p
inner join sys.objects o on (o.object_id = p.object_id)
where o.type_desc = 'USER_TABLE'
and p.partition_number > 1
Anything That You Find In SQL Object Scripts, You Can Also Find Them In System Tables?
Jul 26, 2005I tried all the INFORMATION_SCHEMA on SQL 2000 andI see that the system tables hold pretty much everything I aminterested in: Objects names (columns, functions, stored procedures, ...)stored procedure statements in syscomments table.My questions are:If you script your whole database everything you end up havingin the text sql scripts, are those also located in the system tables?That means i could simply read those system tables to get any informationI would normally look in the sql script files?Can i quickly generate a SQL statement of all the indexes on my database?I read many places that Microsoftsays not to modify anything in those tables and not query them since theirstructure might change in future SQL versions.Is it safe to use and rely the system tables?I basically want to do at least fetching of information i want from thesystem tables rather than the SQL script files.I also want to know if it's pretty safe for me to make changes in thesetables.Can i rename an object name for example an Index name, a Stored Procedurename?Can i add a new column in the syscolumns table for a user table?Thank you
View 4 Replies View RelatedQuery To Find A Value In Column B Based On An Aggregate Function On Column A?
Jul 20, 2005Hi,Suppose I have a table containing monthly sales figures from my shopbranches:Branch Month Sales-----------------------London Jan 5000London Feb 4500London Mar 5200Cardiff Jan 2900Cardiff Feb 4100Cardiff Mar 3500The question I am trying to ask is this: in which month did each branchachieve its highest sales? So I want a result set something like this:Branch Month----------------London MarCardiff FebI can do a "SELECT Branch, MAX(Sales) FROM MonthlySales GROUP BY Branch" totell me what the highest monthly sales figure was, but I just can't figureout how to write a query to tell me which month corresponded to MAX(Sales).Ideas anyone?Cheers,....Andy
View 5 Replies View RelatedTransact SQL :: Find Unique Rows In Column That Are Associated With Another Field In Another Column?
May 1, 2015I am having issues trying to write a query that would provide me the unique GUID numbers associated with a distinct PID if the unique GUID's > 1. To summarize, I need a query that just shows which PID's have more than one unique GUID. A PID could have multiple GUID's that are the same, I'm looking for the PID's that have multiple GUID's that are different/unique.
Table1
GUID PID
GUID1 PID1
GUID1 PID1
GUID1 PID1
GUID2 PID1
GUID3 PID2
GUID3 PID2
GUID3 PID2
The result of the query would only have PID1 because it has two unique GUID's. PID2 would not be listed has it has the same GUID3 in each row.
Result:
PID1
Find Duplicate In Column Tied To Another Column
Apr 29, 2015I have one table with two columns, GUID and PID and another table with three columns, GUID, LastName, and FirstName. For each unique PID, I need to find the PID's that have more than one GUID and then match that with their respective FirstName and LastName from the other table.
Table1
GUID PID
GUID1 PID1
GUID1 PID1
GUID1 PID1
GUID2 PID1
GUID3 PID2
GUID3 PID2
GUID3 PID2
Table2
GUID LastName FirstName
GUID1 Mulder Fox
GUID2 Scully Dana
GUID3 Skinner Walter
So I'm looking for a result like:
PID1 GUID 1 Mulder Fox
----- GUID 2 Scully Dana
How To Find Column Name Without Knowing Column Name
Sep 5, 2015I was querying to find the first non null address value using the COALESCE function.And I got the correct result.But then I jumped into another question and i.e what if I need to find the column name without knowing the column nameand just by using the column value.What I mean is this...My query was.....
SELECT COALESCE(AddressLine1,AddressLine2) AS [Addresss] FROM Person.Address
This is what I got.
Address#500-75 O'Connor Street#9900 2700 Production Way00, rue Saint-Lazare02, place de Fontenoy035, boulevard du Montparnasse081, boulevard du Montparnasse081, boulevard du Montparnasse084, boulevard du Montparnasse1 Corporate Center Drive1 Mt. Dell Drive
But then what if I just know the address of that person i.e #500-75 O'Connor Street..How am I suppose to retrieve that without knowing the column name.
Find A Key In All Tables
Oct 28, 2013I have many tables that have as one of its keys POLICY_NUMBER is there a way to write a script that will look in all tables in a db for a certain POLICY_NUMBER
View 5 Replies View RelatedFind Tables With No PK
Jan 27, 2004I modified my origianl script (used a cursor) to use a looping method I saw in a different thread http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=30286 by Nigel Rivett. Since I used code on this board I though I would post back for the greater SQL Team script toolbox. Feel free to use it to catch those deliquent sql programmers who forget to create a PK. Enjoy.
declare @database varchar(128) ,
@maxdatabase varchar(128) ,
@cmd nvarchar(1000)
create table #pk (databasename sysname, tablename sysname)
select @database = '', @maxdatabase = max(name) from master.dbo.sysdatabases where dbid > 5
while @database < @maxdatabase
begin
select @database = min(name) from master.dbo.sysdatabases where dbid > 5 and name > @database
set @cmd = 'select ''' + @database + ''', o.name from ' + @database + '.dbo.sysobjects o join ' + @database + '.dbo.sysindexes i on o.id=i.id
where o.xtype=''U'' and i.indid = 0'
insert into #pk exec sp_executesql @cmd
end
select * from #pk
drop table #pk
- Eric
Find Available Tables In A DB
Mar 20, 2008I am brand new to using SQL. I created a DB with a bunch of tables there. What I would like to do is from my VB.NET application I would like to allow the user to see what tables are available in that DB.
Also I need a way to check if a table exists. I used the following:
strTemp = "IF EXISTS " & _
"(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'simplesql') AND type in (N'U'))"
cmd.Connection = conncmd.CommandType = CommandType.Textcmdcmd.ExecuteNonQuery()
but I get an exception : Incorrect syntax near ')'
thanks for your help
How To Find All The Tables Which Are In Database
Feb 24, 2008Hi......i want to find all the tables which are in database. is there any hint or code that can help meThanks in advance
View 4 Replies View RelatedHow To Find Out Tables Which Can't Be Replicated
Jun 22, 2004Is there any query to find out all the tables without a Primary key or without a Unique index ?
View 3 Replies View RelatedFind Same Coumn In Other Tables
Jun 9, 2008Is there any way i can find some certain fields in the whole database? like i know a column called Type_ID in table Sys_Type, how can i know there are same Type_ID column in other tables? Thanks in advance.
View 2 Replies View RelatedFind All Differences Between 2 Tables
Mar 5, 2015I have this 2 tables:
Table1: Table2:
ID Value ID Value
--------- ---------
1 2 2 1
2 1 3 4
4 3 4 4I
need to find all differences between the 2 Tables.
A mismatch can be:
- Table1.ID exists, Table2.ID does not exist
- Table2.ID exists, Table1.ID does not exist
- Table1.ID = Table2.ID and Table1.Value <> Table2.Value
So I want to be returned:
ID Value ID Value
--------------------
1 2 NULL NULL
4 3 4 4
NULL NULL 3 4
There must be something simpler than the solution I came up with which is:select * from Table1 a full outer join Table2 b on a.Id = b.Id
WHERE a.value <> b.value or (a.value IS NULL and b.value IS NOT NULL) or (a.value IS NOT NULL and b.value IS NULL)Martin
How To Find The Tables In A View
Nov 15, 2006Dear friends,
is there any way to find the table names from a view?
ex: suppose i have a view named as vorganization.i need all the tables in this view. is it possible to get through a query? same case for procedures and functions also.........
or we have to use sp_helptext?
is there any better way?
thank you very much.
Vinod
How To Find No. Of Databases And Tables?
Mar 26, 2008Hi,
is there any way to find out the no. of databases and
no. of tables in a particular database using a sql query ???
If any server variable is used then please mention that???
thanx in advance
San
How Can I Find Connection Between Two Tables???
Mar 19, 2008Hi,
I have a database with 150 tables.
I need to find connections between specified tables. (I need to find through what fileds and tables are table1 and table2 connected)
Is there any tool in SQL Server for this?
Thanx
How To Compare Two Tables And Find The Gap?
Mar 18, 2008
Hi,
We have inventory table which is updated everyday.
I need to find the gap between updated table vs history (which means yesterday's data).
what i did is I just copy the inventory everyday before it is updated.
So, whenver inventory table is updated I want to know the gap between these two (history vs updated) tables.
The structure of two tables are same and here are columns:
date_key (fk)
store_key(fk)
item_key(fk)
begin_inventory_dollars
ending_inventory_dollars
begin_inventory_units
ending_inventory_units
I want to compare each row and if the value is changed I want to have a gap (updated value - history value) and if data is new then just want to add.
Here is tsql: however when I compare to gap between table from excel spreadsheet and this query, this query does not return a right value. (Some are correct but some don't)
Code Snippet
select a.date_date_key,
sum(a.Beg_Inventory_Dollars- (case when b.beg_inventory_dollars is NULL then 0 else b.Beg_Inventory_Dollars end)),
sum(a.Beg_Inventory_Units-(case when b.beg_Inventory_Units is NULL then 0 else b.beg_Inventory_Units end)) ,
sum(a.Ending_Inventory_Dollars- (case when b.Ending_Inventory_Dollars is NULL then 0 else b.Ending_Inventory_Dollars end)),
Sum(a.Ending_Inventory_Units-(case when b.Ending_Inventory_units is NULL then 0 else b.Ending_Inventory_Units end))
from UPDATED TABLE a
left outer join HISTORY TABLE b
on b.item_key = a.item_key and
b.store_key =a.store_key and
b.date_key =a.date_key
group by a.fisc_date_key
Did I miss something in here?
Any kind of advice would be grealy appreciated.
Thanks.
How To Drop An Identity Column From All Tables Tables In A Database
Mar 2, 2008Does anyone have a script that can drop the Identity columns from all the tables in a database? Thanks
View 1 Replies View RelatedTransact SQL :: SUM Of Two Table Column Base On Another Column Value And SUBTRACT And Join Tables
Oct 14, 2015I have the following table
Table Name EmployeeInformation
EmployeeID EmployeeFirstName EmployeeLastName
1 |John |Baker
2 |Carl |Lennon
3 |Marion |Herbert
Table Name PeriodInformation
PeriodID PeriodStart PeriodEnd
1 |1/1/14 |12/30/14
2 |1/1/15 |12/30/15
[code]...
I want a query to join all this tables based on EmployeeID, PeriodID and LeaveTypeID sum of LeaveEntitlement.LeaveEntitlementDaysNumber based on LeaveTypeID AS EntitleAnnaul and AS EntitleSick and sum AssignedLeave.AssignedLeaveDaysNumber based on LeaveTypeID AS AssignedAnnaul and AS AssignedSick and subtract EntitleAnnaul from AssignedAnnual based on LeaveTypeID AS AnnualBalance and subtract EntitleSick from AssignedSick based on LeaveTypeID AS SickBalance
and the table should be shown as below after executing the query
EmployeeID, EmployeeFirstName, EmployeeLastName, PeriodID, PeriodStart, PeriodEnd, EntitleAnnual, AssignedAnnual, AnnualBalance, EntitleSick, AssignedSick, SickBalance
Find Max Serial Number From 2 Tables
Feb 11, 2008I need a query to find max serial number by comparing two different tables. Here is my requirementI am having two tables named Table1 and Table2. Each tables having more than 30,000,000 records.I want a simple query to find Max srno from two tables.For exampleIf Table1 max is 245 where partno=2 and ano=2and Table2 max is 343 where partno=2 and ano=2Then 343 is max serial noIf Table1 max is 435 where partno=2 and ano=2and Table2 max is 34 where partno=2 and ano=2Then 435 is max serial noI used this query but its taking more time select max(v.MaxSrNo) from ((select max(MaxSrNo) as MaxSrNo from Table1 where partno=@partno and ano=@ano)union all (select max(MaxSrNo) from Table2 where partno=@partno and ano=@ano)) as v Pls give me a simple query to find max srno.
View 3 Replies View RelatedHow To Find Text/data In All Of My Tables?
Jan 18, 2000Hello,
I have a quick question. Can you please tell me how to find/search a string/value in all of my tables in a database.
For example I need to find a value/data "GH45678", where-ever it appears in a database.
I would really appreciate your prompt response. I would also like to thank you very much in advance for your time/help.
Note: I need to find specific data in all of my tables, I am looking for something like text search in my database. I am using SQL Server 6.5. Thanks
How To Find Out All The Statistics From All The Tables And Drop Them
Jul 21, 2003How to find out all the statistics from all the tables and drop them..any script anyone can help with?
When we are trying to make datatype changes in few related tables,it's giving error saying that some statistics are dependent onthe column blah blah...
Thanks,
Sheila.
How To Find Maximum Number From Two Tables
Feb 19, 2012My db contains two table Employee_detail and Student_Detail.Employee_detail has Emp_id and Student_Detail has stud_id.Now my problem is that i want to get one maximum no from these two table.
View 11 Replies View RelatedCompare Two Tables And Find The Occurance
May 17, 2008Hi friends,
I have a two table with following fields, table names are tbl_userinfo, tbl_Property.
tbl_userinfo fields are
user_id name
1 dhin
2 Mike
3 sam
4 Red
tbl_Property fields are
prpty_Id User_Id Address
1 1 3CostalRoad
2 1 westbengal
3 2 Loasass
what i want to do is, if tbl_info User_id occures in tbl_property, i want to display that full info abt tbl_userinfo
after comparing two tables Expected result is
user_id name
1 dhin
2 Mike
Please help me how to do this