Query To Understand The Names Of All Available Tables In Database
Aug 31, 2014SQL query to understand the names of all the available tables in the database , number of records in these tables and size of these tables ?
View 1 Replies
ADVERTISEMENT
SQL 2012 :: Query To Understand Names Of All Available Tables / Number Of Records
Aug 31, 2014SQL query to understand the names of all the available tables , number of records in these tables and size of these tables?
View 4 Replies View RelatedTwo Different Tables, Each With Identical Column Names... Query Help
Nov 18, 2005I have two different tables... one for all Staff, and another for all Temp Staff. I need both to output to a datagrid, and so I need to grab both tables from a SQL query to output to my datagrid, but I can't seem to get the logic right for it to work. Can someone give me some suggestions on why my results are blank when I'm running this query? I thought a simple join would allow both sets of identical column names to coexist in peace...SELECT TOP 100 PERCENT dbo.StaffDirectory.UserName, dbo.StaffDirectory.LastName, dbo.StaffDirectory.FirstName, dbo.StaffDirectory.Dept, dbo.StaffDirectory.Title, dbo.StaffDirectory.EMail, dbo.StaffDirectory.LocationFROM dbo.StaffDirectory INNER JOIN dbo.TempStaff ON dbo.StaffDirectory.Location = dbo.TempStaff.Location AND dbo.StaffDirectory.EMail = dbo.TempStaff.Email AND dbo.StaffDirectory.Title = dbo.TempStaff.Title AND dbo.StaffDirectory.Dept = dbo.TempStaff.Dept AND dbo.StaffDirectory.FirstName = dbo.TempStaff.FName AND dbo.StaffDirectory.LastName = dbo.TempStaff.LName AND dbo.StaffDirectory.UserName = dbo.TempStaff.UName AND dbo.StaffDirectory.MDNo = dbo.TempStaff.MDNoIs something wrong here? It just doesn't work =(Any suggestions would be really appreciated.Thank you
View 5 Replies View RelatedHow To Query Sys Tables For Index Names And Columns
Nov 12, 2001I'm looking for a query that will return all index names, the table the index is on and the columns in the index...
View 1 Replies View RelatedDatabase Tables And User Names
Jan 26, 2007We have recently copied a database from one machine to another. On the old machine, when we access the tables we do not need to use the username.tablename convention to query them. On the new box we do.
For example, to query a table called Page we would nee to do this on the new box.
SELECT *
FROM webdev.page
unfortunately all the code is written, without the username prefix. Is there a way to not use the username prefix?
Thank you for your help...sorry for the newbie type question.
I Need A Query To Find Indexes Names For Some Kind Of Tables
Apr 9, 2008Hi,
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...
Compare Column Names From Two Tables In Same Database
May 17, 2008I'm not a full-time DBA, so excuse my style of expressing my question.I have a database which has 2 tables in SQL 2005. Both these tables have similar column names, EXCEPT for new extra columns in FY2007_DATA. I can visually see the difference in columns in Database Diagrams. My goal is to :- I want to compare FY2007 tbl column names to FY2006 column names and display only those columns as results that do not match.Tbl 1 :- FY2006_DATA Tbl 2:-
FY2007_DATA
With online reading and help I have managed to get this script to do exactly opposite of what i want. Below is the query
/* This query compares the column names from two tables and displays the ones that have an exact match. It does not care for case-sensitiveness */
Select a.Table_Name, a.Column_Name, (b.Table_Name), (b.column_name)
From [2006-2011].INFORMATION_SCHEMA.Columns AS a
Join [2006-2011].INFORMATION_SCHEMA.Columns AS b on a.Column_Name = b.Column_Name
Where a.TABLE_NAME = 'FY2006_DATA'
And b.TABLE_NAME = 'FY2007_DATA'
AND a.Column_Name IN
(Select Column_Name = LEFT(c.column_name, 20) FROM
[H1B_2006-2011].INFORMATION_SCHEMA.Columns AS c WHERE c.TABLE_NAME = 'FY2007_DATA' )
When I change "AND a.Column_Name IN.." to "AND a.Column_Name NOT IN.." so that the results will (should) display the extra columns in FY2007, in fact I do not see any results, but query executes perfect.
How can I achieve my goal.?
Thank you
Regarding The Query To Save The Ids Of The Edited Names From Different Tables Into A Single Table.
Mar 8, 2008Here with the below query iam binding my gridview with industry name,company name,Plant Name,Group Name related to the IDs in Audit table.Select Aud.Ad_ID_PK,Aud.Audit_Name,Ind.Industry_Name,Cmp.Company_Name,Pla.Plant_Name,Gr.Groups_Name,Aud.Audit_Started_On,Aud.Audit_Scheduledto,Aud.Audit_Created_On from
Industry Ind,
Company Cmp,
Plant Pla,
Groups Gr,
Audits Audwhere Ind.Ind_Id_PK =Aud.Audit_Industry and
Cmp.Cmp_ID_PK =Aud.Audit_Company and
Pla.Pl_ID_PK =Aud.Audit_Plant and
Gr.G_ID_PK =Aud.Audit_Group and
Ad_ID_PK in (select Ad_ID_PK from Audits)
Now i want to edit these names.
when i click on edit in gridview these names will be filled into textboxes and when i change the names it should compare the name with particular tables and should get the Id of that and store in Audits table.
For example:
i have this data in my audits table:
Commercial83312
2
2
2
1
Here Commercial83312 is ID of that Audit and 2,2,2,1 are the Industry,Company,Plant and group Ids for that particular audit.In the front end i can see the names of this particular IDs.
when i edit the industry name in the UI it must check the name with industry table and get the ID of the changed name and store it in audit table.
so the data may be changed in audits table as :
Commercial83312
4
2
2
1
so here the industry ID is changed
I need the stored procedure for this.
please help me,its very urgent...
SQL Server 2008 :: How To Delete Tables In Database Whose Table Names Match A Criteria
Jul 22, 2015The database has approx. 2500 temporary tables. The temp tables match a pattern such as APTMP... I tried deleting the tables in SSMS with the statement, Delete from Information_Schema.tables where substring(table_name,1,5) = 'APTMP' This returns the error message"Ad hoc updates to system catalogs are not allowed".
What is the correct way to delete a group of tables whose name match a pattern from within SSMS?
Table Variables Vs. Temporary Tables - Need To Understand Something
Nov 5, 2007Hi all,
I had a problem in a stored proc when I was using table variables in SQL Server 2005. I fixed the problem by changing them to temporary tables. It works now but I really want to understand why I had to do what I did. Here's the situation:
Had a stored proc that cached quite a bit of data (100-200k records with 7 columns) into a table variable. Then, it looped through this and returned a result set. The table this procedure was querying, then caching, was highly transactional. After about 2 or 3 users were hitting the same area at once, the procedure locked. If there were no locks, it executed very fast. However, it usually timed out (due to this locking).
After researching, I see that your usually better off sticking with temp tables for large amounts of data - but the primary explanation I hear for this is that you can index it. However, it also seems that the temp table will perform read-locking as its an actual physical table, whereas the table variable will not. Therefore, because there's no locking and its entirely in memory, the table variable is often the better choice. This is confusing me though because the problem I was experiencing was some sort of locking - which I thought would have been less likely with table variables.
The only other thought I had is that the table variables (if there were several of them existing at once due to several users executing the same procedure), were causing some kind of memory limit to be hit, making the database wait until some more memory became available.
Sorry for the long post, but I'd really like to know if anyone else has had these issues and what the cause might be.
Thanks,
Mike
Simple Query Which I Am Not Able To Understand
Jun 13, 2008Hi,I have a float column in the database. When ever, I enter a value into the table, the values are rounded. Suppose I execute the following script: create table tblamount( flttotal_amount float)insert into tblamount values (0.00)insert into tblamount values (0.00)insert into tblamount values (0.00)insert into tblamount values (0.00)select * from #tblamount returns the following values:0000 but I want the ouput with the values 0.00 that I entered in the DB. Can u pls help me out with this?
View 2 Replies View RelatedTSQL Query - I Don't Understand
Apr 17, 2008Hi,
I hope that someone can help me understand why my query is not returning what I expect. When I run this query:
SELECT DISTINCT(TransactionKey)
FROM Transactions_Fact
WHERE DateKey = 14550
AND TransactionKey BETWEEN 1 AND 90000000
AND TransactionKey NOT IN (SELECT DISTINCT(TransactionKey)
FROM tmpTransactions
WHERE TranDate = 14550
AND TransactionKey BETWEEN 1 AND 90000000)
I get 150 rows back, which is what I expect. However, if I leave out the 'AND TransactionKey BETWEEN 1 AND 90000000', then I don't get back anything?!?
SELECT DISTINCT(TransactionKey)
FROM Transactions_Fact
WHERE DateKey = 14550
AND TransactionKey NOT IN (SELECT DISTINCT(TransactionKey)
FROM tmpTransactions
WHERE TranDate = 14550)
Any ideas as to what I'm missing here? It seems like it should at least return the same 150 rows. Thanks for any help.
Gary Hines
I Can't Understand The Meaning Of A Prediction Query
Jul 27, 2006Dear friends,
I'm reading Wiley's Data mining with SQL Server 2005... There are MANY things I can't understand about MovieClick example (Chapter 3).
I hope someone is going to help me with this troubles...
WARNING (1): I'm a dummy both with sql server and data mining.
WARNING (2): My English is not good at all.
Just two questions for now:
1) When I create the model to predict the number of bedrooms for homeowners, the book says to check BEDROOMS as Predictable... question: is it also an INPUT for the model, or PREDICTABLE only?
2) I'd like to keep this model (number of bedrooms.......) and make a prediction query.
- Query builder
- select case table -> Homeowners
- Drag the Customer ID column from the Homeowners table and drop it on the grid
- Drag the BEDROOMS column from the mining model and drop it on the grid.
- On the last row: Source=PredictionFunction, Field=PredictProbability
- Drag the BEDROOMS column from the mining model and drop it into Criteria/Argument
- Add (i.e.) 'Two or Three' to the field Criteria/Argument
I execute the query and I obtain many rows in a table with the following colums: CustomerID, BEDROOMS and Expression: WHAT DOES THIS MEAN?
WHICH INFO DO I GET FROM THOSE NUMBERS? WHAT CAN I LEARN FROM THEM?
Thanx a lot in advance, please help me!
SQL 2012 :: Extract All Tables Names And Their Row Counts From Linked Server Tables
Oct 7, 2015I am using the following select statement to get the row count from SQL linked server table.
SELECT Count(*) FROM OPENQUERY (CMSPROD, 'Select * From MHDLIB.MHSERV0P')
MHDLIB is the library name in IBM DB2 database. The above query gives me only the row count of table MHSERV0P. However, I need to get the names, rowcounts, and sizes of all tables that exist in MHDLIB librray. Is it possible at all?
Seek Help To Understand The Jet Database Engine
Oct 4, 2007Hi,
I am reading the book by Steven Roman:
"Access Database : Design and Programming" 3rd edition.
On page 120, Figure 7-2, he showed the the structure of the Jet Database Engine, which is very confusing to me.
According to this picture, I come to such an understanding:
1) VBA is just the hosting language for the Jet Database Engine;
2) Microsoft Visual Basic, Excel, Access, Word are hosting languages for VBA.
Isn't this weird? VB hosts VBA?
Thanks for any input!
Dynamic Tables Names And Temporary Tables Options
Oct 5, 2007Firstly I consider myself quite an experienced SQL Server user, andamnow using SQL Server 2005 Express for the main backend of mysoftware.My problem is thus: The boss needs to run reports; I have designedthese reports as SQL procedures, to be executed through an ASPapplication. Basic, and even medium sized (10,000+ records) reportingrun at an acceptable speed, but for anything larger, IIS timeouts andquery timeouts often cause problems.I subsequently came up with the idea that I could reduce processingtimes by up to two-thirds by writing information from eachcalculationstage to a number of tables as the reporting procedure runs..ie. stage 1, write to table xxx1,stage 2 reads table xxx1 and writes to table xxx2,stage 3 reads table xxx2 and writes to table xxx3,etc, etc, etcprocedure read final table, and outputs information.This works wonderfully, EXCEPT that two people can't run the samereport at the same time, because as one procedure creates and writesto table xxx2, the other procedure tries to drop the table, or read atable that has already been dropped....Does anyone have any suggestions about how to get around thisproblem?I have thought about generating the table names dynamically using'sp_execute', but the statement I need to run is far too long(apparently there is a maximum length you can pass to it), and evenbreaking it down into sub-procedures is soooooooooooooooo timeconsuming and inefficient having to format statements as strings(replacing quotes and so on)How can I use multiple tables, or indeed process HUGE procedures,withdynamic table names, or temporary tables?All answers/suggestions/questions gratefully received.Thanks
View 2 Replies View RelatedHelp Me Understand How To Move The Express Database To Live
Mar 23, 2006im using the the login controls with asp.net
when i test locally everything works great, its see the ASPNETDB.MDF file, checks the login info and passes the user on to the next page.However when i move the application to the live server it fails, it cant find the datasource that contains users/password etc. here is there error:An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) So im missing something here, im used to connecting directly to a remote SQL database and accessing the info from there.
Can someone point me in the right direction? Can the MDF file not be used off the server itself without SQL express installed? Do i need to migrate those tables into a real SQL database?Thanks,Sean
Can’t Understand The Query Execution Interval, For Every One Criteria It Takes Extra 60 Seconds. Help Please?,
Aug 17, 2006Can€™t understand the Query execution interval, for every one criteria it takes extra 60 seconds. Help please?,
Query A
SELECT TOP 15 * FROM CMD4 AS T1 WHERE T1.ST IN
(SELECT max(T2.ST) FROM CMD4 AS T2 WHERE T1.SERIAL=T2.SERIAL
AND T2.sd='20060817') AND sd='20060817'
--AND STOCKNAME like 'A%'
ORDER BY D_CUM desc;
The above query take 2 seconds to return result
Query B
SELECT TOP 15 * FROM CMD4 AS T1 WHERE T1.ST IN
(SELECT max(T2.ST) FROM CMD4 AS T2 WHERE T1.SERIAL=T2.SERIAL
AND T2.sd='20060817') AND sd='20060817' AND STOCKNAME like 'A%' ORDER BY D_CUM desc
This query same as above but " AND STOCKNAME like 'A%'" Criteria only added but it takes 01 minute 43 seconds to return result. why it is like that where i need to adjust the query i have no idea. please help
How To Basically Copy Tables With New Names Rather Than Create Similar Tables From Similar Manual Input.
May 26, 2007I have a table that I am basically reduplicating a couple of times for each part of this database that I want to create.Each table basically has the same data: The tables will be called motherTable, fatherTable, sonTable, daughterTable and so on.I am pretty much using the following in each column: UserID, MotherID(or FatherID or SonID, etc., etc. and so on for each unique table), FirstName, LastName, MiddleName, BirthPlace, Photo, Age.I don't see an option to copy a table and just modify the second ID part and rename that table accordingly.How can I make this an easier way of creating these similar tables without retyping all these columns over and over again?Thanks in advance.
View 4 Replies View RelatedGetting Names Of Tables And Fields
Oct 8, 2004Hi All,
I need to get the names of tables and fields (or atleast the fields) from a database. Is there a query I can use that will return what all the db's tables and those tables fields are?
Thanks
Lee
How Do I Get Names Of Tables, Part II
Mar 17, 1999------------
D.P. at 3/15/99 2:04:04 PM
Using VB5|6/ADO/MSSQL6.5. I want to get the names of tables in a query. I.e. if query is "Select Name, Title from Employee inner join job on employee.empid = job.empid" I want a way to get "Name, Title"
In VB 3 we used the VBSQL.VBX which had 2 functions for this: SQLTabCount and SQLTabName.
Thanks in advance
DP
------------
Gregory at 3/15/99 2:25:31 PM
dp ++++++++++++++++++
see my responses below
dp ++++++++++++++++++
if u need to get "Name" and "Title" - u're trying to get column(field) names??
-or-
if u need to get table names involved in a query - u need to get "Employee" and "Job"
so what exactly do u need??
********
dp ++++++++++++++++++
My bad... I wanted Employee, Job. The Table names.
dp ++++++++++++++++++
if u need to see column names - u can use Fields collection to get all fields in a recordset
I don't know if there's a way to get table names of tables involved in a query, in ADO.in RDO there's a property of a rdoColumn object to get table name (which doesn't work always anyway).
********
dp ++++++++++++++++++
actually this can be done in ADO. You can get the table from which the column was derived by using rs.fields(##).properties("BASETABLENAME") but I don't want to rely on this because I may not have a field from each table... What I really want is creat a recorset and ask it what tables it's drawing from. Again, VBSQL had SQLTabCount which gave you the number of tables in the query and then SQLTabName would give you the names by table number
dp ++++++++++++++++++
or, in select statement u can aliases:
select Name as 'Employee_Name',
title as 'Job_Title'
from employee, job
where employee.job_id = job.job_id
so, when you get all fields from fields collection, u can check field name
dp ++++++++++++++++++
yeah... i'll do this if I have to... looking for a better way (hopefully) first
dp ++++++++++++++++++
********
or , u can use ODBC API functions to get that information (for example using SQLColumns() function) - but, u'll need to connect first, to get connection handle, then prepare or execute statement to get statement handle, and then get information about columns (and table names).
dp ++++++++++++++++++
this sounds interesting.... where might I find more info about this??? If i create an ado recorset, will I be able to "analyze" it in this way via ODBC?
dp ++++++++++++++++++
Get Column Names From All Tables In A Db
Mar 7, 2005Is there a way that you could get the column names for each table in a database using 1 query?
something like:
tbl colname
t1 catID
t1 catName
t2 prodID
t2 prodDesc
t3 cartID
...
...
I know it would be long, but I would just be searching through the saved output for specific names.
Query For Tables In A Database...
Feb 23, 2004Good Morning
Shopping for help writing a query for my VB Program to execute against
SQL Server. Here is what I have so far:
SELECT name
FROM sysobjects
WHERE (xtype = 'U')
Is there a way to add to that query to get the list of tables that have the properties COST and PARTNUMBER?
Thanks,
Ed
Getting Primary Key Column Names For All Tables
Sep 9, 1999I have a situation in which i have to get the last value stored in the Primary Key for all the tables. Based on this value i have to update another table which stores the Table names and the last Key value for the table. The values in this table are not correct therefore i have to update it now. I was trying to write a cursor for this but the only problem is i can't get to know how to get the column name on which the primary key is defined for all the tables, thru code.
I would appreciate if someone could help me out with this.
Truncate Tables With Spaces In Their Names Using STP
Dec 16, 2005I am attempting to how to truncate list of tables using STP. That is decalre a cursor for a list table names and then to truncate the table names one by one.
The code below shows what I want to achieve. I want to truncate all the tables with names beginning with ZZ but this is failing. I have tried using both delete and truncate.
Is it possible and what do I need to do?
-- Code
SET QUOTED_IDENTIFIER Off
GO
SET ANSI_NULLS ON
GO
create PROCEDURE dbo.Empty_ZZ
AS
DECLARE @tablename sysname
DECLARE @localname varchar(50)
DECLARE ZZtablenames_cursor CURSOR FOR
select [name]
from sysobjects
WHERE [xtype] = 'U'
and name like 'ZZ %'
OPEN ZZtablenames_cursor
FETCH NEXT FROM ZZtablenames_cursor
into @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
set @localname = '[' + @tablename + ']'
TRUNCATE + @localname
FETCH NEXT FROM ZZtablenames_cursor
END
CLOSE ZZtablenames_cursor
DEALLOCATE ZZtablenames_cursor
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Joining Tables With Some Column Names That Are Same
Dec 13, 2013If you need to inner join 2 tables that have some columns names that are the same, how can you have those columns be named differently in the query result without aliasing them individually?
Tried select a.*,b.* from tbldm a,tblap b where a.id=b.id hoping the col names in the result would have the a.s and b.s in front of them but they didn't.
Return Tables That Have Certain Column Names
Feb 28, 2008Hi all,
From the INFORMATION_SCHEMA.TABLES view I want to return the TABLE_NAME of tables that have columns say, named Email and EmailStatusId. Is it possible to do this with a single select statement or would I have to use two selects for this?
Please advise.
Thanks in advance.
Query To Get List Of Tables From Database
Sep 21, 2004Can some one please tell me whats a best way ot get a list of all the tables that are present in a particular database.
Thanks
Query List Of All Tables In A Database
Mar 5, 2014I'm running SQL Server 2012 Express. I need to query a database and return the list of tables in the database. How to construct this query?
View 4 Replies View RelatedQuery Multiple Database Tables
May 3, 2006Sql2005??? -NEW to SQL. Have a database which creates tables basically named the same thing except the date. i.e. dbo.table05012006, dbo.table05022006. I need to query a table if the date is = yesterday. I am searching for a way to do this everyday dynamically. Is this even possible?
View 1 Replies View RelatedCreating /accessing Tables With Dynamic Names
May 17, 2004Hi
I have developed an application in ASP/SQL server 7.
Ths system is single user. One of the tables is updated by usr actions( say Table A).
To make it multi user. I want to create table such as A_Username.
How can query be written for this. Also there are many stored procedures which will access this table. In these stored procedures i will send username as an input. Then the query in the stored procedure shd access the table as A_Username .
Such dynamic table name refrencing , how can it be done.. Is creating a string for the query and then executing it using sp_exec the only option?
pls suggest
Replicating Data To Tables Of Different Column Names
Apr 17, 2007hi,
is it possible to replicate data from one table to another which have different names,different schemas and different column names.
Im trying this using transactional replication.
i achieved this for different schema, different table names.but it is failing for different column names.
till now i haven't got a proper reply for my problem.
regards
Baji Prasad.B
Transact SQL :: How To Automate Names Of Select Into Tables
Jun 3, 2015I have a number of tables, that I need to copy with data intact, about once a year.
Let's say the table name is 'dbo.MyTable'
I just do a SQL Statement like this:
SELECT * INTO dbo.MyTable'BU06032015 FROM dbo.MyTable
How can I automate this so that it does the Select Into statement and automatically adds the 'BU' + the current date?