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'
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
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
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]....
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 RelatedHI
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
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
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 RelatedHi,
I want to set column description in .Net 2005, Please guide me.
thanx very much
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.
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...
How can I query/script Column or Table description (extended property MS_Description') in SQLserver 2005 db.
View 4 Replies View RelatedI 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.
How can I get the SQL Server 2000 Column Description within ADO.NET 2.0?
BR / Chris
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>
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
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?
Is there a way to display a column alias as part of the result set column labels?
View 9 Replies View RelatedI am JOINing a Table with a View, and the View can have multiple rows relating to the join condition(i.e. My view as 4 columns, and can look like the following:
A B C D
A E F G
B H I J
I want this to look like -
A B C D E F G
B H I J
I want one row, but not one column, I need to maintain each value in its own column, withinin a single row.
Doesn't matter to me if that transition happens in my View or as the outcome of my Join.
I've been looking at the PIVOT command, but that appears to only work with an aggrgrate function. I don't want to calculate/aggreagte anything, just combine my multiple rows in one.
This is my VIEW stmt -
SELECT [PKG ID] AS PKG_ID, [APPR GRP NAME], [USRID OF APPR], [APPR STATUS]FROM dbo.pkgapproversWHERE ([APPR STATUS] = 'approved') AND ([APPR GRP TYPE] = 'E')
This is my Join stmt - set @sql = N'SELECT DISTINCT [PKG ID] AS PKG_ID, [ELM ACT] AS Action, [END EXEC DATE] AS "Exec Date", [COMMENT] AS Comment, [USRID OF APPR]
FROM [PkgAction]
FULL JOIN vw_PkgApprovals
ON PkgAction.[PKG ID] = vw_PkgApprovals.[PKG_ID]
WHERE 1=1'
Any thoughts....Thanks
Hi,
1. This is what SET NOCOUNT ON does:
-- SET NOCOUNT to ON and no longer display the count message.
SET NOCOUNT ON
GO
SELECT au_lname
FROM authors
GO
2. Now, is it possible and what is the statement to use if I don't want to display
the header column? au_lname. Thanks.
I have a date column and a value column. I want to display the value column based on the last date. MAX (Date)
I tried using MAX for the date column but that displays the last date.
Any Ideas?
Hello!
I am wracking my brain, trying to figure out how to return & display a column's description.
I work in Cold Fusion with SQL 2000 database.
My table name is "clients" and the column in question is "client_firstname"
The description of this column, as entered in Ent. Mgr, is "First Name"
If anyone can please help me, it would be very greatly appreciated!
Thanks!
Now for the details...
I am trying this:
----------------------------------------------------------------
<cfquery name="get_ext2" datasource="#application.dsn#">
SELECT
objname As 'Column'
, value As 'Description'
FROM
::fn_listextendedproperty
('MS_Description', 'user', 'dbo', 'table','client', 'client_firstname', default)
</cfquery>
<cfoutput>
"#get_ext2.Column#"
"#get_ext2.Description#"
</cfoutput>
----------------------------------------------------------------
... and it is returning this:
"" ""
In my debug output, the query result appears as such:
----------------------------------------------------------------
get_ext2 (Records=0, Time=16ms)
SQL =
SELECT
objname As 'Column'
, value As 'Description'
FROM
::fn_listextendedproperty
('MS_Description', 'user', 'dbo', 'table','client', 'client_firstname', default)
----------------------------------------------------------------
Haaalp!
Thanks....
Richard
Would like to know if it is possiblefor any of you to suggest how to conditionally diaplay a Column based on the values of the other columns.
Here's my query
SELECT
[ProductionDay]
,DATEPART(wk,StartTime) AS WeekCount
,(AVG([RNTDuration])) AS AvgRNT
,MIN([RNTDuration]) As LeastRNT
,MAX([RNTDuration]) As WorstRNT
,dbo.fn_MinimumRNT_sp([ProgramNo],[Machine],[ProductionDay]) AS BestRNT
,dbo.fn_MinimumRNT(SubAssemblyNo,[Machine]) AS BestRNT_txt
,SUM([LoadingDuration]) AS LoadingTime
,[ProgramNo] AS ProgramNo
,COUNT(RNTID) AS PartsCount
,[SubAssemblyNo]
,[Sheetsize]
FROM [RPMS].[dbo].[List_MachineShopRNT]
where Machine = @iMachine and dateadd(month, datediff(month, 0, [StartTime]),0) = @dtMonthStartDate
Group by DATEPART(wk,StartTime), [ProductionDay],[Machine],[ProgramNo],[SubAssemblyNo],[Sheetsize]
Here I would like to Run the function dbo.fn_MinimumRNT_sp or dbo.fn_MinimumRNT if the ProgramNo is 100
Thank you
Query for output this result on Table C ?
========================================================
I have one table store below information, and other one table is store staff name and phone number. how to display of table c data by new column and using the key staff_code1=staff_id or staff_code2= staff_id or staff_code3=staff_id ?
---------------------------------------------------------
Table A
staff_code1,staff_code2,staff_code3
Table B
staff name
staff_id
staff_phone
-----------------------------------------------
Table C display in new column .
staff name-1 code1 staff name-2 code2 , staff name-3 code3
peter id-01 susuan id-03 david id-05
Hi,I have a DataGrid whose DataSource is an SQLDataSource. This SQLDataSource references a SQL Server table with an Identity column, named Id. The SQLDataSource has generated an Update statement for me, but this only works if the Id column is selected for display in the DataGrid, and has Visible = True. If not, then the @original_Id parameter to the generated Update statement has a value of Nothing, and the Update has no effect. So is there a way to enable the Update statement to work, without the user having to see the Id column in the DataGrid? Many thanks,Keith.
View 3 Replies View RelatedMy first ASP.NET/SQL project. I'm creating an Asset Management DB. I wish to view the Asset number, the full name of the user it's assigned to and the Make and Model of each record in the DB. However assets are split into different categories e.g. monitors, PCs, Preinters etc. and they are all in different tables. The SQL below displays the asset number, Name of person assigned and the model of the asset.
SELECT Hardware.AssetNo, [User].FullName, MonitorModel.Model, PCModel.Model AS Expr1, PrinterModel.Model AS Expr2
FROM Hardware INNER JOIN
[User] ON Hardware.UserID = [User].UserID INNER JOIN
Model ON Hardware.ModelID = Model.ModelID LEFT OUTER JOIN
MonitorModel ON Model.MonitorModelID = MonitorModel.MonitorModelID LEFT OUTER JOIN
PCModel ON Model.PCModelID = PCModel.PCModelID LEFT OUTER JOIN
PrinterModel ON Model.PrinterModelID = PrinterModel.PrinterModelID
This outputs:-
Asset number FullName Model Expr1 Expr2
00000 User Name Model NULL NULL
00001 User Name NULL Model NULL
00002 User Name NULL NULL Model
However what i hope to acheive is output Model, Expr1, Expr2 into one column like so:-
Asset number FullName Model
00000 User Name Model
Can i do this in the SQL or do i have to do it in my ASP.NET (VB) Page?
Using VSWD 2005 Ex Edition and SQL Server 2005 Ex Edition
Thank you for your replies
I am trying to display component usage in January for the past year, if I want to display the year in different column, what should I do?
eg.
component_id component_description qty_used_on_2005_Jan qty_used_on_2006_Jan
C58B0BDD tape drive 2 3
Currently I am using this sql:
select cast(year(date_complete) as varchar(10)) + ' Jan' as Year, component_id, component_description,sum(component_qty) as total_qty_used
from view_jobComponent
where month(date_complete) = 1group by component_id, component_description, cast(year(date_complete) as varchar(10)) + ' Jan'order by component_id, component_description
which I will get something like this:
Year component_id component_description total_qty_used
2005 Jan C58B0BDD tape drive 22006 Jan C58B0BDD tape drive 3
I have a column in the following select startement called displayvalue. Displayvalue is a varchar column. Primary everything entered into this column is numeric, but there are times when NR is entered. In the select query if it sees NR, I would like to have NR changed in the resultset to NULL or blank. NR doesn't come up all of the time, but I have not idea how to do this in the select statment.
Here goes..
SELECT SAMPLE.SAMPLEID, SAMPLE.U_WORKORDERNUMBER, SAMPLE.U_SAMPLEDATETIME, SDI.PARAMID, SDI.DISPLAYVALUE
FROM SAMPLE SAMPLE, SDIDATAITEM SDI
WHERE ( (SAMPLE.SAMPLEID = SDI.KEYID1) AND
(SAMPLE.U_WORKORDERNUMBER = '0060') AND (SAMPLE.U_SAMPLEDATETIME > '31-DEC-2003') AND (SDI.PARAMID = 'BOD') )
ORDER BY SAMPLE.SAMPLEID ASC, SDI.PARAMID ASC
How do I only display a part of data from the column ?
Lets say emp_id is E3456 and all i want to display is 3456 without that E?
I have a table with a data cell which is printing top-to-down
1
2
3
4
5
6
7
8
I want it to print like
1 2 3 4 5 6 7
How to do this??
The cell is in a group detials sections. and I am using Reporting Services 2000 with VS.NET 2003
Thanks in Advance
How to display 2 columns of Data in 1 column?
If I use like this
Select Names, 'myData' from emp
The output is:
Name1 myData
Name2 myData
Name3 myData
But, I need to display like this
Name1
myData
Name2
myData
Name3
myData
please advise
Hi all,
I have created a trigger in SQLExpress which looking for any insertion to a specific table in a database:
Create Trigger Tg_Insert on [tablename]
FOR Insert
AS
Select * FROM Inserted
---------------------------------------------------------
Assume there is nothing wrong with this trigger as I have already checked the output with Query tool.
My question is how can I display the newly inserted data to the client? In my case, Console application.
I want to do something like Console.WriteLine("data from inserted table here");
How can I achieve this? FYI, I'm using VS2005 with sqlexpress 2005.
Thanks in advance.
Regards,
SHAH
Hello
I know how I can display a list of names in alphebetical order on my website:
Select L as [Last Name]
From Name_CatEWhere Education = 'yes'Order ByLName ASC
However, to make things a little more orginised I would like to view my database table column in alphabetical order also, but ithie code does not work within my database.
What do I need to change in the following code, to view my database table column in a-z order?
SELECT LName FROM Name_CatEORDER BY LName ASC
Thanks
Lynn