Finding Out Table Names

Jul 20, 2005

I'm fairly new to sql.

I've got a database that all I want to do is view some records from
some tables. But the thing is, I do not know the table names or
their relationships.

I've got access to the database thru query analyzer. Can someone
assist me in gettign the correct statements that will allow me to find
out what tables exist in the database.

View 4 Replies


ADVERTISEMENT

Finding Table Names

Jun 29, 2006

Is there a way i can create a query that gives me a list of all the table names in the database?

View 2 Replies View Related

Finding Table Names

Feb 19, 2008

I'm new here so bare with me for alittle bit.
When I go to a website how to I find out the table names in the data?

I mean how can I do "select * from ___" if I don't know the table name.

Thank you for any help,
S_R_S

View 5 Replies View Related

Finding Out Field Names By Lineage ID

Apr 26, 2006

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 1 Replies View Related

Finding Unique Field Names With Different Values

Jun 8, 2005

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 2 Replies View Related

TSQL Script For Finding Column Names In Stored Procedures

Feb 23, 1999

Does anyone have a TSQL utility (that they can share) that recursively searchs sysobjects for a matching input parameter string(for instance column name) for stored procedure object properties that returns the stored procedure name?

View 1 Replies View Related

Dynamically Pass Table Names And File Names To IS Package

Mar 1, 2015

I am designing a package to export staging tables into a flat file.The names of the tables will be: TableAStaging_YYYYMM and TableBStaging_YYYYMM. As you can see the names of the tables will be changing each month.

The flat files will have similar naming: C:MyPathFlatFileTableAStaging__YYYYMM and C:MyPathFlatFileTableAStaging__YYYYMM.I want to run the package as an sql job in two steps, one for each table.I need to dynamically pass the table names and file names (together with the path) to the IS package.

View 1 Replies View Related

Determine Table Names And Column Names At Runtime?

Jan 22, 2004

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.

View 5 Replies View Related

Table Names And Field Names

Jan 21, 2004

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 5 Replies View Related

Finding Rows In One Table But Not In Another Table Based On 2 Fields

Apr 23, 2008



I have 2 tables with 2 fields in common between them. How do I get a count of rows in table 1 but not in table 2 matching those 2 fields? Thanks.

View 7 Replies View Related

SSIS Loading DWH Staging Area When Table Names Is Selected From Table List

Aug 31, 2007

Hello,
Maybe anyone have done that before?
I have table where i store SOURCE_TABLE_NAME and DESTINATION_TABLE_NAME, there is about 120+ tables.
i need make SSIS package which selects SOURCE_TABLE_NAME from source ole db, and loads it to DESTINATION_TABLE_NAME in destination ole db.

I made such SSIS package. set ole db source data access mode to table or view name variable.
set ole db destination data access mode to table or view name variable. set to variables defoult values (names of existing tables)
but when i loop table names is changed, it reports error, that can map columns, becouse in new tables is different columns.

how to solve that problem?

View 5 Replies View Related

Table Names In Stored Procedures As String Variables And Temporary Table Question

Apr 10, 2008

How do I use table names stored in variables in stored procedures?




Code Snippetif (select count(*) from @tablename) = 0 or (select count(*) from @tablename) = 1000000





I receive the error 'must declare table variable '@tablename''

I've looked into table variables and they are not what I would require to accomplish what is needed.
After browsing through the forums I believe I need to use dynamic sql particuarly involving sp_executesql. However, I am pretty new at sql and do not really understand how to use this and receive an output parameter from it(msdn kind of confuses me too). I am tryin got receive an integer count of the records from a certain table which can change to anything depending on what the user requires.




Code Snippet

if exists(Select * from sysobjects where name = @temptablename)
drop table @temptablename




It does not like the 'drop table @temptablename' part here. This probably wouldn't be an issue if I could get temporary tables to work, however when I use temporary tables i get invalid object '#temptable'.

Heres what the stored procedure does.
I duplicate a table that is going to be modified by using 'select into temptable'
I add the records required using 'Insert into temptable(Columns) Select(Columns)f rom TableA'
then I truncate the original table that is being modified and insert the temporary table into the original.

Heres the actual SQL query that produces the temporary table error.




Code Snippet
Select * into #temptableabcd from TableA

Insert into #temptableabcd(ColumnA, ColumnB,Field_01, Field_02)
SELECT ColumnA, ColumnB, Sum(ABC_01) as 'Field_01', Sum(ABC_02) as 'Field_02',
FROM TableB
where ColumnB = 003860
Group By ColumnA, ColumnB

TRUNCATE TABLE TableA

Insert into TableA(ColumnA, ColumnB,Field_01, Field_02)
Select ColumnA, ColumnB, Sum(Field_01) as 'Field_01', Sum('Field_02) as 'Field_02',
From #temptableabcd
Group by ColumnA, ColumnB




The above coding produces

Msg 208, Level 16, State 0, Line 1

Invalid object name '#temptableabcd'.

Why does this seem to work when I use an actual table? With an actual table the SQL runs smoothly, however that creates the table names as a variable problem from above. Is there certain limitation with temporary tables in stored procedures? How would I get the temporary table to work in this case if possible?

Thanks for the help.


View 6 Replies View Related

SQL Server 2012 :: Return A Table Of Table-names Dynamically?

Sep 14, 2015

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]....

View 9 Replies View Related

Passing Table Names To Table Valued Functions

May 25, 2008

Hello,
It is possible to write stored procedures which take table names as parameters; is it also possible to do this with table valued functions?

For example, a simple stored procedure is this:

CREATE PROCEDURE SelectTop(@tableName sysname)
AS
BEGIN

Execute('Select top 10 * from ' + @tableName + ';')

END

I want to be able to do the analogous thing with a table valued function (so that I can query the result set, without having to create a temp table). How should I do this (i.e., pass a tablename as an argument to a table valued function)?

View 11 Replies View Related

Finding The Table Name.

Dec 15, 2006

I have a script that creates these spin off tables (used for lightning fast access) on a near daily basis. But it is possible the table does not exist for that day - in which the script need to check one day back - so on and so forth until finding the most recently created table.

The script I have written fails with overflow error.
I was hoping someone could tell me the correct syntax to accomplish this and also why i recieve this overflow error...


Code:


Dim str_SQL_Asset_1
daysBack = 1
tblFound = FALSE
Dim xbl_Holdings_date
Dim xMonth
Dim xDay
xbl_Holdings_date = date()
If Len(Month(xbl_Holdings_date))=1 Then
xMonth = "0" & Month(xbl_Holdings_date)
Else
xMonth = Month(xbl_Holdings_date)
End If
If Len(Day(xbl_Holdings_date))=1 Then
xDay = "0" & Day(xbl_Holdings_date)
Else
xDay = Day(xbl_Holdings_date)
End If

DO WHILE tblFound = FALSE
xbl_Holdings_Name = "xbl_Holdings_" & Year(xbl_Holdings_date) & "_" & xMonth & "_" & xDay
SQL_tblCheck_STR = "SELECT * FROM INFORMATION_SCHEMA.TABLES " & _
"WHERE table_name = '" & xbl_Holdings_Name & "' "

Set MM_checkCmd = Server.CreateObject("ADODB.Recordset")
MM_checkCmd.ActiveConnection = conn_str_ClientDB
MM_checkCmd.Source = SQL_tblCheck_STR
MM_checkCmd.CursorType = 0
MM_checkCmd.CursorLocation = 2
MM_checkCmd.LockType = 3
MM_checkCmd.Open()


IF MM_checkCmd.EOF Then
xbl_Holdings_date = (xbl_Holdings_date - daysBack)
daysBack = daysBack + 1
tblFound = FALSE
ELSE
tblFound = TRUE
MM_checkCmd.CLOSE()
SET MM_checkCmd = Nothing
END IF
loop



The goal of this snippet is simply to identify and set the value for the existing table name.
An example table name would be "xbl_Holdings_2006_12_10".

I could have posted this question in the VB forum but I would like to push as much of the work onto SQL as possible and also - this sort of operation is more likely to be required of a dba imo.

View 1 Replies View Related

Join Table Key To Multiple Table Names

Nov 5, 2004

Hi there. I haven't been able to figure out how to join a table on column on multiple table names. Here's the situation:

I have a table "tblJob" with a key of jobID. Now for every jobID, the program creates a new table that keeps track of the stock before the jobId was processed and after it was processed to give accurate stock levels and show the difference in stock levels. So, a jobID of 355 would be related to the table: "tblPreStock_335" and "tblPostStock_335". These 2 tables have all the materials in stock and the quantity. Therefore they show how much material was used. I need to figure out the difference in the material in the stock before and after the processing.

That means that I have to get a stockID, get the associated pre and post tables, and then display the difference of ALL the materials in the pre and post tables.

Could someone help me get started on the right path? Even a link to similiar problem that I haven't found would be nice.

Thx

View 12 Replies View Related

Finding The Primary Key Of A Table.

Dec 3, 2007

I'm trying to find the primary key on a given table in SQL Server 2000 using SQL.  I'm querying the sysobjects table to find a given table, and then querying the sysindexes table.  I've ALMOST found what I'm looking for.   I see the indexes and columns etc. on the tables in the database, I just don't see the field that indicates that the index is the primary key.
Can anyone help?
Thanks, Alex

View 6 Replies View Related

Finding The Size Of A Table?

Oct 12, 2007

Hi All,

Do any of you know how I can find the size of a table in SQL server?

Cheers

Dave

View 3 Replies View Related

Finding Rows Within A Table

Jun 25, 2007

When I open a table and need to make a change within a particular record...



How do I find the particular row that I am looking for? I seem to have to scroll through 600 records to find the one I need. I've found a search and replace function but it doesn't seem to work.



Thanks I appreciate any advice you can give me.



View 3 Replies View Related

Stored Proc Not Finding Table

Jul 17, 2005

Need some suggestions on what to check or look for.My stored proc is not finding the table in the sql db.  This is the error...Invalid object name 'tblServRec'I use the same function to pass in an arraylist and the sp name for another sp and that one works ok.The sp is the same as another one except for the table name and that one works ok.The sp works fine on my local machine.  It finds the table ok.  But when I try it on the server, it doesn't work.I have checked permissions, and they are the same for all tables, even the one that the other sp works ok on the server.Here is the sp if that will help.....CREATE PROCEDURE dbo.deleteServRec
@fldNum    INT  ASBEGIN DECLARE @errCode   INT
 DELETE FROM tblServRec WHERE fldNum = @fldNum     SET @errCode = 0 RETURN @errCode
HANDLE_APPERR:
 SET @errCode = 1 RETURN @errCodeENDGOThanks all,Zath

View 4 Replies View Related

Finding Last DDL Activity Time In A Table

Nov 1, 2001

Hi Friends,

Is there any way to find the time when the last DDL was happened in a table?
For example: The time when the new column(s) were added into a table or changed the datatype.

Thanks for the help in advance.

--Raj

View 1 Replies View Related

Finding Table Modification Date

Oct 10, 2001

Is there a way to find out when the last modification or change to data in a table occured ? How about the last change of a view ?

We'd like to be able to extract data to another table based on a view. Then, at a later date, check to see if the first table or view has changed since the last extract. If not, then we don't need to re-extract the data, we know it hasn't changed.

View 1 Replies View Related

Finding Out Which Rows Have Been Updated In A Table

Feb 9, 2005

Can this be done easilly through a stored proc?

View 1 Replies View Related

Finding Table , Database Size

Sep 29, 2005

Hii all,

I want to find the database , table size in MS SQL Server.

Is there is any query /procedures to find out the database and table size.

Thanks in advance .,

Regards.,

Sri

View 5 Replies View Related

Finding Lowest Value In A Table Greater Than A Certain Value?

Dec 19, 2013

finding the first value to cross a specific threshold in SQL Server

LoadTemp1 LoadTemp2 LoadTemp3 TimeStamp
300 350 200 12-10-2013 13:30
177 238 333 12-10-2013 13:05
240 190 170 12-10-2013 12:45

How do I design query to find the first LoadTemp* that cross a threshold of 340 degrees per TimeStamp.

View 6 Replies View Related

Finding Products Code With A Table

May 29, 2008

Hi i've to table where i have a product code and a area code.

I want to insert the values from table 1 into table 2 if the product code and area code do not already exists in the table in table 2.

Am using this code below but it does not seem to be working.

select distinct p_code1, area_code
from table1
where not exists
(select p_code, area_code
from table 2 )

View 2 Replies View Related

Finding Suspected Abuse From Log-table

Jun 17, 2008

A client has a site, that requires users to log in, and each login is logged with IP in this table:

CREATE TABLE [dbo].[Event](
[EventId] [int] IDENTITY(1,1) NOT NULL,
[EventTypeId] [int] NOT NULL,
[IP] [varchar](50) NULL,
[UserId] [int] NULL
[DateCreated] [datetime] NOT NULL CONSTRAINT [DF_Event_DateCreated] DEFAULT (getdate()),
CONSTRAINT [PK_Event] PRIMARY KEY CLUSTERED
(
[EventId] ASC
)

How the client wants me to write a query, that will find "suspected abuse of accounts" - defined by being successful logins made within one hour from at least 3 different IPs.

So I need to check for events, that are within one hour from each other (datecreated field) and have the same userID - and EventTypeId - but at least 3 different IPs.

Maybe it's just late in the day or I am overworked, but I cant seem to find an effective way to approach this.

Ideas?

View 3 Replies View Related

Finding The Filegroups Associatted With A Table In T-SQL

Jul 20, 2005

Hi,I want to find out in my SP the filegroup associated with a particulartable? I was not able to establish the relationship between sysobjectsand sysfilegroups table. Is there any other way to find filegroup of atable in T_SQL?Also lets say there are 3 dtatfiles in a filegroup 'FG1' and table'xyz' is created on filegroup 'FG1' - Is there any way to find out thedatafile's names on which table xyz's data is scattered?Thanks in Advance,Subodh

View 1 Replies View Related

Finding Out How Many Times A Table Is Accessed

Jul 20, 2005

We have an application that accesses a particular table, but it isnever updated. Without stepping through the complex application code,is there a method of logging when a particular table is accessed?

View 3 Replies View Related

Table Names

Aug 1, 2000

Oracle provides for a table to have an alias name.

Does SQL Server 7 support alias names for tables?

Thanks!!

View 2 Replies View Related

Get Table Names.

May 21, 2007

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 12 Replies View Related

SQL DBO Table Names

Mar 26, 2008

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 3 Replies View Related

Finding Lower Case Data From Table

Jun 17, 2003

Hi,

My database is case insensitive. However, the application is case sensitive for the data in the table.

I need to find out the data from the table where the data is stored in lower case.

Here is the sample data:
Table: inv_manager
column: sku varchar(5)
Data: 1134X
1135x
1123a
b145Y

I just need query to return row 2, 3, 4.

Here is my query:

select sku from inv_manager where sku like lower('%[a-z]%')

The problem is it returns all the rows from the table including first one.

How can I return just the data with lower case ?

Thanks

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved