Changed Table structure

Hi all,
How can i get the information as to when was the last my particular table structure was changed (for ex. adding of a column/dropping a column)
Pls let me know
TIAFrom your release log...|||Can take help Lumigent's schema alert which is free as of now.
SQL doesn't hold this information.|||hey, how cool is that...installs on the server no?

How does it not have an impact on performance I wonder...

Change the table structure

Hi...

I execute select statement on three tables and i get the following table:

ID

Value

Name

1

10

color

2

20

color

3

30

color

4

40

color

and, from this table i want to create the following table:

ID

color

1

10

2

20

3

30

4

40

mean, the value color become the header title of the column.

How can i do that?

thank you...

But you use a GridView or what to show the table ?

If you use a GridView you can set the Visible attribute of a BoundField at runtime.

|||

Hi...

I am not using gridView, or any other control, i just need to do this in sql.

thanks.

|||

may215:

Hi...

I am not using gridView, or any other control, i just need to do this in sql.

thanks.

Are you going to insert values in the second table, or its value will be same as the first table but columns' names are diffrents?

If you are not going to insert values in the second table, then I suggest to create a view that get its data from the first table.

1CREATE VIEW MyViewForTheSecondTable23AS45SELECT [ID], [VALUE]AS'Color'67FROM MyFirstTable8910

Now, you can do the SELECT on the view like:

1SELECT *FROM MyViewForTheSecondTable

The returned result will be same as in the first table but columns name will be ID and Color insted of ID and Value.

Good luck.

|||

Hi...

When i try to create the view i am getting error: invalid column name ID, VALUE....in the following example: invalid column name PhonePropertyValue , PhonePropertyName.

here is the full example i trying to do:

SELECT [dbo.TblPropertyForPhoneType.PhonePropertyValue], [dbo.TblPhoneProperty.PhonePropertyName] AS
'color'

FROM dbo.TblPhoneType INNER JOIN
dbo.TblPropertyForPhoneType ON dbo.TblPhoneType.PhoneTypeID = dbo.TblPropertyForPhoneType.PhoneTypeID INNER JOIN
dbo.TblPhoneProperty ON dbo.TblPropertyForPhoneType.PhonePropertyID = dbo.TblPhoneProperty.PhonePropertyID
WHERE (dbo.TblPhoneProperty.PhonePropertyID = '34')

Why is that?

thanks...

|||

may215:

Hi...

When i try to create the view i am getting error: invalid column name ID, VALUE....i

I cheked the example I wrote .. it is working fine.

Here is a complete senario that worked on my machine and it should work on your machine as well:

1-- create sample database2create database MyTestDB3go45-- use the sample database6use MyTestDB7go89-- create new table for testing10CREATE TABLE MyTable11(12[ID]INT,13[VALUE]CHAR(10)14)15GO1617-- insert two records in the testing table18INSERT INTO MyTableSELECT 4,'red'19INSERT INTO MyTableSELECT 5,'black'202122-- create a view23CREATE VIEW MyViewForTheSecondTable2425AS2627 SELECT [ID], [VALUE]AS'COLOR'2829FROM MyTable303132-- select data from the created view33SELECT *FROM MyViewForTheSecondTable

Please let me know if it did not work with you for any reason.

may215:

in the following example: invalid column name PhonePropertyValue , PhonePropertyName.

here is the full example i trying to do:

SELECT [dbo.TblPropertyForPhoneType.PhonePropertyValue], [dbo.TblPhoneProperty.PhonePropertyName] AS
'color'

FROM dbo.TblPhoneType INNER JOIN
dbo.TblPropertyForPhoneType ON dbo.TblPhoneType.PhoneTypeID = dbo.TblPropertyForPhoneType.PhoneTypeID INNER JOIN
dbo.TblPhoneProperty ON dbo.TblPropertyForPhoneType.PhonePropertyID = dbo.TblPhoneProperty.PhonePropertyID
WHERE (dbo.TblPhoneProperty.PhonePropertyID = '34')

Why is that?

thanks...

First of all, please make sure those columns are exists in the table and they were typed correctly.

Then try this:

1SELECT prop.PhonePropertyValue, prop.PhonePropertyNameAS'color'23FROM dbo.TblPhoneType pt4INNERJOIN5 dbo.TblPropertyForPhoneType prop6ON pt.PhoneTypeID = prop.PhoneTypeID7INNERJOIN8 dbo.TblPhoneProperty pp9ON pp.PhonePropertyID = prop.PhonePropertyID10WHERE pp.PhonePropertyID ='34'11

Good luck.

Change structure in Db

Hi.
How I can change a structure(add/drop columns or add st.
procedures) in databases which are involved in Merge
Replication process? I'm using SQL Server 2000.
Thank you.
use sp_repladdcolumn and sp_repldropcolumn

change recordset position

Hi
Table Structure
company name
Phone No
Products
Example
Company Name Phone No Products
Sri balaji Enterprises 26554432 Advertising Agents
KarthicK Call Taxi 43215434 Call Taxi
Chitra Electricals 65437654 Electricals
Udhayam Call Taxi 24358901 Call Taxi
Annamalaiyar Advertising 67542317 Advertising Agents
Mathi Vehicles 23456789 Automobiles
Chennai Media 45326754 Advertising Agents
OUTPUT FORM
Here i am using 2 combo boxes. first box list out all distinct products.(i.e)
First Combo Box loads
Advertising Agents
Automobiles
Call Taxi
Eletricals
if i click the Advertising agents in first combo box the 2nd combox will
shows the company names of who are all dealing Advertising.
so the second combo box shows
Sri balaji Enterprises
Annamalaiyar Advertising
Chennai Media.
As the same when i click call taxi
Karthick Call taxi
Udhayam Call taxi
Its working...But my problem is..........
Next time when i click Advertising agents the second combo box have to show
Annamalaiyar Advertising(2nd record have 2 come to first)
Chennai Media.(3rd record have 2 come second)
Sri balaji Enterprises(1st record have 2 go last)
same when i click call taxi
Udhayam Call Taxi(2nd record have 2 come to first)
Karthick Call taxi(1st record have 2 go last)
i dont know how to do...if anybody knows help me to do that...
Thanks in advance.Hi
"nila_vaanan" wrote:
> Hi
> Table Structure
> company name
> Phone No
> Products
> Example
> Company Name Phone No Products
> Sri balaji Enterprises 26554432 Advertising Agents
> KarthicK Call Taxi 43215434 Call Taxi
> Chitra Electricals 65437654 Electricals
> Udhayam Call Taxi 24358901 Call Taxi
> Annamalaiyar Advertising 67542317 Advertising Agents
> Mathi Vehicles 23456789 Automobiles
> Chennai Media 45326754 Advertising Agents
> OUTPUT FORM
> Here i am using 2 combo boxes. first box list out all distinct products.(i.e)
> First Combo Box loads
> Advertising Agents
> Automobiles
> Call Taxi
> Eletricals
> if i click the Advertising agents in first combo box the 2nd combox will
> shows the company names of who are all dealing Advertising.
> so the second combo box shows
> Sri balaji Enterprises
> Annamalaiyar Advertising
> Chennai Media.
> As the same when i click call taxi
> Karthick Call taxi
> Udhayam Call taxi
> Its working...But my problem is..........
> Next time when i click Advertising agents the second combo box have to show
> Annamalaiyar Advertising(2nd record have 2 come to first)
> Chennai Media.(3rd record have 2 come second)
> Sri balaji Enterprises(1st record have 2 go last)
> same when i click call taxi
> Udhayam Call Taxi(2nd record have 2 come to first)
> Karthick Call taxi(1st record have 2 go last)
> i dont know how to do...if anybody knows help me to do that...
> Thanks in advance.
>
You don't say how this listbox is being populated, but SQL Server does not
guaranteed and the order of records returned by a SQL query unless you
specify an ORDER BY clause on your statement. Check out the section on ORDER
BY in books online.
John