Get the old SQL servername

I recall a method to get the old servername of a SQL server but old age has gotten to me.
I suspect a particular server has been renamed at one point. Obviously it wasn't me and the person before me is not around for me to ask.
So, can someone help an old guy out?

The results of print @.@.servername is NULL.

ThanksWhat OS? Which version of SQL?

If @.@.SERVERNAME is null it usually means that the sysservers entry is missing.

I'm not positive where to look for the old name, but a good place to start would be the registry. Look in HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\InstalledInstances.

Regards,

hmscott|||SELECT datasource
FROM master.dbo.sysservers
WHERE 0 = srvid-PatP

Get the ColumnName of the Identity in a Table

Hi,
I need to know the name of the Column which is the Identity of a Table.
Below is the query to what point I succeeded. But now I'm stuck.
This query returns all tables with an Identity-Column in a database, crossed
with ALL columns (obviously).
Any suggestions?
TIA,
Michael
SELECT INFORMATION_SCHEMA.TABLES.TABLE_CATALOG,
INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA,
INFORMATION_SCHEMA.TABLES.TABLE_NAME,
INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME,
IDENT_SEED(INFORMATION_SCHEMA.TABLES.TABLE_NAME) AS
IDENT_SEED, IDENT_INCR(INFORMATION_SCHEMA.TABLES.TABLE_NAME)
AS IDENT_INCR,
IDENT_CURRENT(INFORMATION_SCHEMA.TABLES.TABLE_NAME) AS IDENT_CURRENT
FROM INFORMATION_SCHEMA.TABLES INNER JOIN
INFORMATION_SCHEMA.COLUMNS ON
INFORMATION_SCHEMA.TABLES.TABLE_CATALOG =
INFORMATION_SCHEMA.COLUMNS.TABLE_CATALOG AND
INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA =
INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA AND
INFORMATION_SCHEMA.TABLES.TABLE_NAME =
INFORMATION_SCHEMA.COLUMNS.TABLE_NAME
WHERE (IDENT_SEED(INFORMATION_SCHEMA.TABLES.TABLE_NAME) IS NOT NULL)I think just found it:
SELECT TABLE_CATALOG,
TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME,
IDENT_SEED(TABLE_NAME) AS IDENT_SEED,
IDENT_INCR(TABLE_NAME) AS IDENT_INCR,
IDENT_CURRENT(TABLE_NAME) AS IDENT_CURRENT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMNPROPERTY(OBJECT_ID(TABLE_NAME),COL
UMN_NAME,'IsIdentity')=1
I'm just not sure if this approach will work on Server 2005...
Michael|||How about this?
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
AND TABLE_NAME = 'Orders'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Michael Maes" <michael@.merlot.com> wrote in message
news:262D812C-5439-49F0-B6CA-C2B4511BF0FD@.microsoft.com...
> Hi,
> I need to know the name of the Column which is the Identity of a Table.
> Below is the query to what point I succeeded. But now I'm stuck.
> This query returns all tables with an Identity-Column in a database, cross
ed
> with ALL columns (obviously).
> Any suggestions?
> TIA,
> Michael
> SELECT INFORMATION_SCHEMA.TABLES.TABLE_CATALOG,
> INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA,
> INFORMATION_SCHEMA.TABLES.TABLE_NAME,
> INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME,
> IDENT_SEED(INFORMATION_SCHEMA.TABLES.TABLE_NAME) AS
> IDENT_SEED, IDENT_INCR(INFORMATION_SCHEMA.TABLES.TABLE_NAME)
> AS IDENT_INCR,
> IDENT_CURRENT(INFORMATION_SCHEMA.TABLES.TABLE_NAME) AS IDENT_CURRENT
> FROM INFORMATION_SCHEMA.TABLES INNER JOIN
> INFORMATION_SCHEMA.COLUMNS ON
> INFORMATION_SCHEMA.TABLES.TABLE_CATALOG =
> INFORMATION_SCHEMA.COLUMNS.TABLE_CATALOG AND
> INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA =
> INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA AND
> INFORMATION_SCHEMA.TABLES.TABLE_NAME =
> INFORMATION_SCHEMA.COLUMNS.TABLE_NAME
> WHERE (IDENT_SEED(INFORMATION_SCHEMA.TABLES.TABLE_NAME) IS NOT NULL)|||Hi Tibor,
Thanks for your reply.
Will this approach also be compatible with 2005?
Regards,
Michael
"Tibor Karaszi" wrote:

> How about this?
> SELECT *
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
> AND TABLE_NAME = 'Orders'
>|||Yep. The purpose of info schema view is not only be stable across versions,
but also across
different DBMS vendors. They are defined in the ANSI SQL standard.
I just ran the query against 2005 April CTP, just to give it a spin...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Michael Maes" <michael@.merlot.com> wrote in message
news:B85C547B-726F-47A7-84FE-70A0384314CE@.microsoft.com...
> Hi Tibor,
> Thanks for your reply.
> Will this approach also be compatible with 2005?
> Regards,
> Michael
> "Tibor Karaszi" wrote:
>|||Thanks Tibor,
Quite a relief :-)
Michael
"Tibor Karaszi" wrote:

> Yep. The purpose of info schema view is not only be stable across versions
, but also across
> different DBMS vendors. They are defined in the ANSI SQL standard.
> I just ran the query against 2005 April CTP, just to give it a spin...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>