Column Descriptions
May 10, 2007Is there a way to get a list column names and their descriptions?
View 6 Replies
ADVERTISEMENT
Column Descriptions
Jun 21, 2001How do you give a column a description in SQL.
I have a project where the table will created with SQL using the legacy database systems 6 byte field names.
the old system (DB2/400) has field(column) descriptions that I would like to move over but how?
I know I could create a view over the table and use the more descriptive field names.. but I would like for them to be in the same table..
like
emhrdt - column name (for reference to the legacy systems source code)
Employee Hire Date - column descriptions (so programmers for the new system know what it means).
How To Display Column Descriptions?
Sep 18, 2004Hello!
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
Export Column Descriptions
Jul 24, 2007I am trying to export a table from SQL Server 2000 with the following columns:
TableName (the name of a table in the database)
FieldName (the name of a field in the table)
FieldDescription (the Description of the field)
The field descriptions are accessed in Enterprise Manager by right-clicking on a table and choosing Design Table, on the Columns tab - "Description".
Can this be done?
thanks
Table - Column - Enterprise Mgr. Descriptions
Apr 22, 2004I wrote this quick SQL for project documentation purposes.
/*
Provides Table Name, Column Name, Extended Description
*/
select
a.name as tbl_name,
b.name as column_name,
d.name as data_type,
d.length as length,
d.xprec as prec,
d.scale as scale,
b.usertype,
b.scale,
c.value
from sysobjects as a
inner join
syscolumns as b
on a.id = b.id
inner join
sysproperties as c
on b.colid = c.smallid and a.id = c.id
inner join
systypes as d
on b.xtype = d.xtype
Exporting Table And Column Descriptions From Database To Cube
May 22, 2008
Can somebody tell me how to transfer table and column descriptions from SQL 2005 database to dimensions and attributes in Analysis Services?
Thanks!
Selecting Multiple Descriptions
Feb 28, 2006I want to select multiple records based on descriptions that are the same. Descriptions is my column and I really want to find duplicate records based on the description column.
View 2 Replies View RelatedAdding Descriptions To Database Objects
May 16, 2008Is there an easy way to add descriptions or comments to database objects? So, for instance, I might have a table that a number of analysts are using, and I'd like an easy way to save comments or descriptive data about each of the columns in the table. That way, all consumers of the DB can easily look up the meaning of each column, table, stored procedure, etc. I could maintain all of this information in a system separate from my RDBMS, but I'm wondering if there are existing ways to do this already built into SQL Server. I've seen something similar to this built into Oracle systems.
Thanks!
How Can I Create A Script To List All JobId And Job Descriptions
Oct 22, 2007Hi Guys,
I just need a script to get all Job Id and Job Descriptions from Sql Server Agent/Jobs and then save it into a text file. I'm not sure how to accomplish this using Sql Server Management Objects(SMO). Any sample scripts and/or stored procedures, etc. would be appreciated.
Thanks for your help.
-W2528
Analysis :: Add Descriptions To Attributes In Role Playing Dimensions
Jul 28, 2015I have a requirement to set Description values for our cube dimensions and attributes.
I've done this for regular dimensions but I cant seem to find a way for role playing dimensions. I can set the base dimension descriptions but not the 'clones'. Is this possible?
SQL Server 2012 :: Return Descriptions From Table2 In Same Format As Codes From Table1?
Apr 22, 2015Table1 contains a CODES column which may store multiple codes separated by commas.
The descriptions for these codes are stored individually in a column in in Table2.
I would like to return the descriptions from Table2 in the same format as the codes from Table1.
Table1 Stores multiple codes per ID in a single column.
ID CODES
-- --------------
01 Code1,Code2,Code3
Table2 Stores the definitions for the codes in Table1
CODE DESCRIPTION
----- ---------------
Code1 Description1
Code2 Description2
Code3 Description3
The tables are what the tables are. Here is what I am trying to accomplish:
ID CODES DESCRIPTIONS
-- ----------------- --------------------------------------
01 Code1,etc Description1,Description2,Description3
I've found two different queries which return each code and associated description by ID in separate rows. Perhaps the next step is to aggregate from this?
Integration Services :: Add New Column Based On Existing Column Using Derived Column Task?
Jul 28, 2015I have a excel file which has a column called "Code" and their values are A,B,C,D,E,F,G,H. I want to create a new column called "status" based on the values of "Code".
Code:
A
B
C
D
E
F
G
H
If A,C,E,G then "status" = "Active" else if B,D,F,H then "Status" = "Inactive". I like to do it using "Derived Column".
Transact SQL :: Calculate DateTime Column By Merging Values From Date Column And Only Time Part Of DateTime Column?
Aug 3, 2015How can I calculate a DateTime column by merging values from a Date column and only the time part of a DateTime column?
View 5 Replies View RelatedHow To Refer A Column When The Referencing Column Is An Identity Column
Oct 16, 2006Hi all,
The requirement is to have a table say 'child_table', with an Identity column to refer another column from a table say 'Parent_table'..
i cannot implement this constraint, it throws the error when i execute the below Alter query,
ALTER TABLE child_table ADD CONSTRAINT fk_1_ct FOREIGN KEY (child_id)
REFERENCES parent_table (parent_id) ON DELETE CASCADE
the error thrown is :
Failed to execute alter table query: 'ALTER TABLE child_table ADD CONSTRAINT
fk_1_ct FOREIGN KEY (child_id) REFERENCES parent_table (parent_id) ON DELETE
CASCADE '. Message: java.sql.SQLException: Cascading foreign key 'fk_1_ct' cannot be
created where the referencing column 'child_table.child_id' is an identity column.
any workarounds for this ?
TSQL - Using ALTER TABLE - ALTER COLUMN To Modify Column Type / Set Identity Column
Sep 7, 2007Hi guys,
If I have a temporary table called #CTE
With the columns
[Account]
[Name]
[RowID Table Level]
[RowID Data Level]
and I need to change the column type for the columns:
[RowID Table Level]
[RowID Data Level]
to integer, and set the column [RowID Table Level] as Identity (index) starting from 1, incrementing 1 each time.
What will be the right syntax using SQL SERVER 2000?
I am trying to solve the question in the link below:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2093921&SiteID=1
Thanks in advance,
Aldo.
I have tried the code below, but getting syntax error...
ALTER TABLE #CTE
ALTER COLUMN
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;
I have also tried:
ALTER TABLE #CTE
MODIFY
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;
Alter Table Add Column - How Do You Add A Column After Say The Second Column
Jul 11, 2007
When you use "Alter Table add Column", it adds the column to the end of the list of fields.
How do you insert the new column to position number 2 for instance given that you may have more than 2 columns?
Create table T1 ( a varchar(20), b varchar(20), c varchar(20))
Alter table add column x varchar(20)
so that the resulting table is
T1 a varchar(20), x varchar(20), b varchar(20), c varchar(20)
Can this be done programmatically?
ALTER TABLE DROP COLUMN LastUpdated Failed Because One Or More Objects Access This Column.
Mar 7, 2008Hi I’m trying to alter a table and delete a column I get the following error. The object 'DF__Morningst__LastU__19EB91BA' is dependent on column 'LastUpdated'.
ALTER TABLE DROP COLUMN LastUpdated failed because one or more objects access this column. I tried deleting the concerned constraint. But the next time I get the same error with a different constraint name. I want to find out if I can dynamically check the constraint name and delete it and then drop the column. Can anyone help.IF EXISTS(SELECT 1FROM sysobjects,syscolumnsWHERE sysobjects.id = syscolumns.idAND sysobjects.name = TablenameAND syscolumns.name = column name)BEGIN EXECUTE ('ALTER TABLE tablename DROP CONSTRAINT DF__SecurityM__DsegL__08C105B8')EXECUTE ('ALTER TABLE tablenameDrop column columnname)ENDGO
Exclude Records From A Table Where ID Column Is Same But Mail Code Column Is Multi-valued
Nov 12, 2012I am trying to exclude records from a table where the ID column is the same but the Mail code Column is multi-valued.For Example: (the table looks like....)
ID Mail_code
111111 XNT
111111 N11
111111 XNC
222222 XNC
222222 XNL
333333 XNC
So, if there is any ID that has a value of XNC, I want to exclude the ID all together from my output regardless of the other values.
SQL Server 2008 :: Display A Column Alias As Part Of The Result Set Column Labels?
Feb 26, 2015Is there a way to display a column alias as part of the result set column labels?
View 9 Replies View RelatedCast/Convert Mmddyy In String To New DB_DATETIMESTAMP Column In Derived Column Transformation
Mar 5, 2007Hi,
I have dates in "mmddyy" format coming from the sources and they are older dates of mid 80s like 082580 for instance.
When I cast it this way (DT_DBTIMESTAMP) Source_Date , It says ok but throws a runtime error.
When I hardcode a date in same format, (DT_DBTIMESTAMP) "082580" , It becomes red (an indication of syntax error) . Please note that we use double quotes in expressions in Derived Column Transformation; So an anticipation that using double quotes over single ones would be the syntax problem would be wrong.
Any help in this will sincerely be appreciated.
Thanks
Reporting Services :: Change Column Size Dynamically Depends On Content Of The Column?
Jun 4, 2015How to change column size dynamically depends on content of the column.
View 2 Replies View RelatedReporting Services :: Add A Variance Column Into SSRS Report Where Column Headers Are Non-static?
May 13, 2015I am creating a report in SSRS which has the following criteria:
- Row 1 (parent) is 'Product'
- Row 2 (child) is 'Feed'
- Columns are date. I have 5 dates showing at any one time across the top. The date field is set up as a parameter so depending on the date the user selects, the report will show that date on the end column and then the 4 days prior to that in the other columns.
- Data is the number of records.
I have a sub total on the Product and the report is collapsed on Product as default.
What i'm stuck on is trying to insert a column at the very end that will show the variance between the last two dates. So the difference between the date the user selected (@date parameter) and the day before that.
Reporting Services :: Get Computed Current Row Closing Balance Column Value In Next Row Opening Value Column
Nov 17, 2015I have a Table Having Date,Opening,Addition,Sale values where opening value comes in the very first row other times it is zero.
In ssrs how can i have a report showing closing value = Opening+Addition-Sale in current row (it is simple for 1st row ). this closing be the opening value in next row and same formula to be continued...
Date Opening AdditionTotalTank saleClosing Stock
01-11-15 14435 0 14435 8243 20627
02-11-15 0 15000 15000 9433
03-11-15 0 9000 9000 9436
04-11-15 0 12000 12000 8392
05-11-15 0 6000 6000 8157
06-11-15 0 12000 12000 8456
07-11-15 0 15000 15000 10903
08-11-15 0 6000 6000 8485
09-11-15 0 6000 6000 9413
10-11-15 0 21000 21000 6413
Error Invalid Column Name (In Sqlserver 2005) While Giving Alias Column Name
Jan 15, 2008ALTER procedure [dbo].[MyPro](@StartRowIndex int,@MaximumRows int)
As
Begin
Declare @Sel Nvarchar(2000)set @Sel=N'Select *,Row_number() over(order by myId) as ROWNUM from MyFirstTable Where ROWNUM
Between ' + convert(nvarchar(15),@StartRowIndex) + ' and ('+ convert(nvarchar(15),@StartRowIndex) + '+' + convert(nvarchar(15),@MaximumRows) + ')-1'
print @Sel
Exec Sp_executesql @Sel
End
--Execute Mypro 1,4 --->>Here I Executed
Error
Select *,Row_number() over(order by myId) as ROWNUM from MyFirstTable Where ROWNUM
Between 1 and (1+4)-1
Msg 207, Level 16, State 1, Line 1
Invalid column name 'ROWNUM'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'ROWNUM
Procedure successfully created but giving error while Excuting'.
Please anybody give reply
Thanks
SQL Server 2012 :: Subtract / Exclude Value Items From A Column And Add It To Another Column In Same Table
May 26, 2014I got a sales cost and cost amount table for my budget. the sales cost table is getting updated with FOBB items which makes the total incorrect . the FOBB values needs to be moved from the sales cost column to the cost amount column. how can i do it with an SQL script.
View 1 Replies View RelatedT-SQL (SS2K8) :: Cannot Define Primary Key Constraint On Nullable Column But Column Not Null
Sep 30, 2014We have a database where many tables have a field that has to be lengthened. In some cases this is a primary key or part of a primary key. The table in question is:-
/****** Object: Table [dbo].[DTb_HWSQueueMonthEnd] Script Date: 09/25/2014 14:05:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DTb_HWSQueueMonthEnd](
[Code] ....
The script I am using is
DECLARE@Column varchar(100)--The name of the column to change
DECLARE@size varchar(5)--The new size of the column
DECLARE @TSQL varchar(255)--Contains the code to be executed
DECLARE @Object varchar(50)--Holds the name of the table
DECLARE @dropc varchar(255)-- Drop constraint script
[Code] ....
When I the the script I get the error message Could not create constraint. See previous errors.
Looking at the strings I build
ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] DROP CONSTRAINT PK_DTb_HWSQueueMonthEnd
ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] Alter Column [Patient System Number] varchar(10)
ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] ADD CONSTRAINT PK_DTb_HWSQueueMonthEnd PRIMARY KEY NONCLUSTERED ([Patient System Number] ASC,[Episode Number] ASC,[CensusDate] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
They all seem fine except the last one which returns the error
Msg 8111, Level 16, State 1, Line 1
Cannot define PRIMARY KEY constraint on nullable column in table 'DTb_HWSQueueMonthEnd'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
None of the fields I try to create the key on are nullable.
Importing Data From Oracle9i CLOB Column To MS SQL Server Text Column
Jul 20, 2005Hi everyone,I encountered an error "Need to run the object to perform this operationCode execution exception: EXCEPTION_ACCESS_VIOLATION" When I try to import data from Oracle to MS SQL Server with EnterpriseManager (version 8.0) using DTS Import/Export Wizard. There are 508 rowsin Oracle table and I did get first 42 rows imported to SQL Server.Anyone knows what does the above error message mean and what causes therest of the row failed importing?Thanks very much in advance!Rene Z.--Posted via http://dbforums.com
View 1 Replies View RelatedMaster Data Services :: Filter Column Based On Other Column In Same Entity
May 12, 2015Using MDS 2012: I have an entity "XYZ_Entity". In "XYZ_Entity" entity I have 2 domain based Columns "DealerGroup" and "Dealer".
While inserting information into "XYZ_Entity" entity user can select the required dealer group from domain base Dealer Group values. Now for selecting Dealer he wants the dealers to be filter based on selected dealer group and he can select from the filtered list. reason to do that is he don't want to go through thousands of dealers and select an incorrect one.
Is it possible, if yes then how?
Cannot Insert The Value NULL Into Column SnapshotDataID, Table ReportServerTempDB.dbo.SessionData; Column Does Not Allow Nul
May 3, 2007I receive this message when I try to run any report. The reportserver and reportservertempdb databases were upgraded using backup/restore from SQL2000 to SQL2005 on a separate server which is running RS2005 . Please help. Thanks
View 1 Replies View RelatedTransact SQL :: SUM Of Two Table Column Base On Another Column Value And SUBTRACT And Join Tables
Oct 14, 2015I have the following table
Table Name EmployeeInformation
EmployeeID EmployeeFirstName EmployeeLastName
1 |John |Baker
2 |Carl |Lennon
3 |Marion |Herbert
Table Name PeriodInformation
PeriodID PeriodStart PeriodEnd
1 |1/1/14 |12/30/14
2 |1/1/15 |12/30/15
[code]...
I want a query to join all this tables based on EmployeeID, PeriodID and LeaveTypeID sum of LeaveEntitlement.LeaveEntitlementDaysNumber based on LeaveTypeID AS EntitleAnnaul and AS EntitleSick and sum AssignedLeave.AssignedLeaveDaysNumber based on LeaveTypeID AS AssignedAnnaul and AS AssignedSick and subtract EntitleAnnaul from AssignedAnnual based on LeaveTypeID AS AnnualBalance and subtract EntitleSick from AssignedSick based on LeaveTypeID AS SickBalance
and the table should be shown as below after executing the query
EmployeeID, EmployeeFirstName, EmployeeLastName, PeriodID, PeriodStart, PeriodEnd, EntitleAnnual, AssignedAnnual, AnnualBalance, EntitleSick, AssignedSick, SickBalance
How Can I Set Constant Padding Between The Columns Of The Column Chart(stacked Column Sub-type)?
Aug 2, 2006Hi All,
I am working on a column chart type (stacked column sub-type) report.
Our customer requires us that the space(padding) between the columns should be a constant(including the space between the Y-axis and the first column). I know how to set the width of the columns, but I really don't know how to set the width of the space between them. The columns just varies the space between them automatically according to the number of the columns (the number of the columns is not certain).
Thanks a lot in advance!
Danny
Derived Column Usage When Column Does Not Exist In Source (but Exists In Destination)
Sep 11, 2007
Posted - 09/10/2007 : 15:53:26
Hey all - got a problem that seems like it would be simple (and probably is : )
I'm importing a csv file into a SQL 2005 table and would like to add 2 columns that exist in the table but not in the csv file. I need these 2 columns to contain the current month and year (columns are named CM and CY respectively). How do I go about adding this data to each row during the transformation? A derived column task? Script task? None of these seem to be able to do this for me.
Here's a portion of the transformation script I was using to accomplish this when we were using SQL 2000 DTS jobs:
'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************
' Copy each source column to the destination column
Function Main()
DTSDestination("CM") = Month(Now)
DTSDestination("CY") = Year(Now)
DTSDestination("Comments") = DTSSource("Col031")
DTSDestination("Manufacturer") = DTSSource("Col030")
DTSDestination("Model") = DTSSource("Col029")
DTSDestination("Last Check-in Date") = DTSSource("Col028")
Main = DTSTransformStat_OK
End Function
***********************************************************
Hopefully this question isnt answered somewhere else, but I did a quick search and came up with nothing. I've actually tried to utilize the script component and the "Row" object, but the only properties I'm given with that are the ones from the source data.
thanks in advance!
jm
Analysis :: Calculated Column That Makes Integer In YYYYMMDD Format Form Date Column
Oct 12, 2015I am trying to create a whole number DAX calculated column that is derived from a date column. Basically it gets the date from the source data column and outputs it as an integer in the YYYYMMDD format.So 01/OCT/2015 would become --> 20151001...I've been fidgeting with DAX but my problem is that I keep missing the leading zeroes for months and days. So 01/March/2015 becomes 201531 which is not what I want (I need 20150301 in this case).
View 2 Replies View Related