Name Of Tables And Columns

Aug 2, 2007

Hi,
i just migrated an database from oracle to sql server 2005 with the migration tool from microsoft (v3). the migration tool works only with uppercase table and column names, but i need them in lower case. is there a way to modify the names of tables and columns with t-sql to lower case?
Thx
Frank

View 7 Replies


ADVERTISEMENT

SQL 2012 :: Calculated Columns Conditional On Calculated Columns Multiple Tables

Apr 20, 2014

I have 4 tables involved here. The priority table is TABLE1:

NAMEID TRANDATE TRANAMT RMPROPID TOTBAL
000001235 04/14/2014 335 A0A00 605
000001234 04/14/2014 243 A0A01 243
000001236 04/14/2014 425 A0A02 500

TRANAMT being the amount paid & TOTBAL being the balance due per the NAMEID & RMPROPID specified.The other table includes a breakdown of the total balance, in a manner of speaking, by charge code (thru a SUM(OPENAMT) query of DISTINCT CHGCODE

TABLE2
NAMEID TRANDATE TRANAMT RMPROPID CHGCODE OPENAMT
000001234 04/01/2014 400 A0A01 ARC 0
000001234 04/05/2014 -142 A0A01 ARC 228
000001234 04/10/2014 15 A0A01 ALT 15

[code]...

Also with a remaining balance (per CHGCODE) column. Any alternative solution that would effectively split the TABLE1.TRANAMT up into the respective TABLE2.CHGCODE balances? Either way, I can't figure out how to word the queries.

View 0 Replies View Related

To Many Columns Or To Many Tables?

Feb 6, 2007

my supervisor is trying to help move our existing database in IBM U2 into SQL Sever  he has a file/table that as 12001 columns, 1 column is the key300 columns are different field errors that can be checked true or falseand for each of those 300 columns there are three extra columns where extra information can be stored   is there a size limitation when it comes to columns in a SQL table?what is an efficient way of doing this?create one giant table with 12000 columns?create 300 tables, each table associated with a error association? which will then need to be joined?any suggestions? comments? tips?    

View 3 Replies View Related

How To Add Tables Name With 3 Columns

Aug 6, 2015

When I run below query I get 3 columns, but when I try to add table name ind.object_name (object_id) it's giving me an error "Ambiguous column name 'object_id".How do I add tables name with 3 columns?

SELECT ind.name, ic.index_id , ind.type_desc from sys.indexes ind
INNER JOIN
sys.index_columns ic ON ind.object_id = ic.object_id and ind.index_id = ic.index_id
INNER JOIN
sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id
INNER JOIN
sys.tables t ON ind.object_id = t.object_id

View 4 Replies View Related

How To Index Through A Tables Columns

May 8, 2005

I am trying to index through the columns of MyTable so I can do the same work on all columns. I know how to get the column names from MyTable but when I use @MyColName in the SELECT statement to get MyTable Column 0 Row values I get a table with the column name in each row cell. I can't get the syntax correct to return the value in each cell for that column.
This is a extremely simplified example !!!!!!DECLARE @MyColName nvarchar(30)
--Get the MyTable Column 0 NameSELECT @MyColName = Col_Name(Object_ID('MyTable'), 0)
--Display the MyTable Column 0 Row valuesSELECT @MyColName FROM MyTable --This is the syntax I can not get correct
 
Can anyone help ?
Thanks

View 2 Replies View Related

Cant Add New Columns To Data Tables

Sep 19, 2005

Hi All,I am having  a problem for which i dont find any reasons ..I hope to get a solution from here.I have 2 tables ..1 with around 150 columns and the other with around 80 columns.I have view based on these tables.The problem i m facing from yesterday is that i cant add /delete/change on these tables.If i make a change on these tables and hit the save button the sql server enterprise just hangs..i also tried to add the columns through the query but no results.I cant even drop this view.Please any help on this??Thanks

View 1 Replies View Related

Loop Through All Tables And Columns

Oct 26, 2005

Is there a way to change the collation against all the tables and columns within a database?

View 2 Replies View Related

Search Tables And Get Columns

Nov 10, 2005

I drew short straw on some reports work and am spending an eternity searching through catalogs looking for tables and columns. I'm a little new to SQL. Is there a way to search a catalog for table column names?

View 2 Replies View Related

Listing All Tables And Their Columns?

Jun 14, 2006

Hey all. I apologize, but I'm a developer, not a DBA. I need to run a query that will list each table in a DB as well as the columns i nthose tables.

I know that you can use: EXEC sp_help 'table_name' to get a description, but I'm not sure how to set up a cursor to substitute the table names, or where to get the tables names.

Any help would be greatly appreciated. Thanks!

View 2 Replies View Related

Search All Columns Of All Tables

Feb 5, 2008

How to search all columns of all tables for a keyword?:o
that in MS sql

thanks in advance..

View 2 Replies View Related

Sum Columns From Multiple Tables

May 5, 2012

I have several tables that I need to sum up a colomn and display the results. I have been struggling with this for some time. Here are my Tables and their columns:

Ingredients_tbl
IngredientID
Description

Inventory_tbl
IngredientID
AmountRemaining
Renconciled
Active

Order_Details_tbl
IngredientID
OrderAmount
Status

Order_Details_Details_tbl
IngredientID
OrderAmount
Status

I want to display all the ingredients from the ingredients table, sum the amountremaining of the items that are reconciled = 'false' from the inventory_tbl, sum the orderamount from both the order_details_tbl and Order_details_details_tbl for each ingredient based on status. Here is some sample data and results I am looking for:

Ingredients_tbl
IngredientID -- Description
1 -- Corn
2 -- Beer
3 -- SBM
4 -- Wine

Inventory_tbl
IngredientID -- AmountRemaining -- Reconciled
1 -- 500 -- False
1 -- 500 -- False
1 -- 100 -- True
1 -- 500 -- False
2 -- 1000 -- False
2 -- 1000 -- False
4 -- 500 -- False
4 -- 500 -- False

Order_Details_tbl
IngredientID -- OrderAmount -- Status
1 -- 100 -- ORDERED
1 -- 100 -- MIXED
1 -- 100 -- DELIVERED
2 -- 100 -- ORDERED
3 -- 100 -- ORDERED
3 -- 100 -- ORDERED
3 -- 100 -- ORDERED
4 -- 100 -- ORDERED
4 -- 100 -- DELIVERED

Order_Details_Details_tbl
IngredientID -- OrderAmount -- Status
1 -- 100 -- ORDERED
1 -- 100 -- DELIVERED
1 -- 100 -- DELIVERED
2 -- 100 -- ORDERED
3 -- 100 -- ORDERED
3 -- 100 -- ORDERED
4 -- 100 -- ORDERED

I would like the results to be like this:

IngredientID
Description
Inventory = Sum of AmountRemaining of the records in Inventory_tbl where Renconciled = 'false'
Production1 = sum of OrderAmount of the records in the Order_Details_tbl where Status = 'ORDERED' Or Status = 'MIXED'
Production2 = sum of OrderAmount of the records in the Order_Details_Details_tbl where Status = 'ORDERED' Or Status = 'MIXED'

IngredientID -- Description -- Inventory -- Production1 -- Production2
1 -- Corn -- 1500 -- 200 -- 100
2 -- Beer -- 2000 -- 100 -- 100
3 -- SBM -- 0 -- 300 -- 200
4 -- Wine -- 1000 -- 100 -- 100

I thought this was going to be as easy as a few simple joins and aggregate sum on the colomns like this:

SELECT Ingredients_tbl.IngredientID, Ingredients_tbl.Description,
Ingredients_tbl.LowInventory, SUM(Inventory_tbl.AmountRemaining) AS Inventory, SUM(Order_Details_tbl.OrderAmount) AS Production1, SUM(Order_Details_Details_tbl.OrderAmount) AS Production2
FROM Ingredients_tbl
LEFT OUTER JOIN Inventory_tbl ON Inventory_tbl.IngredientID = Ingredients_tbl.IngredientID AND Inventory_tbl.Reconciled = 'False'

[Code] .....

But this obviously has its issues with incorrect returned sum values.

View 2 Replies View Related

Combine 2 Columns From 2 Tables

May 23, 2008

Hi,

I have 2 tables called Table A, Table B,

In Table A i am having Data1, Data2 like 2 datas in Column 1
In Table B i am having Data2, Data3 Like 2 datas in Column 1

Now want a output like

Data1,
Data2,
Data3

Please help me to get this....

Thank you,
Senthil

View 4 Replies View Related

How To Compare 3 Columns From 2 Tables

Jul 26, 2013

I have requirement like this

1 st column 'A1Ctest' 2 nd column 'diagnoising heart disease' and my 3 rd column is combination of both columns
'A1Ctest for diagnoising heart disease'.Here i need to comapre 'A1Ctest' from 1st column and 'diagnoising heart disease' from 2 nd column

View 5 Replies View Related

Finding Two Columns In All Tables

Oct 29, 2013

I have several tables that have the POLICY_NUMBER and POLICY_DATE_TIME in them.. All the tables with these two columns should have a POLICY_ NUMBER and a corresponding POLICY_DATE_TIME.I would like to find all tables that have POLICY_NUMBER = 123456 but do not have the corresponding POLICY_DATE_TIME..

View 1 Replies View Related

Get Value From Columns Using List Of Tables

Feb 5, 2014

how do I get value from column using list of tables?

For example, I have list:

schema_name, table_name, column_name

How do I get:

schema_name, table_name, column_name, column_value

View 7 Replies View Related

Combining Columns From 2 Tables

Aug 7, 2014

I am using the JOIN function to pull data from two tables. Table_A has all columns I need; Table_B contains only 1 column I need. The column I need data from in Table_A is called CITY_NAME and stops May 1st. The column I need in Table_B (which has the same values but begins May 2nd) is labeled CITY. In Table_A I have NULL values starting Mat 1st for CITY_NAME. In Table_B, I have NULL values for any date before May 2nd.

I need to replace the NULL values in table B (May 1st and forward) with the values that are in Table B

SELECT
a.DATE,
a.STATE,
b.CITY
FROM TABLE_A a
LEFT JOIN TABLE_B b ON a.ID = b.ID

I need to use a function similar to UNION, but TABLE_A has 10 columns and TABLE_3 has 3 columns.

View 3 Replies View Related

Get Common Columns Name Between Tables

Aug 8, 2006

Hi,

Do anyone know or have a suggestion how to get commun columns name between several table in a SQL server database

For example I have table_1(name, age, school) table_2(name, address, city) and table_3(name, department, company)

name is the common column name (I don't care about the data) in this case. Is there a way to do it simple and easy ?

THANK YOU

View 6 Replies View Related

Getting The Matching Columns From Two Tables

Mar 1, 2006

Hi all..I have two tables such as cisco and ciscocom. and i wan to compare eachrow of ciscocom with cisco having same column values. i wan to get thecount of matching columns for each row in cisco...eg:Ciscocom has columns: Products,fw,ports,sec,des,tput etc and cisco hascolumns:fw,ports,sec,des,tput etc. i wan the number of matching columfor each row in ciscocom. please provide me with the procedure....Waiting for your response....

View 2 Replies View Related

Tables Have Too Many Nullable Columns

Jul 20, 2005

Hi,I've enherited a big mess, a SQL Server 2000 database withapproximately 50 user tables and 65+ GB data, no explicitrelationships among entities (RI constraints whatsover), attempt tocreate an ERD would more than likely kill the relatively complexedapp, the owner would want to drop out of window, so,I don't intend to do that, you get the picture, sorry no DDLs thistime, and many tables have many nullable columns, joins slows down thesystem quite a bit, what's my best bet to improve its performance?change most of these nullable columns into non-nullable with defaultvalue of something like ' ', any thoughts along this line would beappreciated.

View 6 Replies View Related

How Do I List Columns Within Tables?

Mar 4, 2008

Hi All.
I'm an Oracle DBA who's currently being asked to look at a SqlServer Database. I need a list of columns per table, but am having trouble.
I'll admit I might be being lazy here, but I'm in a hurry and using the valueable resources available to me!! Would really appreciate the sql i need to copy into the query window. Much obliged!!
I need........
Table A
Column1 Datatype
Column2 Datatype
Table B
Column1 Datatype
Column2 Datatype
etc....
Many thanks.

View 7 Replies View Related

Transact SQL :: Top 10 Columns In All The Tables

Oct 21, 2015

In our production database we are looking top 10 columns in all the tables, for this using the below script, output was showing only one table how we get the all the tables top 10 ...

select top 10 * from ProductIdList

View 6 Replies View Related

Query Foreign Key Columns And Tables

Dec 20, 2006

I am trying to query the database to get me the foreign key columns and the tables they belong to.I have: The name of the tableI need:The name of the column in the target tableThe name of the column in the referenced tableThe name of the referenced table  Any help would be great, thanks 

View 5 Replies View Related

Need Help With MAX Function Using Two Tables With Common Columns

Apr 9, 2006

I have two tables that contain product SKUs (12-character strings):
Table 1Product IdSKU...
Table 2ProductVariantIdSKU...
I need to find the MAX (i.e., last used) SKU that exists in either table. I did write two sps, one for each table that I can compare in code and use the larger (latest) one but I am not that facile with JOINS, etc., so I can't figure how how to create a single sp to return the value I am looking for--although I assume this must not only be possible but trivial to more experienced SQLers.
Thanks!
Duncan

View 4 Replies View Related

How Can I Update 2 Columns At A Time In 2 Tables

Mar 11, 2002

Hi
I have a 2 Tables EMP, STU
In EMP Table there is a Column "Country"
In STU Table there is a Column "City"
Where the EMPID = STUID
on this conditon how can i update those 2 columns

This is for one Table one column update how can i do 2 at a time
I don't want to do in 2 seperate UPDATE statements

UPDATE EMP
SET EMP.Country = 'USA'
WHERE EMP.EMPID = STU.STUID


Can some one can help me to fix this issue..


From
Madhavi

View 2 Replies View Related

When Or Where Does To May Columns Or Tables Affect Performance?

May 4, 2001

I have a db which I have little control over most of it's makeup because of the vendor supplied tools. We currently have over 700 tables and 19000 columns. Has anyone seen a problem or saturation pont with these kinds of numbers? The database delivered to the clients will be from 2-50 gig depending on the site. I can probably through hardware at problems, but if anyone has been down this road any suggestions are appreciated.

View 1 Replies View Related

Loading Data Into Columns 75 - N In Tables?

Jun 23, 2004

I'm having a very irritating time trying to migrate data from a COBOL system to SQL Server.

One of the A/R Master files has approx. 200 columns.

I can export this file any number of ways that will (sometimes) load partially into my database, but always when the load succeeds, columns 75 through N simply contain NULL, even though there is data in the file. When the load fails in DTS, the error is always missing column delimiter. Using BULK INSERT the error is always something like data too long at column 75.

Putting all this together, I have deduced that something isn't working if I try
to load a staging table with more than 74 columns of data. This seems like a way-too-low threshold for a robust database, especially since SQL Server is supposed to be able to handle up to 1,024 columns per table.

Has anyone ever encountered this problem?

Thanks in advance for any help

View 2 Replies View Related

Computed Columns In Temp Tables

Jun 25, 2004

I am having a problem with using UDF as part of a temp table computed column. Here's the sample code:
IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name = 'fn_test')
DROP FUNCTION dbo.fn_testGO
CREATE FUNCTION dbo.fn_test( @x int, @y int)
RETURNS INT AS
BEGIN
DECLARE @z INT
SET @z = @x + @y
RETURN @z
END
GO

CREATE TABLE #X
(
x INT,
y INT,
z AS (dbo.fn_test(x,y))
)
I receive the following error:

Server: Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.fn_test'.

I do not get this error if I use a regular table.
HELP!

View 5 Replies View Related

Comments For Tables / Columns In SQL Sever

Jul 27, 2004

Hi,

How do I add comments to tables / columns in SQL Server ? Oracle supports adding comments to tables / columns using "comment on table / column" statement. Is a similar feature available in SQL Server 2000 ?

Any help folks ?

TIA,
Sam

View 1 Replies View Related

Magic Tables With Text Columns

Oct 6, 2004

Hi folks,

I need to execute some kind of

select a,b,c into #inserted from inserted

a = int
b = varchar(50)
c = text

Easy so far. But I what I need is to find out whether the audited table contains text(or nText / image) columns or not. If it contains any text columns the statement should automatically be changed to

select a,b into #inserted from inserted

Remember that the trigger must be usable for any table in the database without manually entering the tables structure or the column names.

Best would be fetching the structure fropm the system views (INFORMATIONSCHEMES)

Any suggestions ?

Thanks a lot!

Stefan

View 1 Replies View Related

Checking Columns In Two Similar Tables

Jan 3, 2014

For every table in my database there is a duplicate table with same columns. For example, employee is the name of main table, there is employee_dup table in same database.

There is only one column extra in _dup tables i.e.,idn column.

Now, I want to know all the columns present in main table which are not present in corresponding _dup table. There might be a chance of missing one or two columns in _dup tables. So i want a query to find out all the columns present in main table that are not present in hx table.

View 6 Replies View Related

Comparing 2 Similar Columns In Different Tables

Mar 5, 2014

I am trying to write an SQL command for my crystal report. I need to compare the same column in 3different tables & get the data from each table for only the matching data.. I understand I need to create a temporary table, get the data into it & then work around.. I am quite new to SQL.

Eg: Considering one customer account
Table 1
Cust.No Name Amt_Counter AmtPaid
123.456 sam 0 0
123.456 sam 1 50

Table 2
Cust.No Name Freq_Counter Frequency
123.456 sam 1 0
123.456 sam 2 15

[code]....

View 3 Replies View Related

Select From A List Of Tables And Columns

Nov 26, 2014

The following returns all base tables within the database of type "varchar":

Code:
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME FROM mydb.information_schema.columns
WHERE TABLE_SCHEMA = 'master' AND TABLE_CATALOG = 'mydb'
AND DATA_TYPE IN('varchar')"
AND TABLE_NAME IN(
SELECT TABLE_NAME FROM mydb.information_schema.tables
WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = 'mydb' AND TABLE_SCHEMA = 'master')

What I then want to do is... For each of these results:

Code:
select [COLUMN_NAME] from [TABLE_SCHEMA].[TABLE_NAME]
WHERE ID = 'test'

Is it possible to do this in one SQL command? Or do I manually have to do it for each in the list from my first query?

View 3 Replies View Related

SQL 2012 :: Compare Two Columns In Three Tables

Dec 16, 2013

I have one database with several tables in it (table 1, table2, table3). In each table is two colums (colum1 = a number (201220) and colum2 = a number (0.50). Now, both tables will have rows with the same data in colum 1, but colum two will have different numbers (different prices). My goal is to run a query that will compare both colums in all three tables, take the lower of the three based on colum 2 and spit out the row. Obviously, this would output all rows (around 175k). The point is to create a least cost spreadsheet (csv) file based on evaluating all three tables.

View 9 Replies View Related







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