How can I retrieve Description and other column information?

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...type

sp_help tablename|||select * from INFORMATION_SCHEMA.TABLES
select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE

See BOL for more information about schema.|||You can also use
INFORMATION_SCHEMA.columns
cheers
-ss|||I too would like to get at the Description and I tried all of the above but Description doesn't seem to be there. I looked at all the INFORMATION_SCHEMA views and found nothing. I checked the system tables for the DB in question and found nothing. Could the Description be something internal to EM that isn't available to clients?|||What do you mean by description?|||I assume the Column Description you can enter in EM when designing a table. It's the first item on the Columns tab.|||OK...I usually maitain my data dictionary separatley...

You need to look up

fn_listextendedproperty

Never really used it....|||I assume the Column Description you can enter in EM when designing a table. It's the first item on the Columns tab.

Check this table:

select * from sysproperties|||Yup... Works fine.

Thanks Brett..|||select * from sysproperties

Works as well but may take extra coding to figure out the id and smallid so the data for the correct column can be identified.

Thanks...|||It works!!! Assuming a Table name of 'KB' and a column named 'Title' this gets the description.

declare @.tbid integer
declare @.colid integer

Select @.tbid=id from sysobjects where xtype='u' and name='KB'
Select @.colid = colid from syscolumns Where id=@.tbid And name='Title'
select value from sysproperties where id=@.tbid and smallid=@.colid and name='MS_Description'

Thanks snail for the pointer to sysProperties.|||Thanks all..
I think we have to know sysproperties table joined with which table..|||Can't help with the JOIN. There are three tables here and I haven't figured out how to JOIN two yet! :)|||Now I am finishing ...
This works! the following three tables joined gives what I asked.
I hope this helps all...
Thank you all for considering

Use Northwind
SELECT sysobjects.name AS [Table], syscolumns.name AS [Column], sysproperties.[value] AS Description
FROM (sysobjects INNER JOIN syscolumns
ON syscolumns.id = sysobjects.id) LEFT JOIN sysproperties
ON sysproperties.smallid = syscolumns.colid AND
sysproperties.id = syscolumns.id
where sysobjects.name = 'Orders'

-- PS
sysobjects.name : table Name (WHERE sysobjects.xtype = 'U')
sysobjects.id : table ID
sysproperties.smallid : Column id
sysproperties.[value] : Description

You can add following
WHERE sysobjects.xtype = 'U' xtype='U' means only select Table object

How can I retrieve a recordset from a matrix-like table?

I have a table defined as (int Row, int Column, money Data); as it were a matrix.

Which is the more efficient way of retrieving a result set with the following form?

Column1 Column2 Column3
---- ---- ----
Data11 Data12 Data13
Data21 Data22 Data23
Data31 Data32 Data33
... ... ...


Thanks a lot in advance.Lookup "Crosstab queries" in Books Online, and you will see a perfect example of how to do what you want to do.

How can i remove IDENTITY by ALTER TABLE?

Hello!
Can i remove IDENTITY property of the column by ALTER TABLE command?
Thanks!
Best regards, Konstantin KnyazevThis is the script that is generated when you do this with Enterprise
Manager
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_blah
(
oldid int NOT NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.blah)
EXEC('INSERT INTO dbo.Tmp_blah (oldid)
SELECT oldid FROM dbo.blah TABLOCKX')
GO
DROP TABLE dbo.blah
GO
EXECUTE sp_rename N'dbo.Tmp_blah', N'blah', 'OBJECT'
GO
COMMIT
As you can see it's not a one-liner
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Here's an example, basically you can't remove the property without an
intermediary column...
Note, you'll need to deal with the constraint / index if you created one,
just use ALTER TABLE DROP CONSTRAINT and add it again.
use tempdb
create table mytest (
id int not null identity,
myothercol int
)
insert mytest ( myothercol ) values( 1 )
go
begin tran
alter table mytest add id_new int null
exec ( 'update mytest set id_new = id' )
exec ( 'alter table mytest drop column id' )
exec sp_rename 'mytest.id_new', id
commit tran
go
select *
from mytest
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Konstantin Knyazev" <kknyazev_no_spam_@.mail.ru> wrote in message
news:ODgeTATaGHA.1240@.TK2MSFTNGP03.phx.gbl...
> Hello!
> Can i remove IDENTITY property of the column by ALTER TABLE command?
> Thanks!
> Best regards, Konstantin Knyazev
>|||Yes, i have tested this by EM, but i have too big table and it is very long
operation, about 40-50 min. I'm looking something quicker.
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1146056271.030082.51260@.i40g2000cwc.googlegroups.com...
> This is the script that is generated when you do this with Enterprise
> Manager
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> BEGIN TRANSACTION
> CREATE TABLE dbo.Tmp_blah
> (
> oldid int NOT NULL
> ) ON [PRIMARY]
> GO
> IF EXISTS(SELECT * FROM dbo.blah)
> EXEC('INSERT INTO dbo.Tmp_blah (oldid)
> SELECT oldid FROM dbo.blah TABLOCKX')
> GO
> DROP TABLE dbo.blah
> GO
> EXECUTE sp_rename N'dbo.Tmp_blah', N'blah', 'OBJECT'
> GO
> COMMIT
>
> As you can see it's not a one-liner
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>|||If your tables are so big and it will be PITA to do it then could you just
use
SET IDENTITY_INSERT table_name ON
do your thing
SET IDENTITY_INSERT table_name OFF
just a thought.
Grant
Who gives a {censored} if I am wrong.
"Konstantin Knyazev" <kknyazev_no_spam_@.mail.ru> wrote in message
news:ODgeTATaGHA.1240@.TK2MSFTNGP03.phx.gbl...
> Hello!
> Can i remove IDENTITY property of the column by ALTER TABLE command?
> Thanks!
> Best regards, Konstantin Knyazev
>|||This table should be a part of updatable distributed view, and using of
IDENTITY is restricted for it.
"Grant" <email@.nowhere.com> wrote in message
news:%23d8ZYKTaGHA.4248@.TK2MSFTNGP05.phx.gbl...
> If your tables are so big and it will be PITA to do it then could you just
> use
> SET IDENTITY_INSERT table_name ON
> do your thing
> SET IDENTITY_INSERT table_name OFF
> just a thought.
> --
> Grant
> Who gives a {censored} if I am wrong.
> "Konstantin Knyazev" <kknyazev_no_spam_@.mail.ru> wrote in message
> news:ODgeTATaGHA.1240@.TK2MSFTNGP03.phx.gbl...
>

How can I put a newline within a string column?

I am using Derived Column Transformation Editor. I have 3 string values which I would like to combine them into one string and have a newline character inbetween the 3 strings. I cannot see a Char() function similar to TSQL to use for this purpose. I thought about creating a Variable but even to that I don't know how I can assign a newline character.

Any ideas?

here is a sample doing a carriage return and a line feed between a field and a static string. Should work between 2 fields as well assuming all the data type stuff meshes out.

Description + "\r\n" + " >>>>>>test"

search BOL for the topic Literals (SSIS) . The string literals at the bottom.
Keep in mind I do not think you will see these take 'effect' in a dataviewer but should see them in the destination such as a text file, or a common thing I have done is use property expressions of the message property on a SendMail task and use "\n" to create new lines in my email message. Here is a good gnarly sample of that....

"Rows Processed: \n NASDAQ: " + (dt_wstr,9)@.[nasdaqrawrows] + "\n NYSE: " + (dt_wstr,9)@.[nyserawrows] + "\n AMEX: " + (dt_wstr,9)@.[amexrawrows] + "\n \n ================== \n The Package: " + (dt_wstr,30)@.[PackageName] + "\n Started: " + (dt_wstr,25)@.StartTime + " \n Package Run Duration in Seconds: " + (DT_WSTR,10) (DATEDIFF( "ss", @.[System::StartTime] , GETDATE() ))

Hope that helps

|||Not required today, but for info, you can make good the lack of a CHAR function, as you can escape a uncicode character, as documented in the Literals BOL topic, but simply \xhhhh where xhhhh is the Unicode character in hexadecimal notation.|||I made a note of the request for CHAR, Darren. Thanks!sql

How Can I Programmatically Remove Identity from a Table?

I am copying a view to another location as a table. SQL 2000 is automaticall
y
setting the tables ID as an Indentity column. I do not want this to happen.
It may be because the original table from whence the view works off has
identity turned on. However the copy of the table I do not wish to have the
indentity turned on as I cannot do blanket inserts with this option on.
I already know what to do from the SQL Manager wizard however I require to
do the same thing using an ALTER TABLE ALTER COLUMN approach. I find all
kinds examples on how to create an indentity insert column but not one
example on how to remove the indentity insert from a table.I dont find remove ID property.
But I traced using Profiler, SQL Server copy a table, create a table remove
ID
,Insert data using INSERT SELECT and last sp_rename.
"Jamie Carper"?? ??? ??:

> I am copying a view to another location as a table. SQL 2000 is automatica
lly
> setting the tables ID as an Indentity column. I do not want this to happen
.
> It may be because the original table from whence the view works off has
> identity turned on. However the copy of the table I do not wish to have th
e
> indentity turned on as I cannot do blanket inserts with this option on.
> I already know what to do from the SQL Manager wizard however I require to
> do the same thing using an ALTER TABLE ALTER COLUMN approach. I find all
> kinds examples on how to create an indentity insert column but not one
> example on how to remove the indentity insert from a table.|||You might consider changing your view to CAST the IDENTITY column so that
the IDENTITY property won't propagate to new tables:
ALTER VIEW View1
AS
SELECT
CAST(Col1 AS int) AS Col1
FROM MyTable
GO
You can't remove IDENTITY from an existing column or can add IDENTITY to an
existing one. If you don't want to recreate the table, you can use a script
like the one below to migrate data to a new non-identity column.
ALTER TABLE MyTable
ADD Col1_new int NOT NULL
--default constraint needed of NOT NULL
CONSTRAINT DF_MyTable_col1 DEFAULT 0
UPDATE MyTable
SET Col1_new = Col1
ALTER TABLE MyTable
DROP COLUMN Col1
EXEC sp_rename 'MyTable.Col1_new', 'Col1'
Hope this helps.
Dan Guzman
SQL Server MVP
"Jamie Carper" <JamieCarper@.discussions.microsoft.com> wrote in message
news:196AC5D9-C450-4BB7-B057-E1F1C6B8628D@.microsoft.com...
>I am copying a view to another location as a table. SQL 2000 is
>automatically
> setting the tables ID as an Indentity column. I do not want this to
> happen.
> It may be because the original table from whence the view works off has
> identity turned on. However the copy of the table I do not wish to have
> the
> indentity turned on as I cannot do blanket inserts with this option on.
> I already know what to do from the SQL Manager wizard however I require to
> do the same thing using an ALTER TABLE ALTER COLUMN approach. I find all
> kinds examples on how to create an indentity insert column but not one
> example on how to remove the indentity insert from a table.|||if I understood correctly, and you want to remove identity insert
then you should be using this switch.
SET ENABLE_IDENTITY_INSERT tbl_name,1
--don't remember the syntax exactly.
or if I have completely misunderstood the question, forgive me :)|||Hey Dan,
Thanks for the tip!
CASTing the Identity column did the trick.
"Dan Guzman" wrote:

> You might consider changing your view to CAST the IDENTITY column so that
> the IDENTITY property won't propagate to new tables:
>
Thanks again,
Jamie|||wrong syntax.. this is the one :)
SET IDENTITY_INSERT table1 ON
"Omnibuzz" wrote:

> if I understood correctly, and you want to remove identity insert
> then you should be using this switch.
> SET ENABLE_IDENTITY_INSERT tbl_name,1
> --don't remember the syntax exactly.
> or if I have completely misunderstood the question, forgive me :)

How can I print table structures in SQLExpress

I need to get a print out of column names, types, and properties for each of the tables. We just started using SQL Express from Access. I cannot find any reference to printing the structures in Microsoft SQL Server Management Studio Express.

Thanks

hi,

this is actually non avaliable at all..

you can have a "similar" result via SQL Server Management Studio Express (http://www.microsoft.com/downloads/details.aspx?familyid=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en) "Diagrams" features.. add all required tables + views and print it out.. not very beautifull but available.. or get them to clipboard and pasting them into Paint or the like...

regards

|||

Sigh............................

Thanks

|||

Rich

Perhaps this will help. Query your database with this:

SELECT * FROM
INFORMATION_SCHEMA.COLUMN

Copy the results into excel or something and get rid of the column you don't need.

|||

Subtle wrote:

Rich

Perhaps this will help. Query your database with this:

SELECT * FROM
INFORMATION_SCHEMA.COLUMN

Copy the results into excel or something and get rid of the column you don't need.

How about using sp_describe_cursor_columns? Then you can print the output from C# or something?

|||

hi,

I'd better go for INFORMATION_SCHEMA ANSI view or, if your really want to go a quiet unsupported way, you could even resort on an unsupported/undocumented stored procedure, sp_MSForEach (http://www.databasejournal.com/features/mssql/article.php/3441031)..

then you can define your query to be executed against all database tables...

for instance something based on catalog views, similar to

SET NOCOUNT ON;

USE AdventureWorks;

GO

DECLARE @.cmd varchar(MAX);

SET @.cmd = 'SELECT ''?'' AS [ObjName];

SELECT clmns.name AS [Name]

, usrt.name AS [DataType], ISNULL(baset.name, N'''') AS [SystemType]

, clmns.is_nullable, clmns.is_identity, clmns.is_rowguidcol, clmns.is_computed

, CASE WHEN clmns.name IN (''timestamp'', ''rowversion'') THEN 1 ELSE 0 END AS is_timestamp

, CASE WHEN clmns.default_object_id <> 0 THEN 1 ELSE 0 END AS [has_default_value]

, CASE WHEN clmns.default_object_id = 0 THEN '''' ELSE

(SELECT d.definition

FROM sys.default_constraints d

WHERE(clmns.object_id = d.parent_object_id)

AND d.parent_column_id = clmns.column_id)

END AS [Default Definition]

, CAST(CASE WHEN baset.name IN (N''nchar'', N''nvarchar'') AND clmns.max_length <> -1 THEN clmns.max_length/2 ELSE clmns.max_length END AS int) AS [Length]

, CAST(clmns.precision AS int) AS [NumericPrecision]

, CAST(clmns.scale AS int) AS [NumericScale]

FROM sys.tables AS tbl

INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id

LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = clmns.user_type_id

LEFT OUTER JOIN sys.types AS baset ON baset.user_type_id = clmns.system_type_id and baset.user_type_id = baset.system_type_id

WHERE clmns.object_id = OBJECT_ID(''?'')

ORDER BY clmns.column_id ASC;'

EXEC sp_MSforeachtable @.cmd;

--<--

ObjName

-

[Production].[ProductProductPhoto]

Name DataType SystemType is_nullable is_identity is_rowguidcol is_computed is_timestamp has_default_value Default Definition Length NumericPrecision NumericScale

- -- -- -- - -- -- -- -- -

ProductID int int 0 0 0 0 0 0 4 10 0

ProductPhotoID int int 0 0 0 0 0 0 4 10 0

Primary Flag bit 0 0 0 0 0 1 ((0)) 1 1 0

ModifiedDate datetime datetime 0 0 0 0 0 1 (getdate()) 8 23 3

...other objs..

you are then obviously free to add/remove projections as required..

regards

How can I print column name only from table?

Is there some Sp out there?select column_name
from information_schema.columns where
table_name = 'tbl1'
tbl1 is the table for which you want to find the list of columns.
Hope this is what you want.
--
"coke" wrote:

> Is there some Sp out there?|||coke,
Check the view INFORMATION_SCHEMA.COLUMNS.
Example:
use northwind
go
select TABLE_NAME, COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = 'dbo' and TABLE_NAME = 'Orders'
order by ordinal_position
go
AMB
"coke" wrote:

> Is there some Sp out there?sql

How can I place a value from my dataset into the page header?

My report has a parameter of "End_Date" which is used to create my dataset from a stored procedure. In the dataset, a column called "Begin_Date" is returned with various other information used in the report creation.

Currently, the parameter "End_Date" is used in the page header as part of my report title. My user's whould also like to see the "Begin_Date" value in the page header.

Both End and Begin dates are unique values occuring once per report run. How do I get the "Begin_Date" contained in my dataset as a value to be displayed in the page header title?

many thanks

The current method is to add a hidden text box to the body with that value. Then reference that text box value in the page header.

In future releases we are looking at adding field references to the other page sections.

|||

Thank you Brad for the response. Please excuse my lack of understanding. In the Page header, any time I reference a field in the body I get a message basically saying "Fields can not be used in any page headers or footers".

I have tried placing a hidden text box in the body with the value and referencing it in the page header, but with no success. I continue to receive the "Fields can not be used in any page headers or footers" error message.

Have I misunderstood your response? thanks

|||

you need to give reference like these for the header textbox

=ReportItems!txtReportHeader.Value

where txtReportHeader is the text box in the body, which contains the value of the dataset field

|||

Thanks rakam, it worked like a charm....

I didn't realize you could reference ReportItems from headers and footers. Appreciate the information.

How can I pass a large XML document from a column of data type ntext to a stored procedure

I'm trying to "prepare" a large xml document that we've
stored (as a string) in our database as an ntext
document. I can't seem to find a way to pass the ntext
datatype to sp_xml_preparedocument. Ntext are not allowed
as local variables, and I'm not sure how chopping up the
ntext, with readtext can help me.

sp_xml_preparedocument @.idoc output, (ntext)

In SQL 2005, you can cast the ntext to nvarchar(max) or the XML datatype and go from there using either OPENXML or XQuery to manipulate the XML.

For SQL 2000, if you know the offset of the data in the buffer, you can use the READTEXT functions to access them. Otherwise you could write code outside of the database that selected the values and converted them using native or managed data access API's. But in general in SQL 2000 you cannot have a variable that is bigger than 4000 unicode characters. SQL 2005 removes this restriction.

how can i merge both columns?

I have a column called email_address in table: mailing_list and another column called member_email in table: members and I want to do a SELECt to list the result of both columns in a new column

Create a UNION between the two tables.

Something like:

SELECT ml.eMail_Address

FROM Mailing_List ml

UNION

SELECT m.Member_EMail

FROM Members m

|||

what would be the output column if I use SqlDataReader?

also how can I get the select count(*) of it?

|||

You 'could' just execute the query and see what you get.

As written, there will be one column, named: eMail_Address

SELECT @.@.ROWCOUNT immediately after the query should proved the count.

|||

You can also do:

select *

from (

SELECT ml.eMail_Address

FROM Mailing_List ml

UNION

SELECT m.Member_EMail

FROM Members m ) as emailAddresses

Then you can count them, or do whatever with them, treating this derived table as any other. The first name in the UNION operator will be the name of the column in the result set.

The one thing I wanted to note is that you should always name things the same sort of way. If it is email_address in one place, it should be email_address in another. member_email could mean something else without context and knowledge of the data, while email_address is quite clear.

Naming it member_email_address would be acceptable, as an extension to explain whose email address it is.

|||

Louis,

I agree wholy with your remarks about consistency in naming. Any column containing an email address 'should' be names 'eMailAddress'. As you indicate, anything else is opening the door for confusion.

But it seems redundent to go with your alternative suggestion, member_email_address, since the column would be known as Members.eMailAddress and therefore wouldn't be confused with MailingList.eMailAddress. (I also don't believe in unnecessary underscores separating words either.) I guess I put adding a table name prefix to a column name right up there in the same category as prefixing table names with 'tbl'. Totally wasted keystrokes that offer no added value.

|||

Yeah, I agree with you. That was just an out of context naming suggestion, meaning that the name member_email_address is an acceptable name. I agree wholeheartedly that you would name it email address in the member table, if it was directly referencing the member. But if there was an email that was specifically a email address that was specific to their membership, and it wasn't clear from the name email_address, then member_email_address is a good name.

To me, naming is such a monumental pain, mostly because it is one of the most important steps in the process. A funky name causes a ripple effect of confusion to future users/programmers/support persons/etc. Thanks for clearing that one up!

how can i merge both columns?

I have a column called email_address in table: mailing_list and another column called member_email in table: members and I want to do a SELECt to list the result of both columns in a new column

Create a UNION between the two tables.

Something like:

SELECT ml.eMail_Address

FROM Mailing_List ml

UNION

SELECT m.Member_EMail

FROM Members m

|||

what would be the output column if I use SqlDataReader?

also how can I get the select count(*) of it?

|||

You 'could' just execute the query and see what you get.

As written, there will be one column, named: eMail_Address

SELECT @.@.ROWCOUNT immediately after the query should proved the count.

|||

You can also do:

select *

from (

SELECT ml.eMail_Address

FROM Mailing_List ml

UNION

SELECT m.Member_EMail

FROM Members m ) as emailAddresses

Then you can count them, or do whatever with them, treating this derived table as any other. The first name in the UNION operator will be the name of the column in the result set.

The one thing I wanted to note is that you should always name things the same sort of way. If it is email_address in one place, it should be email_address in another. member_email could mean something else without context and knowledge of the data, while email_address is quite clear.

Naming it member_email_address would be acceptable, as an extension to explain whose email address it is.

|||

Louis,

I agree wholy with your remarks about consistency in naming. Any column containing an email address 'should' be names 'eMailAddress'. As you indicate, anything else is opening the door for confusion.

But it seems redundent to go with your alternative suggestion, member_email_address, since the column would be known as Members.eMailAddress and therefore wouldn't be confused with MailingList.eMailAddress. (I also don't believe in unnecessary underscores separating words either.) I guess I put adding a table name prefix to a column name right up there in the same category as prefixing table names with 'tbl'. Totally wasted keystrokes that offer no added value.

|||

Yeah, I agree with you. That was just an out of context naming suggestion, meaning that the name member_email_address is an acceptable name. I agree wholeheartedly that you would name it email address in the member table, if it was directly referencing the member. But if there was an email that was specifically a email address that was specific to their membership, and it wasn't clear from the name email_address, then member_email_address is a good name.

To me, naming is such a monumental pain, mostly because it is one of the most important steps in the process. A funky name causes a ripple effect of confusion to future users/programmers/support persons/etc. Thanks for clearing that one up!

sql

how can i Mask a column in a sql server database

Hi i have a table in my database and i want to mask or encrypt a particular column in it? How can i do it.

Any help will be appreciated.

Regards

Karen

If you wanted to do it on the SQL Server Database just simply follow these steps:

How to: Encrypt a Column of Data
http://msdn2.microsoft.com/en-us/library/ms179331.aspx

How can I made SQL modifications that I can do at EM ?

Hi all, this probably is a stupid question, but here I go.
I have read in some places that you can't set up an already existent column as IDENTITY, but in Enterprise Manager, you can do it. My question is, how EM can do this? Because I need to do this in a lot of databases, and I have to do this by SQL.

Kind Reggards
Dirceu

IDENTITY

Specifies that the new column is an identity column. When a new row is added to the table, SQL Server provides a unique, incremental value for the column. Identity columns are commonly used in conjunction with PRIMARY KEY constraints to serve as the unique row identifier for the table. The IDENTITY property can be assigned to a tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0) column. Only one identity column can be created per table. The DEFAULT keyword and bound defaults cannot be used with an identity column. Either both the seed and increment must be specified, or neither. If neither are specified, the default is (1,1).

Seed

Is the value used for the first row loaded into the table.

Increment

Is the incremental value added to the identity value of the previous row loaded.

You can add an identity column by using

alter table <table name>
add <col_name> bigint IDENTITY (1, 1)
go

|||EM adds/removes the identity property by dropping and recreating the table. You can use SQL Profiler to look at the generated SQL code and use it for your upgrade purpose. Other than dropping and recreating the table/column there is no direct DDL statement to add/drop identity property in SQL Server. SQL Server Mobile edition however permits altering a column to add identity property.|||Good,
My problem is that this table has a lot of foreign keys (other table reffer to this table and this table reffer to other tables) I have been trying to use profiler but I doesn't understand its results. I will try to see the users help.

Thanks

How can i insert a curreny formated column

Hi i have made a column that is called carprice. Instead of just typing in £500 i want to be able to put in 500 and then have sql auto format it to £500 in the column. In other words just put the £ price infront. Is there a way of doing this thanks.A currency value should be left as a number without any symbols. The client side code should be the one formatting the value with the appropriate currency symbol. DotNet has support for this type of thing for client side formatting.|||I agree, storing as a number is the best design. Later when you need to use that data in a calculation, you dont have to try and parse it and deal with invalid entries. But the downside to just having a number is that you have no idea what currency that number is in. So if your app has any possibilities of ever going global, you might consider having a second field to track the currency. You could have a dropdown that lists all the currencies you support. You could then case on the currency to do the formatting of the output of the value you stored as a number.

How can I insert a column using a script (instead of appending)?

Hi,

I recently had to add a new column to a table that resides off-site,
in a customer's environment. Previously, the table had around 300
columns, all of which were in alphabetical order.

I found that the ALTER TABLE command appeared to be only capable of
appending a new column, but wasn't capable of inserting one into the
middle of the table.

Since alphabetical order wasn't a requirement, but just a "nice to
look at" feature, I appended it anyway, and forgot about it.

Anyone know of a clever way to do this though?

Curiously,

Warren Wright
Scorex Development TeamWarren Wright (warren.wright@.us.scorex.com) writes:
> I recently had to add a new column to a table that resides off-site,
> in a customer's environment. Previously, the table had around 300
> columns, all of which were in alphabetical order.
> I found that the ALTER TABLE command appeared to be only capable of
> appending a new column, but wasn't capable of inserting one into the
> middle of the table.
> Since alphabetical order wasn't a requirement, but just a "nice to
> look at" feature, I appended it anyway, and forgot about it.
> Anyone know of a clever way to do this though?

The "clever" way is to fiddle with syscolumns, but I highly discourge
from this.

The normal way is to go the long way: rename the old table, create
the new table including triggers, constrains and clustered index, insert
data from the old table to the new, move referencing foreign keys, drop the
old table. Restore non-clustered index.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

How can I insert a column into a table before another column via T-SQL?

Please, who can help me to resolve this problem?
My database is MS SQL 2000...
Thank you very much!!There is only one way to do this - recreate table. If your table is very big - just use DTS to export and import data.

BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_t2
(
id int NULL,
newcolumn int NULL,
code varchar(10) NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.t2)
EXEC('INSERT INTO dbo.Tmp_t2 (id, code)
SELECT id, code FROM dbo.t2 TABLOCKX')
GO
DROP TABLE dbo.t2
GO
EXECUTE sp_rename 'dbo.Tmp_t2', 't2', 'OBJECT'
GO
COMMIT|||Thanks for your reply, first!!

This way I had think before.

But, I think that the Enterprise Manager can do this in SQL 2000 visual tool.

So.....Maybe somthing can help us to complete this task.

I hope so.....|||Open your table in design mode through Enterprise Manager, then make the change you want but do not close the window or save your changes.

Instead, click on the scripting icon (3rd from left on my toolbar), and then copy the script it creates.

Close your table without saving the changes, and then you can adapt the script you copied to whatever you need.

blindman|||Originally posted by Richard Chen
Thanks for your reply, first!!

This way I had think before.

But, I think that the Enterprise Manager can do this in SQL 2000 visual tool.

So.....Maybe somthing can help us to complete this task.

I hope so.....

Enterprise Manager does the same way. To be honest I took script from EI - in design table click on icon 'Save change script' (third from the left) after some changes done.|||Plagiarist!|||Originally posted by blindman
Plagiarist!

Idea was in air!|||Originally posted by blindman
:D

I just moved from KY (I had an offer from OH, but in MD is much better).|||Now I understand what you said.

It's so kind of you.

I'II try it right now!!

How can I increase the range of an identity column

Hi guys
Im new at SQL Server and I have a problem with an identity column
the max value for the identity column has been reached I dunno if I can increased by an alter table or I need to recreate the table ?
the table has foreign keys
Thanks in advancedYou could go to the next larger datatype but I suspect your foreign keys will start to bomb out. What was the data type of your identity column and what was the original seed?

Consider the following:

create table Tmp(f1 int not null identity(2147483647,1), f2 varchar(5))
go
insert into Tmp (f2) values('ABC')
select * From Tmp
go
raiserror('Try to squeze one more row.',0,1) with nowait
insert into Tmp (f2) values('DEF')
select * From Tmp
go
raiserror(' ',0,1) with nowait
raiserror('change the seed.',0,1) with nowait
DBCC CHECKIDENT (Tmp, RESEED, -2147483648)
go
raiserror(' ',0,1) with nowait
raiserror('Insert more rows.',0,1) with nowait
insert into Tmp (f2) values('DEF')
select * From Tmp
go
insert into Tmp (f2) values('GHI')
select * From Tmp
go
sp_help Tmp
go
drop table Tmp
go

How can I have table and column definitions?

Hi,
I want to develop a code generator for our Data-Access Layer and I'd like to
know if there are queries I can make to have the table and column
definitions... something like Select * From Tables and Select * From
Columns Where TableName=@.TableName... something like this... I know there
are views in SqlServer 2005... for the Table Definition, I think it's
INFORMATION_TABLES or something like it... there is a similar table for
columns, but some properties are missing, like if the columns is an
Identity...
I also want to know if there is a way to retrieve the relationships like
Table1.Field1-->Table2.Field2 or something like this...
If you have a link to a web site I can find all those informations, go ahead
and post it... ;) but for those who would be tempted, don't post
www.google.com, I already tried to find something useful without any real
success...
Can someone help me please?
Thanks
ThunderMusic
Now don't go off and 'reinvent the wheel'.
Try these articles:
http://www.theserverside.net/tt/articles/showarticle.tss?id=DataSetDesigner
http://www.codegeneration.net/tiki-index.php?page=NET
http://www.codegeneration.net/generators.php
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"ThunderMusic" <NoSpAmdanlatathotmaildotcom@.NoSpAm.com> wrote in message
news:u6BSHIYFHHA.1240@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I want to develop a code generator for our Data-Access Layer and I'd like
> to know if there are queries I can make to have the table and column
> definitions... something like Select * From Tables and Select * From
> Columns Where TableName=@.TableName... something like this... I know
> there are views in SqlServer 2005... for the Table Definition, I think
> it's INFORMATION_TABLES or something like it... there is a similar table
> for columns, but some properties are missing, like if the columns is an
> Identity...
> I also want to know if there is a way to retrieve the relationships like
> Table1.Field1-->Table2.Field2 or something like this...
> If you have a link to a web site I can find all those informations, go
> ahead and post it... ;) but for those who would be tempted, don't post
> www.google.com, I already tried to find something useful without any real
> success...
> Can someone help me please?
> Thanks
> ThunderMusic
>
|||because I want to add my own things in it... and because we will probably
generate 100's of classes, I don't want to cut and paste my things in all
these classes... with my code generator, I'll be sure the classes reflect
exactly what we really need.
thanks tought, those links may be helpful...
ThunderMusic
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:OtThwNYFHHA.5000@.TK2MSFTNGP03.phx.gbl...
> Now don't go off and 'reinvent the wheel'.
> Try these articles:
> http://www.theserverside.net/tt/articles/showarticle.tss?id=DataSetDesigner
> http://www.codegeneration.net/tiki-index.php?page=NET
> http://www.codegeneration.net/generators.php
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to
> the top yourself.
> - H. Norman Schwarzkopf
>
> "ThunderMusic" <NoSpAmdanlatathotmaildotcom@.NoSpAm.com> wrote in message
> news:u6BSHIYFHHA.1240@.TK2MSFTNGP03.phx.gbl...
>
|||I thought the links might give you ideas, if not even code...
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"ThunderMusic" <NoSpAmdanlatathotmaildotcom@.NoSpAm.com> wrote in message
news:u2BNr6YFHHA.1784@.TK2MSFTNGP06.phx.gbl...
> because I want to add my own things in it... and because we will probably
> generate 100's of classes, I don't want to cut and paste my things in all
> these classes... with my code generator, I'll be sure the classes reflect
> exactly what we really need.
> thanks tought, those links may be helpful...
> ThunderMusic
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:OtThwNYFHHA.5000@.TK2MSFTNGP03.phx.gbl...
>

How can I get value of XML element from XML column

I have one simple question. How can I get scalar value of en XML element from XML column?

...

<tag> value <tag/>

...

I wont to get a result: "value"

this runs against a XML variable, but afaik, it should work in a similar manner:

set nocount on
DECLARE @.xVar XML
declare @.docHandle int
SET @.xVar =
'<book genre="security" publicationdate="2002" ISBN="0-7356-1588-2">
<title>Writing Secure Code</title>
<author>
<FirstName>Michael</FirstName>
<LastName>Howard</LastName>
</author>
<author>
<FirstName>David</FirstName>
<LastName>LeBlanc</LastName>
</author>
<price>39.99</price>
</book>'

SELECT
nref.value('FirstName[1]', 'nvarchar(50)') FirstName,
nref.value('LastName[1]', 'nvarchar(50)') LastName
FROM @.xVar.nodes('/book/author') AS R(nref)|||or you could simple do this..seems more concise to me

declare @.xml nvarchar(100)
declare @.id int

set @.xml = '<tag> value </tag>'

exec sp_xml_preparedocument @.id output, @.xml
select tagVal from openxml(@.id, N'//tag') with (tagVal varchar(100) 'text()')|||

Don't forget the :

exec sp_xml_removedocument @.Id

It is an alternative. I'm currently looking at code to try and determine whether the .nodes methods available are faster/the same/slower than OPENXML.

|||

The nodes() method on an indexed column is faster/same than doing server side cursors. It also scales better w.r.t. memory usage.

OpenXML is faster on parameters/variables than nodes(), but less scalable.

Best regards

Michael

How can I get value of XML element from XML column

I have one simple question. How can I get scalar value of en XML element from XML column?

...

<tag> value <tag/>

...

I wont to get a result: "value"

this runs against a XML variable, but afaik, it should work in a similar manner:

set nocount on
DECLARE @.xVar XML
declare @.docHandle int
SET @.xVar =
'<book genre="security" publicationdate="2002" ISBN="0-7356-1588-2">
<title>Writing Secure Code</title>
<author>
<FirstName>Michael</FirstName>
<LastName>Howard</LastName>
</author>
<author>
<FirstName>David</FirstName>
<LastName>LeBlanc</LastName>
</author>
<price>39.99</price>
</book>'

SELECT
nref.value('FirstName[1]', 'nvarchar(50)') FirstName,
nref.value('LastName[1]', 'nvarchar(50)') LastName
FROM @.xVar.nodes('/book/author') AS R(nref)
|||or you could simple do this..seems more concise to me

declare @.xml nvarchar(100)
declare @.id int

set @.xml = '<tag> value </tag>'

exec sp_xml_preparedocument @.id output, @.xml
select tagVal from openxml(@.id, N'//tag') with (tagVal varchar(100) 'text()')|||

Don't forget the :

exec sp_xml_removedocument @.Id

It is an alternative. I'm currently looking at code to try and determine whether the .nodes methods available are faster/the same/slower than OPENXML.

|||

The nodes() method on an indexed column is faster/same than doing server side cursors. It also scales better w.r.t. memory usage.

OpenXML is faster on parameters/variables than nodes(), but less scalable.

Best regards

Michael