I have 3 database say
db1
db2
db3
I need to setup a script to read all the table names in the database above and then query the database to find the list of Stored Procedure using each table.(SQL Server)
Hi all, I need a favour from you... can i do a list to find out the table names used by a stored procedure. remember it's around 700 Stored Procedures. and following a rule <databasename>.dbo.<tablename>
View 1 Replies View RelatedI have a database and I want to retrieve informations about all tables
defined in this database. In other words I need the table list defined in
database. Can you help me?
Thank you.
Is there a way to find all the table names that have a foreign key that references FILE_ID_IN([FILE_ID])
Note: The table def below has a foreign key
IF object_id('CODE_MAP_IN', 'U') IS NOT NULL DROP TABLE CODE_MAP_IN
Print 'Creating table CODE_MAP_IN...'
Create table CODE_MAP_IN
(
[CODE_MAP_FR] [varchar] (53),
[CODE_MAP_TO] [varchar] (53),
[FILE_ID] [float] DEFAULT 1000
PRIMARY KEY ([CODE_MAP_FR]),
FOREIGN KEY ([FILE_ID]) REFERENCES FILE_ID_IN([FILE_ID])
) ON [PRIMARY]
GO
how to find the columns names in a table in sql 2008r2.
as i need to compare the midsing fields in the table from two database.
I have a SQL text column from SP_who2 in table #SqlStatement:
like 1row shown  below :
 "update Panel  set PanelValue=7286 where PanelFirmwareID=4 and PanelSettingID=9004000"
I want to find what table and column  names are in the text ..
I tried like below .. Â
Select B.Statement from #sp_who2 A Â
LEFT JOIN #SqlStatement B ON A.spid = B.spidÂ
 where B.Statement IN (
SELECT T.name, C.name FROM sys.tables T
JOIN sys.columns CÂ
ON T.object_id=C.object_id
WHERE T.type='U'
)Â
Something like this : find the column names and tables name
To find parameter names
In ASP.Net 2.0, how can I get what parameters defined in an rdl report file? I know the report name and the report is deployed to a server.
Thank you for taking the time to read this, I need all the advise and help I can get on this ... so please post anything you think would work ... A little confused I am:
Have a database table called "people" with "person name" and "ID" field. My ASP.NET application mainly stores articles in article table. An article's Article text mentions various people's names in different combinations (e.g. John, Smith, John Smith, Smith John, etc)
Is there any way, I could compare the article text stored in article table with people table and get the people from people table along with their ID's who have been mentioned in that article? ... so in an article "i love john smith ... and i think Mr smith has always been helpful", I get John Smith back...
Not too sure being honest, what is the best way of implementing this, looking for the most efficient way, probably using XML? SQL Query or may be ASP.NET's code behind?
Thanks once again for taking the time.
Cheers,
Tyro
Hi,
I need a query to get the index names of particular tables. for eg.. i have some tables like emp_data,emp_job....etc..Now i want to find all indexe names for those tablenames that starts with emp........ Plz help me...
how to find the names of the tables owned by the particular user in sql server and how to display the distinct object types owned by the particular user.
View 1 Replies View RelatedI need to find column names if any after 2 “check” columns.
Scenario: I have a database, with approx 400-1500 tables, depending on installation of software. The software is structured so that, when it synchronizes the SQL database it will create all the columns e.g. custacc, custname etc. and then it will always put in two check columns “CheckOne” and “CheckTwo” these two columns has to be the two last ones. In 99.9 this always works fine, but sometime if the users creates a new field in the software, when it synchronizes the new field “lands” behind the two checkfields, which is not good.
So what I am after is a script, which can run through all user tables, tell me if there are columns after the two checkfields and list those tables if any.
Any help would be greatly appreciated.
Cheers
Henrik.
1. I need to find the names of the customers who have purchased academic books. (Coding required as Subquery NOT as Join)
2. Here, I need to show a list of authors who have written books and list the books they have written.
Sort the list by last name
(Coding required as Subquery)
I 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 RelatedI have a table name and I want to get the column name of the PK of this table.
Do you know how can I do this ?
I am trying to import a sql table from one sql database to another using DTS. I am getting an error and when I dblclick on the row it says
'Error at destination row 44324 .Errors so far encountered in this task 1. Unspecified error'
How do I find and list row 44324 ? Or is there something else I should do/check
TIA
Dear all,
i want to find the second max no from a column can anyone help me in this
I have a Product table with the columnsAcctNumProdCodeInvoiceDateI can have multiple rows for a given AcctNum:123 A 01/01/2005123 B 01/02/2005123 C 01/03/2005234 C 02/01/2004345 A 01/01/2005345 B 01/02/2005I need the max(InvoiceDate) and if the max for a given AcctNum is a ProdCode= B. So if the latest InvoiceDate is for a given AcctNum is B then returnthat row.123 B 01/02/2005Would not be returned because the max Invoice date for AcctNum 123 isProdCode C.345 B 01/02/2005Would be returned because the max Invoice date for AcctNum 345 is ProdCodeB.I can solve this using a cursor fairly easily by using a distinct AcctNum inthe cursor select and getting the max InvoiceDate for each AcctNum. This isa costly and I'm looking for a solution using temp tables or a query tohandle this problem.I hope I have made this clear enough (sorry if I was too verbose). Thanks inadvance for your help.-p
View 2 Replies View RelatedHi Folks,
Can any one tell me how to find table which has max records, let say i hve 50 tables in my database so how to find particular table has max records.
Thnx & Regards
Deepa.
Dear all experts,
I need to find out some property of a tableAs following sql statement:
select mtable.name,mcolumn.name,mtype.name,mcolumn.is_identity
from sys.tables mtable, sys.all_columns mcolumn, sys.types mtype
where mcolumn.object_id = mtable.object_id
and mcolumn.system_type_id = mtype.system_type_id
and mtable.name in ('TestTable')
order by mtable.name,mcolumn.column_id
But there is no PK information.
Follwing sql statement shows the PK name of a table, but no composite info(which fields):
select mtable.name,mkey.name
from sys.key_constraints mkey, sys.tables mtable
where mkey.parent_object_id = mtable.object_id
and mkey.type = 'PK'
and mkey.name like 'TestTable'
How can I find which fields are PK in a table?thanks...
-Winson
I have table 'stores' that has 3 columns (storeid, article, doc), I have a second table 'allstores' that has 3 columns(storeid(always 'ALL'), article, doc). The stores table's storeid column will have a stores id, then will have multiple articles, and docs. The 'allstores' table will have 'all' in the store for every article and doc combination. This table is like the master lookup table for all possible article and doc combinations. The 'stores' table will have the actual article and doc per storeid.
What I am wanting to pull is all article, doc combinations that exist in the 'allstores' table, but do not exist in the 'stores' table, per storeid. So if the article/doc combination exists in the 'allstores' table and in the 'stores' table for storeid of 50 does not use that combination, but store 51 does, I want the output of storeid 50, and what combination does not exist for that storeid. I will try this example:
'allstores' 'Stores'
storeid doc article storeid doc article
ALL 0010 001 101 0010 001
ALL 0010 002 101 0010 002
ALL 0011 001 102 0011 002
ALL 0011 002
So I want the query to pull the one from 'allstores' that does not exist in 'stores' which in this case would the 3rd record "ALL 0011 001".
So randomly every 1 to 6 days queries start timing out and I'm almost positive it's from an improperly terminated transaction
Is there a way to snoop this out the next time it happens? Like when a table's locked I can look and see yea this is the transaction it's in the middle of?
Find value match in SQL table
Is there any application that can compare two tables and find the similarities (there is no high rate of exact match on the field values but there are similarities)?
Hi,
I have table which stores the fund name and its data. We get quarterly information from the fund co. Suppose if the user wants to add a fund thats not in our database we let then add a ClientFundId and a FundName. But may be after sometime the fund company may add that fund in the next quarter.. So how do i get rid of Duplicated Data..
In the ClientFundId column we can a 9 letter Aplhanumeric or a 5 letter character but if the fund co.. provides those values the 5 letter characters are stored in Ticker column and the 9 letter words are stored in Cusip column.. So i just wrote this query hoping i could retrieve the duplicate values but it didnt list any..but i found one this is my query..
Select
FundId,
Cusip,
Ticker,
ClientFundId,
FundName,
ShortName
From Fund
Where
ClientFundId = Ticker
or
ClientFundId = Cusip
Any help will appreciated
Thanks
Karen
Hello,
I have 2 tables, and use objectdatasource and stored procedures, with sql server.
Let say in the first table I have IDCustomer as a datakey, and other records, and in the second I have the same IDCustomer and CustomerName. I have an INSERT stored procedure that will create a new record in the first table (so generate a new IDCustomer value), and I would like to insert immediately this new value in the second table.
How can I know the value of this new IDCustomer ? What is the best way to handle that ? Once the insert in the first table is done should read it the table and extract (with an executescalar) the value and then insert it in the second table ? This solution should work but I am not sure this is the best one.
Thanks for your help.
I'd like to know if I can make one proc/cursor that I can pass only a table name and it will determine what cols exist in the table?
AND, what is the syntax for such a query?
I will be receiving data back where I will receive the accountID and only the deltas will have values all other columns will be null.
My proc will update an existing record, updating the specific col when an accountID exists, it will create a new record if the accountID does not exist.
I'd like to search all the cols getting their names and values when not null.
I can construct a proc for each table searching each col by known name.
However, I'd like to know if I can make one proc/cursor that I can pass only a table name and it will determine the cols?
All my tables start with cols AccountID and end with RecID with any number of cols inbetween.
TIA
JeffP...
cross posted to ms.pub.mssqlserver.programming
Im trying to find a table and Drop in T-SQL
using this script.
/* Start */
Use Students
IF exists (Select * from information_schema.tables
where table_name ='##Exams_result)
drop table ##Exams_result
go
Create table ##Exams_result..............etc
/* end */
But I cant find my temporary table on this way...
Any sugestion?
Luiz Lucasi
lc@culting.com
Rio de Janeiro - Brazil
does someone have a querry to display the duplicate records in a table.
Table:
zipcode dma
My data upload is failing because there is a primary key on zipcode and the source data (42k records) has about 50 duplicate zipcode records in it. It is possible that there is a unique combo of zipcode / dma but I need to identify the duplicate records to determine that.
I need a snippet of code that will determine wether or not a table exists in a database...
thanx
Hi,
I need to find the text data type column in all the table.
Please let me know the script.
Thanks,
Ravi
Hi Frinds,
How can i find out the no of rows in each table. if the no of table are more than thousands(i.e. 1000+,2000+ etc..) are there. Certainly 'select count(0) from <table>' is not the good idea. Egarly awaiting for the solution.
thanks
Nageswara Rao Bomma
nrbomma@yahoo.com
nrbomma@hotmail.com
I need to find the primary key of a table, in MySQL i used SHOW COLUMNS and looped through them to find which one was primary if any. The MSSQL equivalent is SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = 'table_name' apparently. However the result doesnt give me any key information. How can i find out
1. if a primary key exists on a table
2. what column that primary key exists on
How could i find all tables name which have column value 'Ferrari'
.
.
.
How could we find table name from it's column value?????
Hello team.
I have an issue I'm hoping you can help me with.
I have very large sql server with 15 databases and thousands of tables. We have an "employee" table where we have historically been joining to the "EmployeeName" field in procs, views, etc.(bad practice, I know). I would like to now go back and make things right. I would like to identify ALL procs and views that have a join on the "EmployeeName" field, and modify it to use the "employeeid" field. I would like a script or a suggestion that would help me identify all the places where I would need to make this change.
I thought of querying the syscomments table, but the joins are not always laid out the same way so I know I wouldn't be able to catch all of them. Maybe using profiler to capture all statements executed and have them trigger an email to a DB developer every time? I don't know... Suggestions?
Many Thanks