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 RepliesI 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 RelatedI 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 RelatedHi 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.
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 View RelatedI 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 RelatedBeing 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
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?
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 '-'
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
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
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 RelatedI 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;
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]....
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] ...
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
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
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 RelatedCREATE 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 RelatedI'm new to my company, although not new to SQL 2005 and I found something interesting. I don't have an ERD yet, and so I was asking a co-worker what table some data was in, they told me a table that is NOT in SQL Server 2005's list of tables, views or synonyms.
I thought that was strange, and so I searched over and over again and still I couldn't find it. Then I did a select statement the table that Access thinks exists and SQL Server does not show and to my shock, the select statement pulled in data!
So how did this happen? How can I find the object in SSMS folder listing of tables/views or whatever and what am I overlooking?
Thanks,
Keith
Hello,
I am very new to SQL and I have an issue. We have a website that has pictures on the site. We want to take them down but the pictures are somehow tied into the database, they aren't on any of the servers. How can I find these pictures that seem to be somehow hard coded into the database?
Any help would be greatly appreciated.
i have
create table test
(id_text int
,text_name varchar(10)
,id_author int)
insert into test (id_text, text_name, id_author) values (205, 'name1', 123)
insert into test (id_text, text_name, id_author) values (205, 'name2', 124)
insert into test (id_text, text_name, id_author) values (205, 'name2', 125)
insert into test (id_text, text_name, id_author) values (206, 'name2', 124)
insert into test (id_text, text_name, id_author) values (207, 'name2', 122)
insert into test (id_text, text_name, id_author) values (207, 'name2', 128)
select id_text, id_author from test
and i need to get out only id_text with the first author.
any solutions for sql server 2000?
thank you
I have a report that is grouped by week. The details of the week have the date, time interval (half hour), and the maximum value for that day. So in the example below I am showing the week of May 21st. Then I have all of the days of that week, sorted by date as requested, with the halfhour the maximum value occured in on that day. So Monday the 22nd has the maximum value for the week at 254 (and is highlighted in red). Where the ????? are below, I need to display '1400' which is the interval, but have not yet been able to figure out how to do this.
I tried the following expression:
=iif(max(Fields!MaxValue.Value) = Fields!MaxValue.Value, Fields!Interva.Value, "X")
but this only works if the first day is the maximum value.
5/21/2006
?????
254
Sun, 5/21/2006
1000
147
Mon, 5/22/2006
1400
254
Tue, 5/23/2006
1830
141
Wed, 5/24/2006
1830
107
Thu, 5/25/2006
0830
210
Fri, 5/26/2006
1900
36
Sat, 5/27/2006
1000
149
Any ideas are greatly appreciated!
I have, say 4 rows for 1 client I want to find the date difference between two(or more) of the rows when the first has a true flag and the next does not
Row1 Date1,falseRow2 Date2,trueRow3 Date3,falseRow4 Date4,false
so I want to find the difference between Row2 Date2 and Row3 Date4I know I can loop through the rows but with 000's of clients each with x numbers of rows this will surely be very slow
I have a form where users can enter an appointment on this form some personal details are entered. I want the users to be able to write in a textbox the users surname. they will then press a button next to the textbox that will bring up a smaller screen displaying that users details :
SELECT * FROM Members where surname = (textbox1)
Alternatively the users can click the button and bring up a screen where the users can put the surname into a textbox and then bring up the users details this way.
I am not sure how to do this or how to select from the database where a field in the database is the same as the information put in to a text.
Is this possible? Thanks in advance Mike
I have a table TEST.
I want to find the triggers which use this table test.
For example: If there is a table A, and a insert in A causes a trigger to insert data in TEST,
I want to find this particular trigger.
Any ideas?
I am using SQL server 2000
thank you
hai
I have to delete a single row from a table , where there are multiple rows with the same data and i want to delete only one row and i cannot alter my table
structure.
Thanks in advance
satya
I have a customer database with the following structure:
Dept ID (int)
Section (varchar)
I need to find only occurrences of a section (eg Admin) where the section name has a record in Dept 1,2 and 3 - only return the result if the record for Admin is associated will these depts.
Example Data:
Dept Section
1 Admin
1 Finance
1 Marketing
2 Admin
2 Sales
2 Marketing
3 Admin
3 Sales
3 Finance
The query would only return 'Admin' since this is the only Section that is represented in Departments 1,2 + 3.
I am not in control of the data design and can't create a bitwise field to achieve this.
tia,
Steve
How can I find out a user's suid or login name from within a stored procedure?
View 1 Replies View RelatedHi
My problem is a little tricky. I am trying to get the first distinct row(s) from a table where one column will have only a few differences, while a second is unique.
Now using "distinct" doesnt help as even though distinct(column1) may return only a few rows, because the next column is unique you will still have all rows returned.
How do I return only 1 row per distinct(column1) and drop the rest? What makes this so annoying is that all columns need to be returned, but only in accordance to distinct(column1)
Hi, sorry if this comes across as a stupid question, but i'm new to SQL. I have had a look on Google and on these forums but not found the answer..
I am running MS SQL 7 with a database containing around 130 tables.. i want to know if there is a query/command that i can type that will look through all the tables and tell me which ones reference a specified table (through it's key). Hope this makes sense! i've looked at some of the stored procedures but not found any that give me anything useful.
EDIT: Just so you are aware, i did not create this database, hence why i am not aware of the relationships myself, i have only just started working on it.
Thanks
Steve
I have a column of data of type varchar. it contains a mix of text id's and numeric id's.
all i want is the highest purely numeric value
The closest i can get is to use a regex expression, but it don;t work and i just know there is a better way of doing it.
SELECT * FROM customers WHERE custref LIKE 'd*d';
I am using MS SQL 7
thanks so much for any help
I have a script that creates these spin off tables (used for lightning fast access) on a near daily basis. But it is possible the table does not exist for that day - in which the script need to check one day back - so on and so forth until finding the most recently created table.
The script I have written fails with overflow error.
I was hoping someone could tell me the correct syntax to accomplish this and also why i recieve this overflow error...
Code:
Dim str_SQL_Asset_1
daysBack = 1
tblFound = FALSE
Dim xbl_Holdings_date
Dim xMonth
Dim xDay
xbl_Holdings_date = date()
If Len(Month(xbl_Holdings_date))=1 Then
xMonth = "0" & Month(xbl_Holdings_date)
Else
xMonth = Month(xbl_Holdings_date)
End If
If Len(Day(xbl_Holdings_date))=1 Then
xDay = "0" & Day(xbl_Holdings_date)
Else
xDay = Day(xbl_Holdings_date)
End If
DO WHILE tblFound = FALSE
xbl_Holdings_Name = "xbl_Holdings_" & Year(xbl_Holdings_date) & "_" & xMonth & "_" & xDay
SQL_tblCheck_STR = "SELECT * FROM INFORMATION_SCHEMA.TABLES " & _
"WHERE table_name = '" & xbl_Holdings_Name & "' "
Set MM_checkCmd = Server.CreateObject("ADODB.Recordset")
MM_checkCmd.ActiveConnection = conn_str_ClientDB
MM_checkCmd.Source = SQL_tblCheck_STR
MM_checkCmd.CursorType = 0
MM_checkCmd.CursorLocation = 2
MM_checkCmd.LockType = 3
MM_checkCmd.Open()
IF MM_checkCmd.EOF Then
xbl_Holdings_date = (xbl_Holdings_date - daysBack)
daysBack = daysBack + 1
tblFound = FALSE
ELSE
tblFound = TRUE
MM_checkCmd.CLOSE()
SET MM_checkCmd = Nothing
END IF
loop
The goal of this snippet is simply to identify and set the value for the existing table name.
An example table name would be "xbl_Holdings_2006_12_10".
I could have posted this question in the VB forum but I would like to push as much of the work onto SQL as possible and also - this sort of operation is more likely to be required of a dba imo.