What Is The Command To See The Attributes Of A Table.
Apr 7, 2008
In query analyzer, what is the command to tell me the attributes of the entities in a table? In oracle I can use the describe command. I know their is a way to do it in Query analyzer but I can't remember how. Also I can look visually by expanding the node of the table. But if I can do this through the command line in query analayzer, it is sometimes quicker.
Example. I want to find out about a table named "Employee"
What command would I type that would tell me all of the columns/attributes in that table, and the data types which they are?
Bill
View 2 Replies
ADVERTISEMENT
Jun 30, 2015
I am working in SQL Server Master Data Services Version 11.0.5058.0 (SP 2).
I have been asked to group all the financial attributes together. When I move one of the attributes up using the arrows, it works good jumping over one attribute at a time. Then I reach a section of attributes where it leap frogs over 24 attributes.
It appears these 24 attributes are in a subgroup but there are no attribute groups and I removed the subscription view from the entity. If I move one of the 24 attributes in the group, it moves it outside of the 24 attributes.
This is under leaf member attributes. There are no collection or consolidated groups.
View 2 Replies
View Related
May 24, 2015
I'm using a DW from Northwind database to build a cube to do some analitical taks. I already create the cube and now I am "cleaning" the dimensions. I'm having some difficults to understand the logical off this part. The reason is that When I create the Data Source View, I only import the Foreign Keys that connect the Dimensions to Fact_Table. I have to drag the attributes of Dimension from Data Source View to the tab attributes?
Imagine this:
I have the following dimension:
Dim_Customer:
Customer_ID
Name_Customer
Job_Function
Date_of_Birth
Contact
Address
City
Country
When I create the cube only Customer_ID appears in attributes tab, it's normal?
One more question:
I don't want to create a hierarchy like:
Customer ID -> Name_Customer
Customer ID -> Date_of_Birth
Customer ID -> Address
Customer ID -> City
Customer ID -> Country
My idea is to create the following hierarchy:
Name_Customer -> Date_of_Birth -> Address -> City -> Country
But the first hierarchy that I show is always appears to me. Do you know what is happens?
View 2 Replies
View Related
Nov 11, 2004
Hello all-
I have a specification table that has some attributes defined.
SpecId - Id of the specification
Attribute - Attribute of the spec. (Like Color, HP etc)
Value - Is the value of the attribute
Then I have a car table that actually has information about the cars. Intention is to take each specification and match the cars that match the specification. If the car has more attributes than the spec, we ignore the extra attributes for the match. But if the car has less attributes, we don't even consider the car as a match (even if the attributes present, match). To summarize, the car's attributes should be >= spec's attributes.
The code I have below is bad because I am joining the same tables twice. In addition, it fails in the condition "the car's attributes should be >= spec's attributes"
Any help is greatly appreciated.
DECLARE @Specification TABLE
(SpecId VARCHAR(10),
AttributeVARCHAR(100),
ValueVARCHAR(100))
DECLARE @Car TABLE
(CarName VARCHAR(10),
AttributeVARCHAR(100),
ValueVARCHAR(100))
INSERT INTO @Specification VALUES ('S1', 'Type', 'Sedan')
INSERT INTO @Specification VALUES ('S1', 'Transmission', 'Auto')
INSERT INTO @Specification VALUES ('S1', 'HP', '220')
INSERT INTO @Specification VALUES ('S2', 'Type', 'SUV')
INSERT INTO @Specification VALUES ('S2', 'Transmission', 'Manual')
INSERT INTO @Specification VALUES ('S2', 'HP', '300')
INSERT INTO @Car VALUES ('Accord', 'Type', 'Sedan')
INSERT INTO @Car VALUES ('Accord', 'Transmission', 'Auto')
INSERT INTO @Car VALUES ('Accord', 'HP', '220')
INSERT INTO @Car VALUES ('Accord', 'Color', 'Black')
INSERT INTO @Car VALUES ('Escape', 'Type', 'SUV')
INSERT INTO @Car VALUES ('Escape', 'Transmission', 'Manual')
INSERT INTO @Car VALUES ('Escape', 'HP', '300')
INSERT INTO @Car VALUES ('Explorer', 'Type', 'SUV')
INSERT INTO @Car VALUES ('Explorer', 'Transmission', 'Manual')
SELECT DISTINCT Spec.SpecId, Car.CarName
FROM @Specification Spec
INNER JOIN @Car Car
ON Spec.Attribute = Car.Attribute
AND Spec.Value = Car.Value
WHERE Spec.SpecId NOT IN (SELECT Spec.SpecId
FROM @Specification Spec
LEFT OUTER JOIN @Car Car
ON Spec.Attribute = Car.Attribute
AND Spec.Value = Car.Value
WHERE Car.CarName IS NULL)
View 2 Replies
View Related
Nov 23, 2006
Bit of a design question as I'm interested to know if anyone's done anythign like this...This is my main table (ish) Thing(ThingId, Ref)I then need to be able to give this "Thing" any number of attributes. Thing1 - Type:Red, Location:LondonThing2 - Type:Blue, Height:400, Width: 300Thing3 - Height:500, Location:Norwich But I have no idea how to model this in the database - it needs to be in such a way that I can add a Thing and all its attributes in one database hit basically (is there a stored procedure you could pass an array into?) My initial thoughts were to have Thing(ThingId, Ref) Attribute(AttributeId, ThingId*, AttributeTypeId*, Value) AttributeType(AttributeTypeId, Description) Is that completely mad? It seems like quite a lot of data accesses to enter a ThingIt could be Thing(ThingId, Ref, Type, Location, Height, Width) but then when "Thing - Color:White" comes along the model is stuffed Any ideas? (hope that makes sense)
View 3 Replies
View Related
Aug 1, 2000
. When I copy tables from one database to another (Using DTS Wizard) I lose my settings .. primary keys + default values !!
Any help would be appreciated..
.
Thanks
View 2 Replies
View Related
Aug 8, 2007
I must be missing something somewhere...
I have a simple table with three fields: ID, LastName, FirstName. The ID is defined as the PK. In the table is a record of "12345, Smith, John". The incoming flat file has a record of "12345, Smith, Johnny".
In the SCD transform, the ID is the business key, and Last Name and First Name are defined as historical attributes.
During the load, the SCD transform correctly sends the data down the right path, but the insert fails with a primary key violation - as I would expect since it's trying to create a new current record.
How do I get around this problem without removing the PK ???
thx
View 9 Replies
View Related
Oct 29, 2014
I'd like to create a table that will store different order items. Several order items make up one single order. Order items can have 0 or more children (max depth will never be deeper than one). Order items can have up to 150 attributes/values. The way I think this should be done is using XML column instead of the EAV type of model. My table structure currently looks like this:
* child_order_item_id (PK)
* parent_order_item_id (FK to child_order_item_id)
* order_id (FK to Order table)
* product_id (FK to Product table)
* price
* attribute_XML
How my attribute_XML should look like or how to validate the xml.
View 2 Replies
View Related
Dec 19, 2007
i have a question if anyone can help please..
i have need to create a database table as a go-between, where users will upload info. then on a backend page admin will make some data settings, then transfer it to the proper, permanent tables.(all this to avoid expecting users themselves to upload files to proper places).
can i use a basic SQL INSERT statement to move from one table directly to another without the use of parameters? for example,
INSERT INTO tblOne (fieldOne,fieldTwo,fieldThree) VALUES (tblTwo(fieldOne,fieldTwo,fieldThree)
if so, what is the syntax? and if not, then what would i have to do, just use some go between variables or objects? perhaps a DataSet to pull the values, then use same DataSet to load the values into new table, then a simple DELETE statement on the intermediate table?
i mean, i can work my way through this, but if anybody has gone through a similar situation i sure could use any pointers or tips to keep it as clean and simple as possible.
thanks all!
View 3 Replies
View Related
Mar 27, 2006
Hi all,
I am new to this site and I hope anyone out there can help me. I was tasked to change the constraints of my existing table. Lets call it table1. This table has an attribute that needs to take the value of another attribute of another table ( let's call it tables2) and that attribute must satisfy a certain expression ( I suppose I can isolate it by using the select statement ).
Anyone know how to get this done.
Please advice.
Thanks and appreciated.
View 4 Replies
View Related
Jun 3, 2008
hi
Can We Use Truncate Command on a table which is Refernced by Foreign Key?
View 5 Replies
View Related
Nov 22, 2007
Hi AllAm new to sql server to sobear with me, have checked around but cantfind an answer to this.I want to change fieldname from nvarchar(50) to nvarchar(255) as partof a scriptAm running this command :ALTER TABLE MYTABLE MODIFY fieldname nvarchar(255) nulli test this out in the sql window but cant get it to run error is:Msg 102, Level 15, State 1, Line 1Incorrect syntax near 'MODIFY'.Can anyone help ?
View 1 Replies
View Related
Jul 20, 2005
Hi,I'm trying to run the ALTER TABLE command using a dynamic string for thetable, like so:DECLARE @TableName CHARSET @TableName = 'Customers'ALTER TABLE @TableNameADD ...blahIs this possible? We know this works:ALTER TABLE Customers ADD ...blahIt looks like I need a way to convert the CHAR value to a literal or perhapseven a table ID?Thanks in advance,Paul
View 3 Replies
View Related
Sep 8, 2006
hi! can i create a table based on a join command? if it is how could it be done?
thanks!
View 2 Replies
View Related
Jul 25, 2006
I have a little application that I have designed where I need to be able to execute create table and create function comands against the database.
It seems that it does not like my sql file. Does anyone know of a different method of doing this?
Error message
Line 2: Incorrect syntax near 'GO'.
Line 4: Incorrect syntax near 'GO'.
Line 8: Incorrect syntax near 'GO'.
'CREATE FUNCTION' must be the first statement in a query batch.
Must declare the variable '@usb'.
Must declare the variable '@usb'.
Must declare the variable '@i'.
A RETURN statement with a return value cannot be used in this context.
Line 89: Incorrect syntax near 'GO'.
Line 91: Incorrect syntax near 'GO'.
Line 94: Incorrect syntax near 'GO'.
Protected Sub Install()
Dim err As String = ""
While err.Length < 1
' Dim your StreamReader
Dim TextFileStream As System.IO.TextReader
'Load the textfile into the stream
TextFileStream = System.IO.File.OpenText(Request.PhysicalApplicationPath & "Scripts .sql")
'Read to the end of the file into a String variable.
executesql(TextFileStream.ReadToEnd, err)
err = "Susscessful"
End While
If err = "Susscessful" Then
Response.Redirect("Default.aspx")
Else
Me.lblError.Text = err
End If
End Sub
Private Function executesql(ByVal s As String, ByRef err As String) As Boolean
Try
Dim conn As New Data.SqlClient.SqlConnection(GenConString())
Dim cmd As New Data.SqlClient.SqlCommand(s, conn)
conn.Open()
cmd.ExecuteNonQuery()
conn.Close()
Return True
Catch ex As Exception
err = ex.Message.ToString
Return False
End Try
End Function
Example sql file
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MyFunc]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[MyFunc]
GO
CREATE FUNCTION [dbo].[MyFunc]
(
-- Add the parameters for the function here
)
RETURNS varchar(1000)
AS
BEGIN
-- Declare the return variable here
DECLARE @Result varchar(1000)
-- Add the T-SQL statements to compute the return value here
-- Do something here
-- Return the result of the function
RETURN @Result
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
View 1 Replies
View Related
Dec 5, 2007
I have several databases that are used to collect data for differentclient-projects. My intention was to collect the data, and administermySQL so each client could see only their data. Each of thesedatabases used to collect data have the same tables. There are otherdatabases which are available to all the users which contain commoninformation.The command "show databases" lists all of the databases (visible tothe particular user) and not the one's I would like to display (thedatabases used to collect data). Is there a way to nest a singlemySQL command to show only the databases that have the same particulartable?
View 1 Replies
View Related
Jun 22, 2007
hi
can someone please explain the following please
why does this fail with specified owner testdb does not exist or you do not have permissions
create testdb.testtable (
testval int
)
yet if i create testdb and then run the following against this new db
create testtable (
testval int
)
command command completes successfully......what am i not understanding or doing wrong?
thank you
View 3 Replies
View Related
Sep 21, 2006
Hi,
I have a data flow task that performs an "upsert" by directing successful rows from a Lookup to an OLE DB Command that updates rows and unsuccessful rows (Lookup error output) to an OLE DB Destination for insertion.
The problem is that execution hangs when both tasks update/insert into the same table (execution is still hung after 20 minutes). Modifying the OLE DB Destination to insert into a different table succeeds (execution completese within 2 minutes). Replacing the OLE DB Destination with a Row Count transformation also works.
Could this be due to a table-locking issue? Any suggestions?
Thanks
ray
View 6 Replies
View Related
Jan 22, 2003
I want to access all attributes of a record in a table without knowing the name of those attributes,how can I do by using transact-sql?
Thanks.
View 1 Replies
View Related
Aug 24, 2004
I am using db keyowrds as attributes in my table such as from, to and date. These are enclosed in [] in SQL Server Enterprise Manager. I'm just asking if doing this is a bad idea? Reason being these are the most applicable names for these attributes but don't want to run into problems further down the line.
Thanks,
_z0
View 5 Replies
View Related
Aug 27, 2007
Have a report that I want to keep certain attributes and as long as the report contains this certain attribute, bring all other attributes with it. Better with an example. In this report I am specifically looking for attribute "Alcohol", if I find this attribute I want to include all others that fit with this record's Primary Key which could include, "Drugs","Arson","Vandalism", etc. Problem is when I try to use a paramater or filter I get the "Alcohol" Attribute but not the "Drugs","Arson","Vandalism", etc. Conversely since I dont have any filter/paramater set I get everything even if it does not include "Alcohol"
suggestions??
thansk in advance
km
View 3 Replies
View Related
Mar 22, 2004
I want to ask something because i need.
Any SQL/T-SQL command inside MsSQL Server 2000 can show the description of {all table columns or specified table columns} of specified table inside specified database?
can you teach me how to do and any example(s)?
View 2 Replies
View Related
Aug 24, 2000
Hi
Does anyone know how i can grant select permission on more than one table in the same database using a single grant commmand....its painful to use grant statement seperately on each table
thanks and regards,
reshma
View 3 Replies
View Related
Apr 15, 2004
is there a command in ms sql server 2000 equivalent to this oracle table copy command?
create table myTable_bak as select * from myTable;
View 1 Replies
View Related
Nov 19, 2014
I am debugging a DB maintance script which creates a table of index maintainance commands which are created separately for each index according to the level of fragmentation and other factors.
For the debugging process, I'm looking for a way to parse each command in the table without actually running them to locate any syntax errors. In other words, as if you clicked the blue check on each one.
Does such a function exist in SQL 2008 (the version I'm doing this on) or other versions?
View 3 Replies
View Related
Sep 16, 2009
I have a primary key named pk, name and surname fields. I need to insert to my table names and surnames.
INSERT INTO People (name,surname) VALUES ('john','black');
I'm not giving pks database gives is auto. But my problem is i need to know the pk that my database gave. Because i have lots of duplicate records. Is there any way to retrieve pk while inserting to table.
View 7 Replies
View Related
Jun 22, 2007
Here I am creating a temp table with $ summations that I can later join with an employees table that I'm dumping into a flat file.
select
e.employee_no,
sum(p.fit) as sumfit,
sum(p.fica) - sum(p.medicare) as sumfica,
sum(p.medicare) as sumedic,
sum(p.fit_earnings) as pearnings,
sum(p.fit_earnings) as tearnings
into #earntable
from employees as e
left outer join pay_summary as p on e.employee_no =p.employee_no
where e.employee_no = 817 and
dateadd(d, 0, datediff(d, 0, p.dated)) between '20061231'and '20070401'
group by e.employee_no
select * from #earntable
When I go to look at the contents of the #earntable with the above select, I get this:
ODBC error 214 Procedure expects parameter '@handle' of type 'int'.
(42000)
The datatypes I'm trying to select into the temp table are all
numeric 12 except employee_no char 10.
What am I missing? A drop table statement?
View 6 Replies
View Related
Feb 23, 2006
I have an existing SQL 7 server named HHARBR. HHARBR has a database namedSPR with a table named "reportname" in it, the table has the name HHARBRembedded the table data.I migrated the HHARBR database SPR to a second server called HHARBR2. When Ilook into the table in HHARBR2 I still see the name HHARBR in the data.What command can I run on HHARBR2 to change the table entries of HHARBR tothe new server name of HHARBR2?I prefer something I can run in the Query AnalyzerSource server: HHARBRSource Database: SPRSource Datbase Table: reportnameDestination Server: HHARBR2Destination Databse: SPRDestination Table: reportnameSummary:Change wrong server name in destination server to reflect the destinationsserver name
View 2 Replies
View Related
Jul 20, 2005
Hi All,I am new to MS SQL Server.I am using MS SQL 2000.I have a problem increating a table by using Select command.I have table called "test"and i want to create another table with the same structure and rows.Itried with the following commandcreate Table test1 as select * from test;But it give an syntax error.I have tried the same command in Oraclebut i was working.Does MS SQL 2000 Server supports this kind of Query.Please help me to solve the problem or any other methods to performthis operation.Thanks in AdvanceKevin
View 2 Replies
View Related
Jan 7, 2008
Hi all,
I'm just testing an SSIS package and am having issues with dealing with locked records.
my situation is as follows:
my source table is oracle, my destination table is in SQL server. my data flow is a very simple update with a lookup transformation and then two OLEDB commands for update and insert.
On each of the OLEDB commands I have set the "command timeout" to 5 seconds (just for testing purposes). also each OLE DB command has a failure path that outputs to a flat file. I'm expecting that if the destination table/records is/are locked then after 5 seconds the record will be output to the flat file.
so to test this I begin a transaction on the destination table and don't commit it. then I start the SSIS job. it doesn't appear to even get to the OLE DB commands. it appears to stop at the beginning of the data flow task. the output window shows this:
"Information: 0x40043007 at Import from Phoenix, DTS.Pipeline: Pre-Execute phase is beginning."
but it just hangs there indefinately. the progress tab tells me that it get's from the validating stage and past the prepare for execute stage but hangs on pre-execute - 0 percent.
I've put the command timeout = 5 on everything that I can find. I've mucked arround with all the possible "validateExternalMetadata" properties even though I only guessed that it may be the cause. is there anything that I'm missing? where should I look next?
(yes it does work perfectly when there is no transaction locking the target table)
Cheers,
Andrew
View 21 Replies
View Related
Jan 3, 2008
I was working on my Lab Environment and i had a DB which was mirrored in synchronous mode without witness server. I truncated one of the tables in this DB and did a failover to the mirror server. The table got truncated in the mirror too. Though we know that truncate is a non logged operation how does this mechanism work? Can someone please expain me??? Thanks in advance. Your replies will be very much helpful to me.
View 4 Replies
View Related
May 24, 2006
I have a few tables that have an disabled attribute using a BIT datatype. A lot of my queries on the front end look like:
SELECT *
FROM TableA
WHERE disabled <> 1
There's usually some other constraints on the query (get TOP 10 and greater than a certain date for example). Right now my tables are very small (only a couple thousand rows). I don't anticipate these tables having more than 100,000 rows.
Right now let's say there's only a CLUSTERED INDEX on the date field, and regular INDEXES on the identity field and perhaps some other UNIQUE name in the table.
Unless I am doing ranged queries on the CLUSTERED INDEXED field, I'm going to be performing table scans almost every time, right?
This sort of goes along with another question:
Say you run the following (SQL Server):
CREATE TABLE TestA (
[id] INT IDENTITY (1, 1) PRIMARY KEY,
disabled BIT DEFAULT 0
)
GO
INSERT INTO TestA (disabled) VALUES ('0')
GO
INSERT INTO TestA (disabled) VALUES ('0')
GO
INSERT INTO TestA (disabled) VALUES ('1')
GO
INSERT INTO TestA (disabled) VALUES ('0')
GO
INSERT INTO TestA (disabled) VALUES ('0')
GO
INSERT INTO TestA (disabled) VALUES ('0')
GO
INSERT INTO TestA (disabled) VALUES ('0')
GO
INSERT INTO TestA (disabled) VALUES ('1')
GO
INSERT INTO TestA (disabled) VALUES ('0')
GO
INSERT INTO TestA (disabled) VALUES ('0')
GO
INSERT INTO TestA (disabled) VALUES ('1')
GO
INSERT INTO TestA (disabled) VALUES ('1')
GO
INSERT INTO TestA (disabled) VALUES ('0')
Since [id] is a PK there will be a CLUSTERED INDEX placed on it. My question is; what does the optimizer do when you perform the following query?
SELECT TOP 3 *
FROM TestA
WHERE disabled <> '1'
My assumption is that since there's a CLUSTERED INDEX it will simply iterate through every tuple and check to see if disabled is not '1'. If my assumption is correct then these kind of boolean fields aren't a big deal if TOP queries are performed on a CLUSTERED INDEX.
So I guess what I am getting at is: Are bit attributes a sign of bad design? As tables get larger will performance degrade significantly? Would a better design be to have a seperate table of disabled items (which may result in large NOT IN subqueries)?
Any information on his would be greatly appreciated.
View 1 Replies
View Related
May 14, 2008
I have a question about storing the history of particular objects in a database. For example, if I had a table of "People" which had fields "PersonId", "Name", "PhoneNumber", "Height", "Weight", "Proffession" the data in every field stored for each person can change over time, except for the "PersonId", of course, which is why it is included.
I would like to be able to view a persons attributes at any point in time and therefore need to maintain a history. The currenct approach in place is to archive images of the whole table at certain points in time, which is unacceptable as it misses some changes, is not very accessible and also stores data which does not change.
My solution would be to created seperate tables for each changing attribute and have corresponding date for each change. For example, for phone numbers have a table "PeoplePhoneNumbers" with fields "PersonId","PhoneNumber" and "ChangeDate". A few shortcomings I can see in this approach is that firstly there will be many tables, one for each changing attribute, which can be in far greater number than those mentioned. Secondly, joins will have to created between every attribute table to get the orignal single table form, although I don't see this as a very important issue.
I am wondering; is there a more elegent way to structure for objects of this changing nature, or is having seperate tables for each changing attribute the best solution? I'm sure this is a very common issue. Thanks very much for the help,
Mike
View 18 Replies
View Related
