SELECT Statement To Return Table Column Names
Is there a SELECT statement that will return a the column names of a given table?
View Complete Forum Thread with Replies
Related Forum Messages:
SELECT Statement - How To Not Get Column Field Names?
I do a SELECT * from table command in an ASP page to build a text fileout on our server, but the export is not to allow a field name rows ofrecords. The first thing I get is a row with all the field names. Whydo these come in if they are not part of the table records? How do Ieliminate this from being produced? Here's the ASP code....<html><head><title>Package Tracking Results - Client Feed</title></head><body><%' define variablesdim oConn ' ADO Connectiondim oRSc ' ADO Recordset - Courier tabledim cSQLstr ' SQL string - Courier tabledim oRSn ' ADO Recordset - NAN tabledim nSQLstr ' SQL string - NAN tabledim objFSO ' FSO Connectiondim objTextFile ' Text File' set and define FSO connection and text file object locationSet objFSO = CreateObject("Scripting.FileSystemObject")'Set objTextFile =objFSO.CreateTextFile(Server.MapPath("textfile.txt"))'Response.Write (Server.MapPath("textfile.txt") & "<br />")Set objTextFile = objFSO.OpenTextFile("C: extfile.txt",2)' write text to text file'objTextFile.WriteLine "This text is in the file ""textfile.txt""!"' SQL strings for Courier and NAN tablescSQLstr = "SELECT * FROM Courier"' set and open ADO connection & oRSc recordsetsset oConn=Server.CreateObject("ADODB.connection")oConn.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" &"c:/Database/QaTracking/QaTracking.mdb" & ";"set oRSc=Server.CreateObject("ADODB.Recordset")oRSc.Open cSQLstr, oConnResponse.ContentType = "text/plain"Dim i, j, tmpIf Not oRSc.EOF ThenFor i = 1 To oRSc.Fields.CountobjTextFile.Write oRSc.Fields(i-1).NameIf i < oRSc.Fields.Count ThenobjTextFile.Write " "End IfNextobjTextFile.WriteLineWhile Not oRSc.EOFFor i = 1 To oRSc.Fields.CountIf oRSc.Fields(i-1) <"" Thentmp = oRSc.Fields(i-1)' If TypeName(tmp) = "String" Then' objTextFile.Write "" &_'Replace(oRSc.Fields(i-1),vbCrLf,"") & ""' ElseobjTextFile.Write oRSc.Fields(i-1)' End IfEnd IfIf i < oRSc.Fields.Count ThenobjTextFile.Write " "End IfNextobjTextFile.WriteLineoRSc.MoveNextWendEnd IfobjTextFile.CloseSet objTextFile = NothingSet objFSO = NothingoRSc.CloseSet oRSc = NothingoConn.CloseSet oConn = Nothing%></body></html>
View Replies !
Dynamic Column Names In Select Statement
I have a quick question on SQL Server. Lets say I have table Order which has column names OrderId, CustomerName, OrderDate and NumberofItems. To select the OrderID values from the table I say Select OrderId from Order. But in the select if I want the column name to be variable how do I do it. I tried the following code through a stored procedure. declare @order_id nvarchar(10) select @order_id = 'OrderID' SELECT @order_id from Order. The code above gave me the string "OrderID" as many times as there were rows in the table but I could never get the actuall values in the OrderId column. Can you please send me some ideas or code where I can get values from the column names and at the same time change the column name dynamically.
View Replies !
Return SP In A SELECT Statement Column
I have a stored procedure which contains a complex scripting that is not an option to rewrite as a single SELECT statement. I want the following output: CatID | CatTitle | CatTree 001 | News | exec sp_DisplayTree(@CatID) My code I tried doesn't work: SELECT C.CatID As CatID, C.CatTitle As CatTitle, CatTree = (exec sp_DisplayTree C.CatID) FROM Cats As C WITH (nolock) I cannot find a solution to my solution, please help...
View Replies !
Return Tables That Have Certain Column Names
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.
View Replies !
How Do I Get The Actual Name Of A Column Or Table In A Sql Select Statement?
Hello fellow .net developers, In a website I'm working on I need to be able to put all of the user tables in a database in a dropdownlist. Another dropdownlist then will autopopulate itself with the names of all the columns from the table selected in the first dropdownlist. So, what I need to know is: is there a sql statement that can return this type of information? Example: Table Names in Database: Customers, Suppliers Columns in Customers Table: Name, Phone, Email, Address I click on the word "Customers" in the first dropdownlist. I then see the words "Name", "Phone", "Email", "Address" in the second dropdownlist. I'm sure you all know this (but I'll say it anyways): I could hardcode this stuff in my code behind file, but that would be really annoying and if the table structure changes I would have to revise my code on the webpage. So any ideas on how to do this the right way would be really cool. Thanks in advance, Robert
View Replies !
Return Column Names Where Values Were Not Found [stored Procedure]
I 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.
View Replies !
Random Selection From Table Variable In Subquery As A Column In Select Statement
Consider the below code: I am trying to find a way so that my select statement (which will actually be used to insert records) can randomly place values in the Source and Type columns that it selects from a list which in this case is records in a table variable. I dont really want to perform the insert inside a loop since the production version will work with millions of records. Anyone have any suggestions of how to change the subqueries that constitute these columns so that they are randomized? SET NOCOUNT ON Declare @RandomRecordCount as int, @Counter as int Select @RandomRecordCount = 1000 Declare @Type table (Name nvarchar(200) NOT NULL) Declare @Source table (Name nvarchar(200) NOT NULL) Declare @Users table (Name nvarchar(200) NOT NULL) Declare @NumericBase table (Number int not null) Set @Counter = 0 while @Counter < @RandomRecordCount begin Insert into @NumericBase(Number)Values(@Counter) set @Counter = @Counter + 1 end Insert into @Type(Name) Select 'Type: Buick' UNION ALL Select 'Type: Cadillac' UNION ALL Select 'Type: Chevrolet' UNION ALL Select 'Type: GMC' Insert into @Source(Name) Select 'Source: Japan' UNION ALL Select 'Source: China' UNION ALL Select 'Source: Spain' UNION ALL Select 'Source: India' UNION ALL Select 'Source: USA' Insert into @Users(Name) Select 'keith' UNION ALL Select 'kevin' UNION ALL Select 'chris' UNION ALL Select 'chad' UNION ALL Select 'brian' select 1 ProviderId, -- static value '' Identifier, '' ClassificationCode, (select TOP 1 Name from @Source order by newid()) Source, (select TOP 1 Name from @Type order by newid()) Type from @NumericBase SET NOCOUNT OFF
View Replies !
Return Table Names - How?
In 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
View Replies !
Using Variables To Select Column Names
Hi I've tried declaring and setting variables in my sql statement and then trying to use them instead of defining a column directly - sorry quite hard to explain, i'll do a simple example eg DECLARE @column DECLARE @value SET @column = 'col1' SET @value = 'bloggs' Select * FROM table1 WHERE @column = @value It keeps returning no results even though i've tried Select * FROM table1 WHERE col1 = 'bloggs' -- which returns results I realise its the column which is not being selected, but there must be a way by using a variable? thanks
View Replies !
Table Column Names = Dataset Column Values?!
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!!!
View Replies !
Return Select Statement Or Values Using SqlDataSource?
Hello all, I have been working with a DetailsView control for the past week and it is a great control, but also lacks on some departments. Anyhow I need to know what the best approach for this scenerio would be? I have a SqlDataSource" <asp:SqlDataSource ID="SqlUpsertAffiliateDetails" runat="server" ConnectionString="<%$ ConnectionStrings:connectionstring %>" SelectCommand="SELECT am.affiliate_id AS AffiliateId, am.member_id AS MemberId, m.First_Name, m.Last_Name, am.category_id AS CategoryId, ac.category_name, am.profile_web_address AS WebAddress, am.profile_email_1 AS Email, am.comments AS Comments, am.date_modified FROM tAffiliateMaster AS am WITH (NOLOCK) INNER JOIN tAffiliateCategories AS ac WITH (NOLOCK) ON am.category_id = ac.category_id INNER JOIN rapdata..Member AS m WITH (NOLOCK) ON am.member_id = m.Member_Number WHERE (am.affiliate_id = @AffiliateId)" UpdateCommand="spUpsertAffiliateProfile" UpdateCommandType="StoredProcedure"> <SelectParameters> <asp:QueryStringParameter Name="AffiliateId" QueryStringField="affiliate_id" /> </SelectParameters> <UpdateParameters> <asp:Parameter Name="Action" Type="Byte" DefaultValue="2" /> </UpdateParameters> </asp:SqlDataSource> And my SP:/* 09-19-07 Used to update affiliate profile */ CREATE PROCEDURE spUpsertAffiliateProfile @Action tinyint, @AffiliateId int, @MemberId int = -1, @CategoryId int, @WebAddress varchar(50), @Email varchar(50), @Comments varchar(1500) AS SET NOCOUNT ON -- Find errors first, check is not needed if deleting IF @Action <> 3 IF NOT EXISTS (SELECT Member_Number FROM rapdata..Member_Association WHERE Member_Number = @MemberId AND Status = 'A' AND Association_ID = 'TRI' AND Bill_Type_Code LIKE '%AF%') BEGIN SELECT retval = 'A qualified member ID was NOT found. Action Failed.', errorcount = 1, 0 AS affiliate_id RETURN END IF @Action = 1 IF EXISTS (SELECT member_id FROM tAffiliateMaster WHERE member_id = @MemberId) BEGIN SELECT retval = 'This member has already been listed. Action Failed.', errorcount = 1, 0 AS affiliate_id RETURN END IF @Action = 1 AND @AffiliateId = 0-- insert BEGIN INSERT INTO tAffiliateMaster (member_id, category_id, profile_web_address, profile_email_1, comments) VALUES (@MemberId, @CategoryId, @WebAddress, @Email, @Comments) SELECT retval = 'Record Entered', errorcount = 0, @@IDENTITY AS affiliate_id RETURN END ELSE IF @Action = 2 AND @AffiliateId > 0-- update BEGIN UPDATE tAffiliateMaster SET category_id= @CategoryId, profile_web_address=@WebAddress, profile_email_1=@Email, comments=@Comments WHERE affiliate_id = @AffiliateId AND member_id = @MemberId SELECT retval = 'Record Updated', errorcount = 0, @AffiliateId AS affiliate_id RETURN END ELSE IF @Action = 3 AND @AffiliateId > 0-- delete BEGIN DELETE tAffiliateMaster WHERE affiliate_id = @AffiliateId SELECT retval = 'Record Deleted', errorcount = 0, 0 AS affiliate_id RETURN END GO My question is how will I be able to return the retval? Will I need to do it within the code behind of the SqlDataSource Updated Event? Thanks!
View Replies !
Return Variable Name As Part Of Select Statement.
hey all, I have the following query: ALTER PROCEDURE [dbo].[sp_SelectMostRecentArticle] AS BEGIN DECLARE @article_id INT SELECT @article_id = ( SELECT TOP 1 article_id FROM article ORDER BY article_id DESC ) DECLARE @comment_count INT SELECT @comment_count = ( SELECT COUNT(comment_id) FROM comment JOIN article ON article_id = comment_article_id GROUP BY article_id HAVING article_id = @article_id ) SELECT TOP 1 article_id, article_author_id, article_title, article_body, article_post_date, article_edit_date, article_status, article_author_id article_author_ip, author_display_name, category_id, category_name--, comment_count AS @comment_count FROM article JOIN author ON author_id = article_author_id JOIN category ON category_id = article_category_id GROUP BY article_id, article_title, article_body, article_post_date, article_edit_date, article_status, article_author_ip,article_author_id, author_display_name, category_id, category_name HAVING article_id = @article_id END GO as you can see, im trying to return a comment_count value, but the only way I can do this is by defining the variable. I have had to do it this way, because I cannot say COUNT(comment.comment_id) AS comment_count or it returns an error that it cant reference the comment.comment_id. But when change it to FROM article, comment; I get errors about the article_author_id and article_comment_id. And i cant add a join, because it would return the amount of rows of the comment... unless someone could help with what i Just decribed (as i would prefer to do it this way), how would i return the variable value as part of the select statement? Cheers
View Replies !
Overloading Select Statement Return Values
Hey guys, This is what I think and hope is a fairly straight forward SQL question Essentially, I have a table which has the following columns that are relevent to my question: PROJID ACTIVITY_NAME COMPLETION_DATE Rows in this table are, for example: PROJID ACTIVITY_NAME COMPLETION_DATE 1 Prepro 10/12/2007 3:42:30 2 Prepro 10/13/2007 9:16:27 2 QA 10/13/2007 10:00:01 2 Delivery 10/14/2007 09:31:12 etc. So, really the key is the PROJID & the ACTIVITY_NAME (really, there's a unique column ID, but for this question, I'll leave it at that). (Though this should be much easier to accomplish in code, the system is not built that way so) Is there a good way that I could return a status for a given PROJID based on whether a row exists for a given PROJID). In other words, ultimately, I would like to return something like this: PROJID LAST_ACTIVITY ---------------------------------------------- 1 Prepro 2 Delivery where the activity order (in this case) is Prepro, QA, Delivery. So because a Delivery row exists for PROJID 2, then the LAST_ACTIVITY would return "Delivery" and because only Prepro exists for PROJID 1, the LAST_ACTIVITY returned would be Prepro I really appreciate the help Thanks, Steve
View Replies !
Selecting Table Names In A SQL Statement
Hey all, I trying to figure out if you can get a list of table names from a particular database by writing a single SQL statement. E.G. In Oracle, from SQLPlus - you can say: Select table_name from user_tables; Is there anything like that SQL that I could run from Query Analyser to get a list of table names from a particular SQL Server database? Thanks in advance, Tommy
View Replies !
Return NULL Values In SELECT Statement With INNER JOIN ?
If I try to run the code below, and even one of the values in the INNER JOIN statements is NULL, the DataReader ends up with zero rows. What I need is to see the results even if one or more of INNER JOIN statements has a NULL value. For example, if I want info on asset# 2104, and there's no value in the DriverID field, I need the rest of the data to display and just have the lblDriverName by blank. Is that possible? <code> Sub BindSearchGrid() Dim searchUnitID As String Dim searchQuery As String searchUnitID = tbSearchUnitID.Text lblIDNum.Text = searchUnitID searchQuery = "SELECT * FROM Assets " & _ "INNER JOIN Condition ON Condition.ConditionID = Assets.ConditionID " & _ "INNER JOIN Drivers ON Drivers.DriverID = Assets.DriverID " & _ "INNER JOIN Departments ON Departments.DepartmentID = Assets.DepartmentID " & _ "INNER JOIN AssetCategories ON AssetCategories.AssetCategoryID = Assets.AssetCategoryID " & _ "INNER JOIN Store ON Store.[Store ID] = Assets.StoreID WHERE RTRIM(Assets.[Unit ID]) = '" & searchUnitID & "'" Dim myReader As SqlDataReader myReader = Data.queryDB(searchQuery) While myReader.Read If Not IsDBNull(myReader("Store Name")) Then lblStrID.Text = myReader("Store Name") If Not IsDBNull(myReader("AssetCategory")) Then lblAsstCat.Text = myReader("AssetCategory") If Not IsDBNull(myReader("Condition Description")) Then lblCondID.Text = myReader("Condition Description") If Not IsDBNull(myReader("DepartmentName")) Then lblDepID.Text = myReader("DepartmentName") If Not IsDBNull(myReader("Unit ID")) Then lblUnID.Text = myReader("Unit ID") If Not IsDBNull(myReader("Year")) Then lblYr.Text = myReader("Year") If Not IsDBNull(myReader("Make")) Then lblMk.Text = myReader("Make") If Not IsDBNull(myReader("Model")) Then lblMod.Text = myReader("Model") If Not IsDBNull(myReader("Mileage")) Then lblMile.Text = myReader("Mileage") If Not IsDBNull(myReader("Vin Number")) Then lblVinNum.Text = myReader("Vin Number") If Not IsDBNull(myReader("License Number")) Then lblLicNum.Text = myReader("License Number") If Not IsDBNull(myReader("Name")) Then lblDriverName.Text = myReader("Name") If Not IsDBNull(myReader("DateAcquired")) Then lblDateAcq.Text = myReader("DateAcquired") If Not IsDBNull(myReader("DateSold")) Then lblDtSld.Text = myReader("DateSold") If Not IsDBNull(myReader("PurchasePrice")) Then lblPrPrice.Text = myReader("PurchasePrice") If Not IsDBNull(myReader("NextSchedMaint")) Then lblNSM.Text = myReader("NextSchedMaint") If Not IsDBNull(myReader("GVWR")) Then lblGrVWR.Text = myReader("GVWR") If Not IsDBNull(myReader("GVW")) Then lblGrVW.Text = myReader("GVW") If Not IsDBNull(myReader("Crane Capacity")) Then lblCrCap.Text = myReader("Crane Capacity") If Not IsDBNull(myReader("Crane Certification")) Then lblCrCert.Text = myReader("Crane Certification") If Not IsDBNull(myReader("Repair Cost")) Then lblRepCost.Text = myReader("Repair Cost") If Not IsDBNull(myReader("Estimate Replacement")) Then lblEstRep.Text = myReader("Estimate Replacement") If Not IsDBNull(myReader("SalvageValue")) Then lblSalVal.Text = myReader("SalvageValue") If Not IsDBNull(myReader("CurrentValue")) Then lblCurVal.Text = myReader("CurrentValue") If Not IsDBNull(myReader("Comments")) Then lblCom.Text = myReader("Comments") If Not IsDBNull(myReader("Description")) Then lblDesc.Text = myReader("Description") End While End Sub</code>
View Replies !
Select Statement To Return Most Current Syscreated Date On Duplicates
I have a db that has duplicate customer records. If I run the following select statment against the appropriate table it returns the duplilcate records in the result set. However, from this list I want to add an additional select statement embedded into the query that will actually return only those records with the most current syscreated date. Example of script I'm using--- select cmp_fadd1, syscreated, cmp_name, cmp_code from cicmpy where cmp_fadd1 in (select cmp_fadd1 from cicmpy group by cmp_fadd1 having count(1) = 2) order by cmp_fadd1,syscreated desc The results is: Address Syscreated date Customer 1622 ONTARIO AVENUE 2005-06-15 22:19:45.000 RELIABLE PARTSLTD 1622 ONTARIO AVENUE 2004-01-22 18:10:05.000 RELIABLE PARTS LTD PEI CENTER 2006-01-05 22:03:50.000 P.G. ENERGY PEI CENTER 2004-01-22 17:57:56.000 P.G. ENERGY From this I want to be able to select ONLY those records with the most current syscreated date or those records with 2005-06-15 and 2006-01-05 Can anyone help me with creating this? Thanks Cyndie
View Replies !
Select Table Names
here a quick rundown of what i want to do: database database1 database hold all my good information. database 2 is the copy for breaking. Im too a point that i need to get my 2nd DB to by like the first. I want to truncate all the tables in database2, and then export, or select from insert into. I just need the data, not triggers or anything like that. So: How can i find the name of my tables (not views) in that database For each (select table_name from system where database="database") copy database.table_name database1.table_name; i hope that makes sense. I just need to restore my devel DB back to the original so i can mess it up again, Thanks
View Replies !
Know Ing The Table Names When I Know Column Name
Hi all, By using below query i can get no of tables having the give column name in a particular database, SELECT COUNT(*) AS CounterFROM syscolumnsWHERE (name = 'empno') but i want to know the table names too? any one please suggest me how to find table names too...... In other words i know the particular column name and right now i want to know the table names in which this column name exists.
View Replies !
Get Column Names From Table
Hi All, I was wondering how would I get the column names from a table? Not the results just a listing of column names. What is the command to get this information? I am using MS Access, but I posted here because MS SQL is the closest thing on this forum and the syntax is usually similar. Thanks a bunch. Val
View Replies !
SELECT Table Field Names
I need a statement or sp that will display, for a given user database, an individual table's fieldnames, datatype, and length. Any help is appreciated. Randy
View Replies !
How To Select Field Names Of Table?
Can someone write a query that select all the fields of tables in database that have type 'image'? Something like this: Select TableName, FieldName FROM TableWhereTheyKeepThoseThings WHERE TableWhereTheyKeepThoseThings.FieldType='Image' ... olny it should work :)
View Replies !
Retrieving All Column Names From A Table
Is there a stored proc or systable that holds the actual names of the columns of a specific table? I know there are for foreign keys but I need the varchar names of every column of a table. Thanks in advance. Regards, Matthew Nye
View Replies !
Prepending Column Names By Table
I wasn't sure whether or not to post this in the .NET or MsSQL forum, mods please move accordingly. Say I have two tables: Store, and Area that have the following columns: Area: AreaID, AreaName, Description Store: StoreID, AreaID, StoreName, Description When I run the following query : SELECT * FROM Area, Store WHERE Area.AreaID = Store.AreaID, the following columns come back from the .NET SQLDataReader AreaID, AreaName, Description, StoreID, AreaID, StoreName, Description Even though the Description fields are different there is no differentiation between which one is which (other than an integer index) Is there a way to form the SQL query so the columns come back Area.AreaID, Area.AreaName, Area.Description etc... I was hoping to do something like the following Select Area.* as 'Area.'.* ... but that wasn't liked by the SQL server, any ideas? Note: The actual tables are more complex than this, and the table names aren't static. Individually aliasing columns won't work, nor will checking the alias. -MBirchmeier
View Replies !
Sort COLUMN NAMES Of A Table
Hi all, Is there a way i can write a script that will sort the column names of my table? Example: Table 1 columns: First_Name Last_Name Address City Country I want to write a script that will sort them like this: Address City Country First_Name Last_Name
View Replies !
Extracting Sql Table Column Names
I am using the following to extract the column names of a table. I would like to do this for the whole database. Currently I am cutting the results into an excel spread. Is there a better way of doing this? Here is the query SELECT name FROM syscolumns WHERE [id] = OBJECT_ID('tablename')
View Replies !
Use Column Names And Not Positions When Inserting From One Table Into Another
/*Code below raises following errors:Server: Msg 245, Level 16, State 1, Line 6Syntax error converting the varchar value 'a' to a column of data typeint.*/create table #x (i integer, c char(1))create table #y (c char(1), i integer)insert into #x VALUES (1, 'a')insert into #y SELECT * from #xdrop table #xdrop table #y--CODE COMPLETEIs there a way to use the column names as the basis for the insert asopposed to column position? The key here is that I DO NOT want toSPECIFY COLUMN NAMES (i.e. 'insert into #y (i,c) SELECT * from #x')Regards,Ty
View Replies !
Export Table To Txt File From DTS With Different Column Names
Hi. I have SQL Server 2000. I am trying to automate a process that runs a pivot table then exports the results to a text file. Because it's a pivot table it could have different column names every time it runs. Does anyone know of a way to do the export in a DTS package? When I do a straight export it always wants the column names to remain the same as when it was first set up. Thanks.
View Replies !
Extracting Column Names From The Sysprotects Table
Hi, I'm working on a C++ project(MSSQL 6.5) to dynamically create permissions on tables based on previously existing permissions. Right now I'm querying via cursors in C++ and reading the responses. I need to get the column names that are stored with the permissions in the sysprotects table. These names are stored as varbinary. Does anyone know any tricks to extract the column names from the binary data either on the tSQl side or in C++? Thanks!
View Replies !
How To Determine Table And Column Names In A Trigger?
Hi all, I'm new to this whole trigger 'thing', so forgive me if this question has been asked and answered a few times already. I'm in the process of writing a trigger that will send an e-mail to an application admin when any table within a given database is altered (IE, a column is added or deleted). I can get the e-mail to fire off when that happens without any issue, but I'd like to be able to let the admin know which table was tweaked and what new column was added. Is this a relatively easy thing to do and I'm just not finding the right built-in variable name, or does something more need to be done? Thanks, -Mike
View Replies !
Variables As Table Or Column Names In A Stored Procedure
I would like to use variables to set the table name and some column names of a SQL Query in a stored procedure (the variable values will come from a webpage)... something like this:ALTER PROCEDURE dbo.usp_SelectWorkHours @DayName varchar,@DayIDName varchar AS BEGINSELECT @DayName.InTime1, @DayName.OutTime1, @DayName.InTime2, @DayName.OutTime2 FROM @DayName INNER JOINWorkHours ON @DayName.@DayIDName = @DayName.@DayIDName INNER JOINEmployees ON WorkHours.WorkHoursID = Employees.WorkHoursID END RETURN ...is this possible?? if so how? Thanks
View Replies !
SQL Question: Change Column Names Of Temp Table In SP
Hi, I have a SP which returns a select query on a temp table so I get to choose column names when I create the #table. Can I determine column names myself based on the results of another query (in the SP) before, or (preferably) after I create the #table and populate it. My query is used to bind to a datagrid so I could use.... dataGrid.Columns[index].HeaderText and set it to a particular output parameter, but I want to keep the code in the SQL. Cheers
View Replies !
Exporting Sql Table Into Csv Format Retaining The Column Names
HiI have been working since 2 days to device a method to export sql tableinto csv format. I have tried using bcp with format option to keep thecolumn names but I'm unable to transfer the file with column names. andalso I'm having problems on columns having decimal data.Can any one suggest me how to automate data transfer(by using SP) andretaining column names.ThanksNoor
View Replies !
Ambiguous Column Names In Multi-Table Join
Hi all,A (possibly dumb) question, but I've had no luck finding a definitiveanswer to it. Suppose I have two tables, Employees and Employers, whichboth have a column named "Id":Employees-Id-FirstName-LastName-SSNetc.Employers-Id-Name-Addressetc.and now I perform the following join:SELECT Employees.*, Employers.*FROM Employees LEFT JOIN Employers ON (Employees.Id=Employers.Id)The result-set will contain two "Id" columns, so SQL Server willdisambiguate them; one column will still be called "Id", while theother will be called "Id1." My question is, how are you supposed toknow which "Id" column belongs to which table? My intuition tells me,and limited testing seems to indicate, that it depends on the order inwhich the table names show up in the query, so that in the aboveexample, "Id" would refer to Employees.Id, while "Id1" would refer toEmployers.Id. Is this order guaranteed?Also, why does SQL Server use such a IMO brain-damaged technique tohandle column name conflicts? In MS Access, it's much morestraightforward; after executing the above query, you can use"Employees.Id" and "Employers.Id" (and more generally,"TableNameOrTableAlias.ColumnName") to refer to the specific "Id"column you want, instead of "Id" and "Id1" -- the"just-tack-on-a-number" strategy is slightly annoying when dealing withcomplex queries.--Mike S
View Replies !
Exporting A Table With Changing Column Names To An Excel File
I'm trying to write an SSIS package that exports a table that has changing column names to an excel file. The column names change due to the fact that the table is created by a pivot daily. the only thing I'm missing is the ability to dynamically map the tables' columns to the excel destination. Is this possible? I read in another thread that "It is not possible to create packages or new objects within packages using SSIS." I also read in the books online that "The input and the input columns of the Excel destination have no custom properties." To me this means that I cannot programmatically create or remove columns in the excel destination. Please tell me I'm wrong. So, to summarize my research so far. In writing an SSIS package, I cannot programmatically create a new excel destination object and I can't manipulate an existing one. I hope I'm wrong. Can anyone help me? (and please correct any wrong assumptions I may have stated)
View Replies !
Return The Results Of A Select Query In A Column Of Another Select Query.
Not sure if this is possible, but maybe. I have a table that contains a bunch of logs. I'm doing something like SELECT * FROM LOGS. The primary key in this table is LogID. I have another table that contains error messages. Each LogID could have multiple error messages associated with it. To get the error messages. When I perform my first select query listed above, I would like one of the columns to be populated with ALL the error messages for that particular LogID (SELECT * FROM ERRORS WHERE LogID = MyLogID). Any thoughts as to how I could accomplish such a daring feat?
View Replies !
|
TRACKER
