Selecting The Column Description In 2005
Aug 3, 2006
I am migrating between SQL Server 2000 and SQL Server 2005 but hit a snag when attempting to write a query to display the column's description.
I used this code with SQL Server 2000 to get the "Description" data.
select o.name 'table' , c.name 'column' , p.value as 'description' , t.name 'datatype' , c.isnullable 'nullable?' , c.length, m.text 'default_text' from sysobjects o
join syscolumns c on o.id = c.id
join sysproperties p on o.id = p.id and p.smallid = c.colid
join systypes t on c.xtype = t.xtype
left outer join syscomments m on m.id = c.cdefault
order by 'table' , 'column'
How can I reproduce this with SQL Server 2005? I tried using the following which gives me a lot of the same data but not "Description":
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
Any help here would be greatly appreciated.
View 3 Replies
ADVERTISEMENT
Aug 13, 2007
Hi,
I want to set column description in .Net 2005, Please guide me.
thanx very much
View 1 Replies
View Related
May 18, 2005
Hello, it seems I forgot how to do it,
I want to create a query to get the column description table, I mean get structure without data.
I remember it was select DESC or something like that, donno if im right!!!
regards
View 6 Replies
View Related
Dec 17, 2013
I am having trouble bringing "description" column in to my sp and them at the out put. in the ppatient_status_mstr there is a column called“description” which has patient status such as “discharged” “termed collection” etc... How do I use the SP to bring this description into application? I am assigning it to @mytext. I am not sure how to declare “description” in the SP? Every time I use it I get errors like:
Msg 4104, Level 16, State 1, Procedure CheckStatus, Line 20
The multi-part identifier "pm.alerts_ind" could not be bound.
Msg 207, Level 16, State 1, Procedure CheckStatus, Line 40
Invalid column name 'description'.
Msg 207, Level 16, State 1, Procedure CheckStatus, Line 43
Invalid column name 'description'.
I want to say if user picks a description from application . show me that description in output. But I am having trouble how to use the description? I am not sure if my “IF” statements are correct either?
Here is the procedure
ALTER PROCEDURE [dbo].[CheckStatus] (@enc_id varchar(36) OUTPUT, @Mytext varchar(50) OUTPUT, @data_ind Char(1) OUTPUT)
AS
BEGIN
DECLARE
@alerts_ind char(1)
[code]....
View 20 Replies
View Related
Jul 23, 2005
Okay guys heres the senario.I have written a kick butt asp application that allows me to test sqlstatements and manage/display all my databases from the web but I havea feature I want to include that I can't figure out how. In EnterpriseManager, one of the column editable properties is the ColumnDescription. I can't find it in sql server itself. only in theEnterprise Manager. I need to access it using a sql statement so thatit will display in my table definiation view that I create in the aspapp.
View 1 Replies
View Related
Nov 16, 2001
HI
I have a big problem with SQL server2000. I can't get COLUMN DESCRIPTION.
I will print DATADICTIONARY from a DB, I can get all elemnts (table name, columns name, datatype, precision, scale, FK, PK, default value..) I missed only DESCRIPTION. How I can get it????
Medo
View 1 Replies
View Related
May 26, 2006
Hello everybody,
If I know the table name, is there any command to list all "Column Name" and the Columns "Description" as shown in the Design Table window.
Any feedback is welcome!
Thank you,
Edi
View 3 Replies
View Related
Aug 10, 2007
I dont know,how can I get column description from Sql Server 2005 and use it in vb.net 2005? would you please help me solve it?
View 1 Replies
View Related
Mar 25, 2008
I've tried looking in sys.syscolums and sys.syscomments, but I can't seem to find where the Description information is retain for a Field in the system tables -- any hints?
Thanks, Rob.
View 2 Replies
View Related
Jun 13, 2006
I have this easy sql for a drop down.
select code_value, desc1
from dbo.market_area_code
where code_value in ('1', '2', '4')
How can I add something to display something like
if code_value = '1' then display 'Blue'
if code_value = '2' then display 'Orange'
if code_value = '4' then display 'Red'
View 3 Replies
View Related
Jun 2, 2004
Hi, all..
I want to know the query to retrieve Column information that we can see from table Design view of Enterprise manager, such as Column name, Pk or not, FK table, Data Type, Null or not, Description(Specially Descrition).
Is there any sp for this or any query for this?
Thank you all...
View 14 Replies
View Related
Jun 12, 2008
How can I query/script Column or Table description (extended property MS_Description') in SQLserver 2005 db.
View 4 Replies
View Related
Feb 9, 2007
How can I get the SQL Server 2000 Column Description within ADO.NET 2.0?
BR / Chris
View 3 Replies
View Related
Jun 1, 2004
Hello All.
I have created a table with short name as the file name but have entered the description in the column box (when creating a new table - see attached). I would like to show this description information in query analyser. Can this be done? I used sp_help <table name> but it doesn't show the description.
Please advise. Thank you.
Best regards
View 2 Replies
View Related
May 1, 2008
I am trying to get the schema of database using the getschema method. However, the schema that is being returned does not include the description. I have added in table and column descriptions for some of my tables and columns but the dataset returned does not include the description column.
Any idea on how to get the description to be output?
Dim testCn As System.Data.OleDb.OleDbConnection
testCn = New System.Data.OleDb.OleDbConnection(step2.GetRevEngConn.ConnectionString)
Dim testDS As DataTable
testCn.Open()
testDS = testCn.GetSchema("TABLES")
testds.writexml("c:schematables.xml")
testDS = testCn.GetSchema("COLUMNS")
testDS.writexml("C:schemacol.xml")
testCn.Close()
- <Columns>
<TABLE_CATALOG>XDMDB_LAPTOP</TABLE_CATALOG>
<TABLE_SCHEMA>dbo</TABLE_SCHEMA>
<TABLE_NAME>PropertyValue</TABLE_NAME>
<COLUMN_NAME>property_value</COLUMN_NAME>
<ORDINAL_POSITION>6</ORDINAL_POSITION>
<COLUMN_HASDEFAULT>false</COLUMN_HASDEFAULT>
<COLUMN_FLAGS>230</COLUMN_FLAGS>
<IS_NULLABLE>true</IS_NULLABLE>
<DATA_TYPE>130</DATA_TYPE>
<CHARACTER_MAXIMUM_LENGTH>0</CHARACTER_MAXIMUM_LENGTH>
<CHARACTER_OCTET_LENGTH>0</CHARACTER_OCTET_LENGTH>
<CHARACTER_SET_CATALOG>master</CHARACTER_SET_CATALOG>
<CHARACTER_SET_SCHEMA>dbo</CHARACTER_SET_SCHEMA>
<CHARACTER_SET_NAME>iso_1</CHARACTER_SET_NAME>
<COLLATION_CATALOG>master</COLLATION_CATALOG>
<COLLATION_SCHEMA>dbo</COLLATION_SCHEMA>
<COLLATION_NAME>SQL_Latin1_General_CP1_CI_AS</COLLATION_NAME>
<COLUMN_LCID>1033</COLUMN_LCID>
<COLUMN_COMPFLAGS>196609</COLUMN_COMPFLAGS>
<COLUMN_SORTID>52</COLUMN_SORTID>
<COLUMN_TDSCOLLATION>CQTQADQ=</COLUMN_TDSCOLLATION>
<IS_COMPUTED>false</IS_COMPUTED>
</Columns>
View 2 Replies
View Related
Feb 2, 2008
I am trying to figure out how to set the Description of a Column in my database table by making a SQL function call. I know that I can go into Microsoft Studio Express and type in each desciption for each column. I just have about 1000 variables and each variable's description is in an Excel spreadsheet. I want to be able to build SQL code that will set each of the 1000 variables own description.
Thanks for any help.
Wesley Marshall
View 4 Replies
View Related
Jul 17, 2013
I have a subselect that should be working but doesn't. Been at it too long today.
DECLARE @Calendar1 AS DateTime
SET @Calendar1 = '{{{ Please choose a start date. }}}'
SELECT
('0' + CONVERT (varchar (10), W.WorkerID)) AS VendorID,
(W.FirstName + ' ' + W.LastName) AS VendorName,
(W.FirstName + ' ' + W.LastName) AS Contact,
[code].....
Where did I go wrong?
View 3 Replies
View Related
Jul 17, 2015
I have a SQL Query issue you can find in SQL Fiddle
SQL FIDDLE for Demo
My query was like this
For Insert
Insert into Employee values('aa', 'T', 'qqq')
Insert into Employee values('aa' , 'F' , 'qqq')
Insert into Employee values('bb', 'F' , 'eee')
Insert into Employee values('cc' , 'T' , 'rrr')
Insert into Employee values('cc' , 'pp' , 'aaa')
Insert into Employee values('cc' , 'Zz' , 'bab')
Insert into Employee values('cc' , 'ZZ' , 'bac')
For select
select col1,MAX(col2) as Col2,Max(Col3) as Col3
from Employee
group by Col1
I supposed to get last row as
cc Zz bab
Instead I am getting
cc Zz rrr
which is wrong
View 8 Replies
View Related
Jan 12, 2007
I have SQL Server 2005 Epress and I want to get the field's dscription..
View 1 Replies
View Related
May 29, 2008
Hi
I have a gridview with a sql data source and a few drop down lists where i choose values to sorth what i would like to retrive from the table. The problem im facing is that when i pass values to the database, i can sort out and retrive my items when both my listboxes have selected values.
But when i want to select everything from the table where the column = clumn or cloumn = value i dont get anything back.
I have 2 dropdown lists. One for category and one for location. These are populated drom the database by selecting the column and retriving destinct values. I have a function that fires when one of the dropdown lists are changed witch changes the sql datasource select value so it retrives items sorted by the selected categories and locations.
Here is the function:
protected void DropDownList_Change(object sender, EventArgs e) { string Category = DropDownListKategori.SelectedValue.ToString(); string Location = DropDownListEtabl.SelectedValue.ToString(); SqlDataSource1.SelectCommand = "SELECT ARTNR, ARTTYP, AKTIVITET, DATUM, KUND, PLATS, KOMMENTAR FROM ARTIKEL WHERE ARTTYP = @ARTTYP AND PLATS = @PLATS"; SqlDataSource1.SelectParameters.Clear(); SqlDataSource1.SelectParameters.Add(@"ARTTYP", Category); SqlDataSource1.SelectParameters.Add(@"PLATS", Location); SqlDataSource1.DataBind(); }
The dropdown lists have an option to not retrive values by category or location. I have set that value to PLATS and ARTTYP thinking the query would would retrive everything if these were selected
SELECT ARTNR, ARTTYP, AKTIVITET, DATUM, KUND, PLATS, KOMMENTAR FROM ARTIKEL WHERE ARTTYP = ARTTYP AND PLATS = PLATS
however if both these drop down lists are set to not filter, my query gets nothing in return. And if one of the lists has this selected and ther other one has a value, say a location or something nothing is retrived either. However if both columns have something selected i do get values returned.
My speculation is that the query beeing run when this happens is that it is trying to find columns having the value ARTTYP or PLATS (category or location), as a string and actually seeing it as Im trying to retrive values where column = column.
Any suggestions on how i can make my query run as intended or is there another problem Im not seeing.
View 7 Replies
View Related
Feb 17, 2004
I want to know how to select a Column without selecting a column name
View 6 Replies
View Related
Jun 5, 2006
Mike writes "Hi,
I am a beginner with TSQL and I hope this is not a silly question :-)
Lets say I have a table with 2 columns, 1 a primary key identity field with increment 1 and the other a char.
EG:
ID ANIMAL
---------
1 CAT
2 DOG
3 PIG
4 RAT
5 PIG
6 DOG
7 DOG
.
.
And so on with many entries
How do I return a selection of rows that have the contents of the ANIMAL field matching 1 or more times
EG:
From above table I want to return rows 2,6 & 7 and 3 & 5 ONLY and not 1(CAT) and 4(RAT) because they only occur once.
In my real life situation I have unknown numeric data in field 2 but the principal is the same.
How do I do this?
Thanks in Advance
Mike"
View 2 Replies
View Related
Jul 23, 2005
Hi :From a crystal report i get a list of employee firstnames as a stringinto my store procedure. Why is it comming this way ? hmmmmmm it's aquestion for me too.ex: "e1,e2,e3"here are my tablestblProjectsProjectId123tblEmployeeemployeeId FirstName1 e12 e23 e3tblProjectsToEmployeeProjectId employeeId1 11 21 32 12 23 13 23 34 14 3i need to find out the project ids all 3 of these employees worked on.so the out put i need isprojectId13How can i get it ????????????now i can use replace command to format it to a OR clause or ANDclauseSET @string= 'employeeId =' + '''' + REPLACE('e1,e2,e3',',',''' ORemployeeId = ''') + ''''some thing like this.OR clause will give me all 4 projects.in('e1','e2','e3') will give me all 4 projects.of cause AND command will not give me any.other method i tried was adding the employee table 3 times into thesame SQL string and doing some thing likeWHERE (empTable1.Firstname ='e1' AND (empTable1.Firstnamein('e2','e3'))AND (empTable2.Firstname ='e2' AND (empTable1.Firstname in('e1','e3'))AND ...and goes alone. this gives me some what i needed. but it's a verymessy way of doing it, because i get a comma seperated stringparameter i have to construct the sql string on the fly.any help or direction on this matter would greatly appreciated.thankseric
View 3 Replies
View Related
May 4, 2007
Greetings,
I have a dataset something like this:
Loc Value Time
A 3 1AM
A 8 2AM
A 2 3AM
A 1 4AM
...
B 8 1AM
B 1 2AM
...
When we collapse the Loc group I want to show the Max vlaue and the time it
occurred.
A 8 2AM
B 8 1AM
What is the expression for the Time column of the table to ensure
the time of the maximum value is shown?
Thanks
View 8 Replies
View Related
Feb 19, 2004
Hello,
Is it possible to get a list of rows from Multiple tables which have the same Column Name. I have 3 tables which comtain similar info
and I want to get a list of Names
the structure is
ID;Name;Address;Phone No.
I was thinking something along the lines of SELECT Name FROM TABLE1,TABLE2, TABLE3
But this does not work.
Is there a nice way of doing this with SQL or should I do code outside the SQL DB
View 4 Replies
View Related
Jul 23, 2005
HiBeen at this for 2 days now.Each business has several packages which they can sort usingsort_order.I'm trying to get one package for each business(that I can do), howeverI want it to be the one with the lowest sort_order valueAs you can see below the first record has sort_order=5 when it shouldbe 1.Most of the sort_order columns will be zero by defaultAny help so i can get on with my life!CheersGary------------Current select-------------------SELECT *FROM dbo.testAccommodation_Packages T1WHERE (NOT EXISTS(SELECT *FROM testAccommodation_PackagesWHERE business_id = T1.business_id AND Package_ID < T1.Package_ID))--------------results:-----------------------Package_IDbusiness_iditem_namesort_order123rd Night FREE ...5113Donegal Town ... 0204Executive ...0--------------To recreate----------------------CREATE TABLE [testAccommodation_Packages] ([Package_ID] [int] IDENTITY (1, 1) NOT NULL ,[business_id] [int] NULL ,[Item_Name] [nvarchar] (300) NOT NULL ,[sort_order] [int] NULL CONSTRAINT[DF_Accommodation_Packages_sort_order] DEFAULT (0),)-------------------------------------------------INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('1','2','3rd Night FREE when you stay 2 nights MIDWEEK (129 EuroPPS)','5')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('2','2','Selected Donegal Town Hotel Weekend Sale - 2 B&B and 1Dinner Only 129 PPS','4')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('3','2','2 Night Specials -Jan, Feb & Mar 2 B&B and 1 Dinner 149Euro PPS','3')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('4','2','Easter Hotel Breaks in Donegal Town - 2 B&B + 1 D169pps','2')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('5','2','2005 Bluestack Hillwalking, 2 nights B&B, 1 Dinner, 5course Lunch 159 Euros PPS (~109 Stg)','1')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('6','2','April Pamper Package - 2 Night Special ONLY195pps','10')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('7','2','Discount Hotel Prices for 8th & 9th April Only 119PPS','7')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('8','2','Golden Year Breaks in Donegal - 4B&B + 2 Dinner229pps','8')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('9','2','Hotel Summer Breaks Sale in Donegal - 2B&B + 1 Dinner169pps','9')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('10','2','STAY SUNDAY NIGHTS FOR 25PPS','6')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('11','3','Donegal Town Midweek Special 99 Euro PPS 3 Nights B&B','0')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('12','3','Bridge Weekend 2 nights B&B 79 Euro PPS (approx 55Stg) Double Room','0')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('13','3','Donegal Spring Weekend Specials 2 B&B 1 Dinner109.00euros pps','0')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('14','3','Valentines Weekend 2 nights B&B and 1 four coursegourmet dinner 99Euro PPS','0')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('19','3','Golden Years Break.40% OFF 4 nights B&B129.00p.p.s.','0')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('20','4','Executive Celebration Offer 1 night B&B + Dinner 139 PPS','0')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('21','4','Watercolour Painting Break 3 B&B Full Board andTuition 335 PPS','0')
View 1 Replies
View Related
Apr 17, 2008
Hi,
I am having two tables Products and Transaction
In products I have ProductID and Description. (10 Records)
In Transaction I have ProductID, Lot, Quantity and ListID . ( 4 Million Records)
When I use the inner join between these tables as below query its taking lot of time to give output.
select ProductID, Desc, Lot, qty from Products inner join Transactions on Products.ProductID = Transactions.ProductID
where ListID = '9090909'
otherwise, if I use the below query its takes very few milli seconds to give output but I am not able to get the description from the product table.
select * from Transactions TR where TR.ListID= '9090909' and TR.ProductID in
(select NDC from Products where Products.ProductID = TR.ProductID)
Any from can help me to get the description too at very few times.
Regards,
View 1 Replies
View Related
Mar 10, 2003
I have a table with one row for each test a user has taken, with columns for userid, score, and test date. I have a query that gets the highest score and the date of the latest test for each user. Easy. But how can I also get the score achieved on the latest test for each agent?
Thanks,
John
(By the way, I've been looking for a good SQL mailing list to ask this question and have been unsuccessful. If there's a better forum than this for this type of question, please let me know).
View 6 Replies
View Related
May 5, 2004
Hi, I have a question regarding how to insert one column values into a table by selecting from different table..Here is the syntax..
------------
insert into propertytable values (select lastvalue+incrementby from agilesequences where name='SEQPROPERTYTABLE', 13926, 0, 4, 1, 451, 1, 8, 1)
the first column in the propertytable will be... select lastvalue+incrementby from agilesequences where name='SEQPROPERTYTABLE'
How do I do that..Help PLZ..
View 3 Replies
View Related
Feb 16, 2006
Godwin writes "Hello,
Heres my question..
I have 2 tables.2 paticular columns exist in both the tables.
I want to be able to select those 2 columns on the 1st table and insert them on to the same 2 columns on the 2nd table.
Now,this 2nd table has another 3 columns that exist in another table.I would like to take those 3 column values from that 3rd table and insert it into the 2nd table by modifying those existing records in the 2nd table.In the 3rd table,there will be around 5 records...I want to copy the existing records 5 times in the 2nd table and insert the 3rd tables rows inside the 2nd table in that respective column for 5 rows.
I hope you understand what I mean...Im sorry for really confusing..
Please help me
Thanks
Godwin"
View 1 Replies
View Related
May 13, 2014
I've got an OLEDB DB2 linked server to a db2/AS400 instance and selecting from a table on the server has never caused problems before. One of the columns is a large text field. If I select all the columns but the large text field, it returns as normal, but including the large text field now, I get:
"Transport error: shared memory provider error: 0 - no process is on the other end of the pipe"
The largest entry in the text field is about 5k characters, and there don't appear to be any strange characters.
View 0 Replies
View Related
Feb 13, 2008
I have a report with a date type parameter. Depending on the value return by this date type parameter the dataset will return either the credit, deposit or process date. How do I go about coding it so that it will dynamically select the right column in my query for my dataset?
Sincerely appreciate all the help I can get.
Thanks in advance.
View 11 Replies
View Related
Jun 25, 2015
I have questions and answers from one table, I need to select questions as column names and answers column values as the results for the questions column.
View 28 Replies
View Related