Table Names And Field Names
I'm trying to do an update query that looks like this:
UPDATE
PAEMPLOYEE
SET PAEMPLOYEE.LOCAT_CODE = EMPLOYEE.PROCESS_LEVEL
FROM
PAEMPLOYEE A
JOIN EMPLOYEE B ON A.EMPLOYEE = B.EMPLOYEE
It's erroring out on the Employee prefix B.EMPLOYEE saying:
..."does not match with a table name or alias name used in the query"
Is it wrong or will it cause problems to have a field name the same as the table name?
View Complete Forum Thread with Replies
Related Forum Messages:
SQL For Field Names From A Table
Hi,We have a database with some tables with (what I woulddenote as) 'referred field names'.Like this:DataTable1 with fields F1, F2, F3DataTable2 with fields F3, F4, F5DataTable3 with fields F1, F5, F2We also have a table with field namesFieldNameTable with fields FIELD, NAMEcontaining data like:FIELD NAME----------------F1 FieldName1F2 FieldName2F3 FieldName3F4 FieldName4F5 FieldName5Now, we need a way to query the data of these tables, butthe result of the query should show the 'referred field names'from the FieldNameTable.For example, querying DataTable3 should produce the outputFieldName1 FieldName5 FieldName2------------------------------------------... ... ...... ... ...Any idea how (and whether) this can be done with an SQL query?Thanks in advance for tips & tricks.Dirk Vdm
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 !
How To Write A Script To Create A Table (With Unfixed Field Names)?
Hello All. You may find below script stupid to you. I am trying to create a table with week number that is not fixed, i.e. March 2006 may have 4 weeks and April 5 weeks based on our company calendar. And Field Names could be Grp, SubGrp, Week_10, Week_11, Week_12, Week_13 depending on the week being stored in TempWeekFile. We could start the month having only Week 10 and following week with Week 11 added into TempWeekFile. My below script shows error. I have following records in my TempWeekFile Week_No WeekCnt 10 1 11 2 12 3 13 4 How to write a script that do the above? Has anyone done this using a better method? Please advise. Thank you. --------------------------------------------------------- if exists (select * from information_schema.tables where table_name='WeeklySalesToThird_Month') drop table WeeklySalesToThird_Month Declare @WeekCount int Declare @Cnt int Declare @WeekNo varchar(2) Select @WeekCount = count(*) from TempWeekFile Select @WeekCount Select @Cnt=1 CREATE TABLE [dbo].[WeeklySalesToThird_Work_Month] ( [Grp] [varchar] (30) NULL , [SubGrp] [varchar] (30) NULL , while @Cnt<=@WeekCount Begin select @WeekNo = Week_No from TempWeekFile where WeekCnt=@Cnt [Week_@WeekNo] [Money] NULL @Cnt=@Cnt+1 End ) ON [PRIMARY] GO --------------------------------------------------------
View Replies !
Are Duplicate Instance Names A Problem If Physical Server Names Are Different?
If I restore the master database from ServerA to ServerB and start the SQL Server services on ServerA and ServerB, the master databases will be identical and running sp_helpserver and @@ServerName will indicate the SQL Server instance for both instances is ServerA. Is this ok considering the physical server names are different? If this is ok, how does SQL Server advertise on the network who it is? Does it append the physical server name to the SQL Server instance name during network communications? Thanks, Dave
View Replies !
DB Mirroring: Different Server Names With Same Instance Names
I'm going to be setting up DB mirroring between two SQL Server 2005 boxes. Since these are on two different servers, can the instance names be the same? Is there any reason NOT to do so if the mirror server is going to be used exclusively for DB mirroring? For example: if the my primary DB is located on SERVER1INSTANCE1, can the mirror be SERVER2INSTANCE1 or do the instance names have to be different even though they're on different boxes. Thanks!
View Replies !
Server Names Or Instance Names
Hello there. I'm trying to populate a drop down box with a list of all available instances of SQL server (express or not) available on a network. To list all instances I'm using EnumAvailableServers() in SmoApplication. The issue is that the Instance column in the returned data table is always empty, and both the Name and the Server columns contain the same string -- the name of the computer the SQL server is installed. Locally I have SSE installed, the instance name is <computer_name>SQLEXPRESS, however, using Smo I can't seem to get to the either full instance name or just the instance name. I can't assume SQLEXPRESS, since there may be more than one installed. Alternately I used EnumRegisteredServers() in SmoApplication.SqlServerRegistrations and that, while returning instance information (can be retrieved from the RegisteredServer in the returned collection), doesn't always return the remote SQL servers in the list. Any idea how can this be done? Maybe I'm going at it the wrong way, maybe there's another class / method I need to use to retrieve all instances? Thank you
View Replies !
Return Field Names
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 Replies !
Field Names From SQL Statement
Is there anyway to determine what the resulting Field Names are going to be from a SQL Statement? For example: SELECT TABLE1.FIELD1, TABLE1.FIELD2, TABLE1.FIELD3, TABLE2.FIELD1 AS ANOTHERNAME FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.PK = TABLE2.FK resulting field names: FIELD1 FIELD2 FIELD3 ANOTHERNAME Seems easy enough splitting all values before "FROM" by comma and doing some manipulation to remove table names and anything before the word "AS". However, it gets more difficult when you have complex CASE statements embedded in you query that may also contain commas. Just a shot in the dark because I don't know if anyone has already done something like this before. Thank you in advance, Jeff
View Replies !
Changing Field Names
This is a followup to my last post. If a field name is changed in the database, what is the easiest way to determine what stored procedures and triggers that reference that field are now broken?
View Replies !
Show Field Names/Schema
I can't seem to find a sample code, either here or on the net - - so I'll go ahead and ask... What I'm looking for is a sample of how to query a database, so that I can populate a listbox with the table names from a database - - and then, populate another listobx with the field names from the database, in order to build a user-driven sql statement builder.... either of the above (at least the field name part) - either a code sample, or a link, will be greatly appreciated) - - Thanks
View Replies !
Crosstab Qry With Dynamic Field Names
Hi,I am trying to create a stored procedures (SQL 7.0), to provide dataina crosstab format.(I'm using Crystal Reports 8.5, but the Crosstab capabilities areterrible, so I have to do as much as possible on the SQL side)I have a table [Occurrences] with the following fields:Year (int)Month (int)Occurs (int)Claims (int)I need a query to give me the following format:Acct_Month 2001 2002 2003Occurs Claims Occurs Claims Occurs ClaimsJanuary 120 180 132 196 110 140February 154 210 165 202 144 178March etc.......Catch! I need the Year field name to be the contents of the fieldYear in the Table (2001, 2002, 2003...). Not the usual Year_1, Year_2approach.I got the month name ok...Acct_Month = DATENAME(month, Convert(Varchar(2), Month) + '/01/'+Convert(Char(4),Year))Is it possible to do this easely, without the use of cursors?Any help would be much appreciated.Luis Pinto
View Replies !
Finding Out Field Names By Lineage ID
Hi, is there any "robust" way to find out the name of a field in the pipeline by it's Lineage ID programatically? There is a sample code out there (on one of the blogs) but it seams not to be reliable... The usecase is easy... What is the field name in an error output of that column that causes the error? We don't want to have hardcoded LineageIDs in the error handling so I think it's the best idea to go with field names... However we only get that LineageID... Thanks,
View Replies !
Scrambled Field Names In Views
While creating a view in SQl Server 2005 Management Studio Or Interation Services on a SQL Server 2000 database using "*" something very strange happens. Has anyone every seen this happen before? The new view is created something like the following: CREATE VIEW [dbo].[new_view] AS SELECT * FROM Table1 GO However, when right-clicking on the new view, and choosing "Design", the SELECT scrambles the field names with aliases of the other field names. When the view is run, the result set is incorrect. It may look something like the following: SELECT Field1, Field2 AS Field3, Field3 AS Field4, Field4 AS Field5, . . . From Table1 GO
View Replies !
Two Different Datasets With Same Field Names Used In Same Report
I am having two datasets in the same report, the column names are the same, in both the datasets, Now How can I use the individual fields in same report. If I use (Fields!xyz.Value, "dsDataSet1") and (Fields!xyz.Value, "dsDataSet2") It is giving me syntax error. How can I use both these fieds, I do not want any aggregates
View Replies !
Trying To Display Alias Field Names
Hello All, I have the following code: USE MLS select sc.name,f.field#,fdesc,flong from sysobjects so join syscolumns sc on so.id = sc.id join fld f on f.field# = replace(sc.name,'_','') where so.name = 'dbo.tbl_MLS_Leads_Trans' I am trying to get the description which is flong and I get the following error message: Msg 208, Level 16, State 1, Line 2 Invalid object name 'fld'. What am I doing wrong? TIA Kurt
View Replies !
Finding Unique Field Names With Different Values
I am in a situation where I need to find out unique field names with different values in a table having 200+ columns. Let's say I have two rows with 200+ columns ( I exported these rows from Lotus Notes to SQL Server) I am not sure what columns makes unique of these rows. It's going to be tedious by checking each column values. Is there anyway I can write a squl query on these two rows which outputs column names which are having unique values. I would appreciate If anybody gives me hint about achieving desired result
View Replies !
A Strange Problem With SQL Query Fro Getting Field Names
Hello All, I have been trying to get this code work, but I could not. Every thing seems going well. However, The result of running the sql query is strange. It shows the field names twice. Eg:) if you have a table called "newtable" that has two fields[Custnumber, Custname], you will get somthing like this [Custnumber, Custname Custnumber, Custname]. I have tried many times, but I couldn't fix it. Sub Page_Load(sender As Object, e As EventArgs) handles Mybase.Load if not page.Ispostback then try Sqlconnection = New Sqlconnection (connectionString) querystring = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNs WHERE TABLE_NAME = 'Newtable'" SqlCommand = New SqlCommand(queryString, Sqlconnection) SqlConnection.Open dataReader = SqlCommand.ExecuteReader(CommandBehavior.CloseConnection) while dataReader.Read() Tablefields_txt.text += dataReader.Getstring(0) & ", " End while catch ex as Exception msgbox("An error has occured: " + ex.Message,0, "Error Message") finally SqlConnection.Close() End try End if Any help , please
View Replies !
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 !
Best Approach To Sending Field Names Dynamically
Hi,I have a C# web app that searches my database table using thefollowing search parametersSearch string, criteria (< =) and the field you want to perform yoursearch on. My understanding is that stored procedure is the way to go.What's the best way of doing this using stored procedures. Can Idefine a placeholder for the field name?Ex.SELECT field1, field2... FROM Table WHERE field1='value1' where field1and value1 are both sent from code.If it's not possible then what is the best way to approach thisproblem? I see so many searches like that on the internet. I can onlydo them with inline SQL and not stored procedure.Thank youMaz.
View Replies !
Query Returning Duplicate Field Names
I have a .NET program that can connect to either an Access 97 database or anSQL Server 7 database. In the database I have two tables which have a fieldcalled ID. When I run a query like "SELECT A.*, B.* FROM A, B", the queryreturns those fields as "A.ID" and "B.ID" when connected to Access 97, butas "ID" and "ID" in SQL Server 7. Is there anyway to get SQL Server toprepend the table name to the field name in a case like this and not returnduplicate field names like that without having to specify aliases for thefields?- Don
View Replies !
Can You Pass Field Names In Stored Procs?
CREATE PROCEDURE [dbo].[removeContact] @RemoveType VARCHAR (50), @ListID INT, @TargetField VARCHAR (50), @TargetValue VARCHAR (150), @RowsAffected INT OUTPUT AS UPDATE [tblContacts] SET @RemoveType = 'yes' WHERE [list_id]=@ListID AND @TargetField = @TargetValue SELECT @RowsAffected = @@ROWCOUNT GO declare @P1 int set @P1=0 exec removeContact '[unsubscribe]', 6, '[email]', 'email@domain.com', @P1 output select @P1 I've tried this with and without the square brackets. Either way, nothing gets updated. Am I barking up the wrong tree?
View Replies !
Changing Field Names Is SQL Server 2000
Why is it that when you change a field name is SQL Server it sometimes completely messes things up. I renamed a field in one of my tables from Emp# to EmpNumber. I had a view based on this table and naturally I knew I would have to change a view I had based on the table. I opened the view and changed the field from Emp# to EmpNumber but when I tried to open the view I got an error “Invalid column Name EMP#”. I have not been able to fix this. I have dropped and recreated the view, refreshed all the objects using enterprise manager, refreshed all the objects using Query Analyzer, shut down and restarted my computer, taken my database offline and put it back on line. The field name EMP# is not in any tables in my database and not referenced any views or procs . I am just starting on this database so I could verify this very easily because I only have a few views and procs. Has anyone had this problem or more importantly does anyone know why this is happening or how to fix it?
View Replies !
.dbf File Import (duplicate Field Names)
I am importing a file creating by an application which exports the file into .dbf format. Very unfortunately, this .dbf file can have fields with IDENTICAL column_names. Utilizing ActiveX, I create an ado connection to the .dbf file using a visual foxpro drver. However, and not unexpectantly, I can not do the 'select *' from the file if there are duplicate names. Can anyone make recommendations here that might help? Oh, this is SQL200 in case that impacts what you might advise!!!!
View Replies !
Dynamic/variable Field Names In Expressions
I have a report containing 3 datasets. One of these sets is a record set of 2 columns (Name,Priority). I use this dataset as a list so I can create a page for each Name is the list. On each page, I would like to access the other 2 datasets and depending on the Name in the list (Fields!Name.Value), I need to access different Fields. This can easily be done by making a long =IFF list but I would rather make this dynamic, cause the IFF statement becomes a long mess. One of the Names of the list is Joe so if the list name = Joe, his columns need to be read. =Max(Fields!ABSDIFF_JOE.Value, "APPLICATIONS") Or =round(sqrt(pow(StDevP(Fields!DIFF_TJOE.Value, "APPLICATIONS"),2) + pow(avg(Fields!DIFF_TJOE.Value, "APPLICATIONS"),2)),2) When the next list/page is created, the next name needs to be used to get the data from the correct field/column =Max(Fields!ABSDIFF_MAX.Value, "APPLICATIONS") or =round(sqrt(pow(StDevP(Fields!DIFF_TMAX.Value, "APPLICATIONS"),2) + pow(avg(Fields!DIFF_TMAX.Value, "APPLICATIONS"),2)),2) Can I somehow create this as such: =Max(Fields!ABSDIFF_ & Fields!Name.Value &.Value, "APPLICATIONS") or =round(sqrt(pow(StDevP(Fields!DIFF_T& Fields!Name.Value &.Value, "APPLICATIONS"),2) + pow(avg(Fields!DIFF_T& Fields!Name.Value &.Value, "APPLICATIONS"),2)),2) As you can see, I replaced the name (Joe or Max) with "Fields!Name.Value". Unfortunately this doesn't work. Any advice would be appreciated. Thanks Rob Ps. The record set used as input for this report is rather difficult to change.
View Replies !
DTS Of Data Between Two Tables, Same Field Names But Differnt Datatypes
I need to migrate data from one sql database to another. The second DBis a newer version of the "old" database with mostly the same tablesand fieldnames. In order support some reporting queries in the "new"version I needed to change the datatype of a few fields from varchar toint(the data stored was integers already as they were lookup tables).DTS works great except in the cases of about 10 fields which I changedthe datatypes on from varchar to int.DTS seems to drop the data if the fieldname and datatype are not anexact match. Is there any way to use DTS and have it copy data from afield call subsid type varchar to a field call subsid type int?
View Replies !
Splitting Out Names From One Field Delimted By Comma And A Space
have a field in a table that has combined lastname,firstname and middle name like combs,albert mike woods-athere,jane alice The last and first name are separated by a comma, and the middle name by a space I need in tsql to split them to last name,first name and middle name I used below select substring (longname, 1, patindex( '%,%' , longname) -1 ) 'firstname', substring (longname, patindex( '%,%', longname) + 1, len(longname)) 'lastname', substring (longname, patindex( '% %', longname) + 1, len(longname)) 'middlename' FROM Demographic_staging I get the names all split, but I get the middle name with the first name. How can I limit the first name to recoginze the comma, but stop at the space
View Replies !
Get Percentage With Variation Of Field Values (country Names)
Any help here would be greatly appreciated... Unfortunately, data wasn't filtered prior to getting inserted into this table. Now I am stuck with cleaning it up. I have thought about writing a query to update all the values, but there are just too many variations, including spelling mistakes, so I've ruled that out as a possible solution. I have a table which has a Country field but the values per record vary. For example US, U.S., USA, United States, UK, United Kingdom, Canada, Can, etc. I'm trying to find the percent of records per country. Sample table data: mytable Id Name Country 1 John US 2 James UK 3 Jane United States 4 Mary Canada 5 Jack U.S. 6 Tony United Kingdom 7 Jeff US 8 Tom Canada 9 Beth UK 10 Mark USA I would like to show US: 50% --> (includes any variation of US ncluding US, U.S., USA, United States) UK: 30% CAN: 20% I've made several attempts myself with no luck. Thanks in advance.
View Replies !
Field Names Missing In MDX Data Set When Using NON EMPTY Clause
So I have an MDX query in an SSRS data set. Here is my MDX query: Code SnippetSELECT { [Measures].[Gross Sales Amount USD], [Measures].[Net Sales Amount USD] } ON COLUMNS, { ([Promotion].[Media Property].[Promo Code Description].ALLMEMBERS) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOMEMBER(@BeginDateConvert, CONSTRAINED) : STRTOMEMBER(@EndDateConvert, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@PromotionMediaProperty, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( { [Promotion].[Campaigns].[Campaign].&[Paid Partner] } ) ON COLUMNS FROM ( SELECT ( { [Products].[Product Line].[Line].&[Merchandise] } ) ON COLUMNS FROM ( SELECT ( { [BusinessUnit].[Business Unit].[Product Business Unit].&[40] } ) ON COLUMNS FROM [Net Sales]))))) WHERE ( [BusinessUnit].[Business Unit].[Product Business Unit].&[40], [Products].[Product Line].[Line].&[Merchandise], [Promotion].[Campaigns].[Campaign].&[Paid Partner] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS This query returns 4 fields. Media Property, Promo Code Description, Gross Sales, and Net Sales. For the given query the measures are empty or null. I do not want any data to show up when the measures are null so I put in NON EMPTY clauses before the COLUMNS and before the ROWS. So now my query looks like this: (I only added the NON EMPTY clauses) Code Snippet SELECT NON EMPTY { [Measures].[Gross Sales Amount USD], [Measures].[Net Sales Amount USD] } ON COLUMNS, NON EMPTY{ ([Promotion].[Media Property].[Promo Code Description].ALLMEMBERS) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOMEMBER(@BeginDateConvert, CONSTRAINED) : STRTOMEMBER(@EndDateConvert, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@PromotionMediaProperty, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( { [Promotion].[Campaigns].[Campaign].&[Paid Partner] } ) ON COLUMNS FROM ( SELECT ( { [Products].[Product Line].[Line].&[Merchandise] } ) ON COLUMNS FROM ( SELECT ( { [BusinessUnit].[Business Unit].[Product Business Unit].&[40] } ) ON COLUMNS FROM [Net Sales]))))) WHERE ( [BusinessUnit].[Business Unit].[Product Business Unit].&[40], [Products].[Product Line].[Line].&[Merchandise], [Promotion].[Campaigns].[Campaign].&[Paid Partner] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS Adding the NON EMPTY returns nothing... not even field names. This is a problem because, I have a table in the report that looks at this data set and when there are no fields, the report can't run. How can I still have NON EMPTY functionality and still show the fields? Is this a problem in SSRS?
View Replies !
Cannot Copy Field Names In Column Headings From View Results
When I am using a SQL Query I have an ability to control whether or not I am able to Include the Column Headers when copying or saving results. The control exists in the Options > Query Results > Results to Grid > Include column headings etc. My question is how to get this same ability when attempting to copy the results of a VIEW vs. a Query. The idea is that when I setup a view it€™s a drag/drop type of query building (query building for dummies if you will). Once I have a view and click the Execute icon it will return all the records selected by the View. However, when I click the upper left/top box to select all rows and column and then try to copy/paste the records into Excel all the data copies just fine but the field name/column headers are not there. How can I get the header fieldname date to copy/paste from View result set that I'm able to copy from a Query result set? Thank you, Mike
View Replies !
How To Retrieve Query Results With Now Field Names, Dotted Line Or Footer
Hi folks,I'm trying to write a simple SELECT statement that will execute inquery analyser but will just have the data with no column names, or thedotted line between them and the data. I also want to avoid thestatement at the end which says nnn rows affected. any ideas? I want todo this because I intend to write the results to a flat file.Thanks for your helpDanny....
View Replies !
Table Names
Oracle provides for a table to have an alias name. Does SQL Server 7 support alias names for tables? Thanks!!
View Replies !
SQL DBO Table Names
In SQL Server Management Studio when browsing the tables they are all prefixed with dbo followed by the table name. Is there a way of turning this off. Thanks
View Replies !
Get Table Names.
I like to print the table names that starts with 'table'. I'm getting the same table name four times. Instead of four different table name. create table table1(tableID int, tableName varchar (30)) insert table1 select 1, 'tableSam' union all select 2, 'tableDaniel' union all select 3, 'tableRon' union all select 4, 'tableKen' union all select 5, 'tableHonda' create table table2(tableID int, tableName varchar (30)) insert table2 select 6, 'tableSam' union all select 7, 'tableDaniel' union all select 8, 'tableRon' union all select 9, 'tableKen' union all select 10, 'tableHonda' create table table3(tableID int, tableName varchar (30)) insert table3 select 11, 'tableSam' union all select 12, 'tableDaniel' union all select 13, 'tableRon' union all select 14, 'tableKen' union all select 15, 'tableHonda' create table table4 (tableID int, tableName varchar (30)) insert table4 select 16, 'tableSam' union all select 17, 'tableDaniel' union all select 18, 'tableRon' union all select 19, 'tableKen' union all select 10, 'tableHonda' declare @string varchar(330) declare @tableName varchar(30) declare @count int set @count = 0 while (@count < 5) begin select @tableName = table_name from information_schema.tables where table_name like 'table%' exec(@string) print @tablename set @count = @count + 1 end
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 !
Table Names As Keys?
Hello all,I got a chance to peak into a database system. Part of its design israther unfamiliar to me. When I look at the diagram generated by SQLServer, there are many floating tables. Eventually it turns out thatthese many floating tables are actually not floating. Their tablenames relate to fields (as TableID) in other tables. In this case,you can get a handle to one of such tables by search TableID columnsin other tables.To be more specific, the database is a microarray database implementedin SQL Server 2000. They have a table called MICROARRAYS. In thistable, there is a column called table_id. These table_ids are in facttable names of a bunch of other tables.My questions are1) Is this good relational design?2) How well is this kind of design supported in SQL Server?3) Are there better alternatives?Any other comment or link to helpful resources will also beappreciated.Thanks,Eric Wu
View Replies !
Finding Out Table Names
I'm fairly new to sql.I've got a database that all I want to do is view some records fromsome tables. But the thing is, I do not know the table names ortheir relationships.I've got access to the database thru query analyzer. Can someoneassist me in gettign the correct statements that will allow me to findout what tables exist in the database.
View Replies !
Qualified Table Names
I have a quick question on how to qualify table names as it relates to"dbo" vs. user names. Suppose that I am a user named "dwuser1", andthat I need to create a table named "dw_stage_1". Do I use dbo as in"dbo.dw_stage_1" or do I use "dwuser1.dw_stage_1" for the qualifiedtable name? Are both OK? If so, what would be the implications ofeach?
View Replies !
Table Names And Variables
I have a cursor which populates a variable with the name of each user table within my DB. I'm trying to copy the tables one at a time by using a command like this: SELECT * INTO @NewTable FROM @OrigTable Query analyzer tells me that there's incorrect syntax near the keyword 'INTO'. This seems fair enough to me as I assume it's trying to put the data into the variable rather than the table name which is held within the variable. Does any know how I can get around this? Are there any alternative ways of copying the table structure (preferable without the data)? BTW both variables are of type char(100)
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 !
Get Table Names From Query
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
View Replies !
HELP - Temp Table Names
I am using 7.0 and trying to use global temp tables to help create a cross tab query. The problem I am finding is that when the table table name contains a 0 in the name it will not build dynamic sql statements. Example output from Trace files. These are the same exact code with the same parameters called for both. set @s_tab1 =@s_tab + '7' Event ClassTextDatabase IDObject IDServer NameBinary DataStart Time +SP:StmtStartingexec('select IDENTITY (int, 1, 1) AS rownum,* into ' + @s_tab1 + ' from ' + @s_tab) InternetReportsit_BusCatCityIRDEV15:55:49.270 THIS CASE DOES NOT WORK, FOR SOME REASON IT DOES NOT EXECUTE THE STATEMENT +SP:StmtStartingexec('drop table ' + @s_tab) setInternetReportsit_BusCatCityIRDEV15:55:49.270 +SP:StartingDynamic SQLInternetReportsIRDEV15:55:49.270 +SP:StmtStartingdrop table ##MJP5570 InternetReports131366573IRDEV15:55:49.270 Event ClassTextDatabase IDObject IDServer NameBinary DataStart Time +SP:StmtStarting@s_temp ='select * into ' + @s_tab + ' from ' + @s_tab1 + ' where rownum >' + str(@i_firstrec) + ' and rownum < '+ str(@i_lastrec) InternetReportsit_BusCatCityIRDEV16:01:01.940 +SP:StmtStartingexec(@s_temp) InternetReportsit_BusCatCityIRDEV16:01:01.940 +SP:StartingDynamic SQLInternetReportsIRDEV16:01:01.950 +SP:StmtStartingselect * into ##MJP1287 from ##MJP1287MJ where rownum > 0 and rownum < 16InternetReports831921532IRDEV16:01:01.950 +SP:CompletedDynamic SQLInternetReportsIRDEV16:01:02.210 +SP:StmtStartingexec('drop table ' + @s_tab1) InternetReportsit_BusCatCityIRDEV16:01:02.210 +SP:StartingDynamic SQLInternetReportsIRDEV16:01:02.210 +SP:StmtStartingdrop table ##MJP1287MJInternetReports566890076IRDEV16:01:02.210
View Replies !
Detecting Table Names
If anyone could please help me with this, I would appreciate it. I need to detect the names of each table available to the database connection. Does anyone know how this is done? I'm sure it is possible, because Visual Interdev does it... As soon as you add a data connection, it displays each table. I need this for an ASP page. Thanks, -Ryan
View Replies !
Unable To See Table Names In DTS
While setting up a BCP in a DTS package, I've found a rather annoying 'feature' in DTS. When selecting the destination table, the display does not provide enough spaces to see the full table name. It is a drop down with a set width, and the database and owner are displayed with the table name. I hadn't noticed this problem in the past, but the database I'm working with now has several tables that start with the same letters, and when the drop down displays them, they all look the same. Does anyone know of a work around for this so that I can see the full table names? Even being able to see them after the package is created would be nice, because then I could check to see if I guessed the correct table. Thanks
View Replies !
|
TRACKER
