Hi 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.
Is there a SELECT statement that will return a the column names of a given table?
View 5 Replies View RelatedI need to check whether procedure found any matches or not. If not it has to return the column name where matching value was not found. For example, if there was no record found in the table "Addresses" column "customer" with the value @username, it should return "street". If id with value @prod_id was not found in the table "Products", the "productname" must be returned as well.
CREATE PROC sp_test
@id INT,
@username VARCHAR(50),
@prod_id INT
AS
SELECT name FROM Customers WHERE id=@id
SELECT street FROM Addresses WHERE customer=@username
SELECT productname FROM Products WHERE id=@prod_id
It is kind of check, which has to find out if users have inserted all the necessary values or not.
Thanks for any advice.
Is 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.
I 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.
If 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.
I 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 Relatedhi,
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
I'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
I am trying a create views that would join 2 tables:
Table 1: Has all the columns need by a view (
Name: Product
Structure: ID, Attribute 1, Attribute 2, Attribute 3, Attribute 4, Attribute 5 etc
Table 2: Is a lookup table that provides the names of columns
Name: lookupTable
Structure: tableName, ColumnName, columnValue
Values: Product, Attribute1, Color
Product, Attribute2, Size
Product, Attribute3, Flavor
Product, Attribute4, Shape
I want to create a view that looks like
ID, Color, Size, Flavor, Shape
Hi
I was wondering if anyone has an idea of how we could find the table names and column names of the tables in our Sql server database at runtime/dynamically given our connection string? Please let me know.
Thanks.
Howdy all,I'm wishing to write a stored proc to return only the field names from a table. What I've tried gets the field names but also returns all of the data in each row. I only want the field names. Is this possible?Thanks!JP
View 7 Replies View RelatedIn a stored procedure returning several tables how can I assign a name to each table.
I wish to select the table in my app using DataSet.Tables("MyTable") rather than DataSet.Tables(0)
Create Procedure myProc As
Select * from table1
Select * from table2
Select * from table3
Go
Hello,
Does anyone know how to return the name of all the databases except the system databases on a server for SQL Server 2005?
Thank you
Hi, I am trying to get all the databases from MS SQL into a dropdownlist. However, I am not sure how to do that in query ... is there a way to retrieve all databases name using a query in MS SQL? Thanks.
View 1 Replies View RelatedHi,
I need to write a stored procedure,that look's up the owner of each database on an server and returns the names of the databases which are not owned by SA.
Can we do this..if so how??
TIA
I 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?
I don't know why this is so difficult. What I want to do is take a table name as a parameter to build a query and get an integer value from the result of the query. But from all of the research I have been doing, Dynamic SQL is bad in SQL server because of SQL Injections. But my users are not going to be supplying the table names.
Things I have learned:
- SQL Functions cannot use Exec to execute query strings.
- SQL Functions can return a concatenated string that could be used by a stored procedure to Exec the query string.
So how can I write a stored procedure that will
1. take a parameter
2. Pass the parameter to a function that will return a string
3. Execute that string as SQL
4. Get a return value from that SQL statement
5. Then finally, from a View, how can I pass a parameter to the stored procedure and get the returned value from the stored procedure to be used as a field in the View?
Numbers 3, 4, and 5 are where I am really stuck. I guess I don't know the proper syntax and limitations of SQL Server.
Firstly 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 Related
I need to create the following table in reporting services
PRODUCT April March Feb
2008 2007 2008 2007 2008 2007
chair 8 9 7 4 4 4
table 3 4 5 6 4 6
My problem is the month names are a column in the dataset, but I dont know how to get it to fill as column headers???
Thanks in advance!!!
I 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 RelatedHi 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
------------
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 ++++++++++++++++++
I'm working on a social network where I store my friend GUIDs in a table with the following structure:user1_guid user2_guidI am trying to write a query to return a single list of all a users' friends in a single column. Depending on who initiates the friendship, a users' guid value can be in either of the two columns. Here is the crazy sql I have come up with to give what I want, but I'm sure there's a better way... Any ideas?SELECT DISTINCT UserIdFROM espace_ProfilePropertyWHERE (UserId IN
(SELECT CAST(REPLACE(CAST(user1_guid AS VarChar(36)) + CAST(user2_guid AS VarChar(36)), @userGuid, '') AS uniqueidentifier) AS UserId FROM espace_UserConnection WHERE (user1_guid = @userGuid) OR
(user2_guid = @userGuid))) AND (UserId IN
(SELECT UserId FROM espace_ProfileProperty))
I 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
We 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.
Hello,
Im trying to get the column names from a database and display them in textboxes. someone has already helped me by tellnig me that i need to use the FillSchema command. Which works just fine and I can see only the colum names in a datagrid when i bind it to that.
The problem is that I do not know how to extract the name of a column and put it in to a textbox ?
does anybody know how I can fo this ?
Thanks a million
Rob
Hi,How do I display the column names from my Sql server table?In asp3 the recordset allowed thisforeach key in rs ColumnName = key.name ColumnValue = key.valuenextHow do I do this in .Net?I want to use a DataReader so I can read through each record and only display the ones I want.TABLE_ONEColumn_OneColumn_TwoColumn_Threethanks,
View 1 Replies View RelatedHi, i need a query to have the columns names !!
If you have another solution, let me know !!
superj !!!
Hi
When I run this query..
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='TableTest'
I get all the column names, but the result is not in the same order as when I check the design for the table, why is that?
At my work, we are upgrading a number of MS Access 2000 databases to SQL Server 2000. Many of the columns have names that do not follow the rules for Identifiers. For example there are columns with a numeric names 1,2,3,4,5,6,7,8,9. There are also columns with a hyphen in the name and columns that begin with a number and not an underscore or an alpha character. Plus there are columns with names like first, last, position, etc. (There was also columns with reserved words used as the names. I have changed those column names in the databases that I have already converted) Will leaving these names alone create a problem? The database serves as the backend to ASP pages.
Miranda
I have a function that returns a table from a comma-delimited string.
I want to take this a step further and create a function that will return a set of tablenames in a table based on a 'group' parameter which is a simple integer...1->9, etc.Obviously, what I am doing is not working out.
CREATE FUNCTION dbo.fnReturnTablesForGroup
(
@whichgroup int
)
RETURNS @RETTAB TABLE (
TABLENAME VARCHAR(50)
[code]....
I'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 Related