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!
i have a table with a few hundred columns. Each SELECT statement, I list each of the columns, this is taking lots and lots of space and it is difficult to review the code due to its length...Below, I have to list out every column when I only want to use case logic on 1 column. In the next step I will have to list out every single column again
SELECT
ACCT1_NO
,ACCT1_DT
,ACCT1_RISK
,ACCT1_RISK_WEIGHT
,ACCT1_CUSTOMER_NAME
,ACCT1_CUSTOMER_ADDRESS
,ACCT1_CUSTOMER_ST = CASE WHEN ACCT1_CUSTOMER_ST = ' ' THEN 'DC' END
,ACCT1_CUSTOMER_CITY
,ACCT1_CUSTOMER_ZIP
--,THIS CONTINUES DOWN FOR ANOTHER 150 OR SO ACCTS.
INTO #A1
FROM STAGE.CUSTOMER_ACCTS
Is there a way I can tell SQL to take all of the columns and then list the column where I want to do my case statement. Something like the code below
(which will fail as ACCT1_CUSTOMER_ST will be listed twice.
SELECT *
,ACCT1_CUSTOMER_ST = CASE WHEN ACCT1_CUSTOMER_ST = ' ' THEN 'DC' END
INTO #A1
FROM STAGE.CUSTOMER_ACCTS
Hi everybody,
Is there anyway we can find the list of tables in each filegroups by writing a sql query..
Thanks
muthu
Hi,
I want to create a nested SP which will dump out the results of All Temp Tables that are currently created in the session. The purpose of this is for createing a generic debugging SP which will write the contents of all temp tables to a debug table (when a certain flag is set).
I need to know how to:
- Get a list of all temp tables created
- Get a list from each temp table of the columns created.
Hope someone can help!
Cheers,
Andrew
I am trying to create a calendar style report that will have 12 months (as columns) and store opening listing in rows. I have created a matrix, but the problem that I have is that the store opening listing displays in the right period, but they are not in any order. I would like to have the openings always on top, right under the header in the matrix. Now I have them scattered randomly all over the matrix. I tried numerous way of sorting and that does not work.
I am attaching a sample of what I would like to accomplish (months are columns).
script to get the list of views in a database, involving tables from other databases?I AM using SQL server 2014
View 2 Replies View RelatedI 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.
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 RelatedWhen 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
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
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
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 RelatedIs there a way to change the collation against all the tables and columns within a database?
View 2 Replies View RelatedI 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 RelatedHow to search all columns of all tables for a keyword?:o
that in MS sql
thanks in advance..
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.
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
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
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 Relatedhow 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
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.
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
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 RelatedHi,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 RelatedHi 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.
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
Hi fellas.
I need you to answer a question:
What should I do and which SQL Server tool do I have to use in order to find a job listing on the database ?
Thanks in advance,
Abrahão.
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 RelatedI 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
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
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 RelatedI'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
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!