return db names on server

Hello,

Does anyone know how to return the name of all the databases except the system databases on a server for SQL Server 2005?

Thank you

select * from sys.databases where database_id not in (1, 2, 3, 4)

|||Thank you!

Return all fields of database

Hello,

Is it possible to return all the field names of a database. I do not want the data rows. Just a list of fields in the databse.

Thanks

Something like this will do it

Use YourDBName

select table_name, column_name, ordinal_position, data_type

from information_schema.columns

order by 1,3

|||

Thanks for that.

I am trying this

Use CallsAndIncidents

select PROBSUMMARYM1

from information_schema.columns

I have my database as HOUAPPS237.CallsAndIncidents.dbo.PROBSUMMARYM1

where PROBSUMMARYM1 is the table name whose fields I want returned . i.e Name,Type ,Level,etc.

I did not understand these 2 lines

select table_name, column_name, ordinal_position, data_type

from information_schema.columns

what should be column_name(many columns), ordinal_position(?), data_type(different data types),information_schema.columns(?)

Thanks for the help Will.

Kiran

|||

... just run this - don't change the SQL

USE HOUAPPS237.CallsAndIncidents.dbo.PROBSUMMARYM1

select table_name, column_name, ordinal_position, data_type

from information_schema.columns

and see what you get

or even

USE PROBSUMMARYM1

select table_name, column_name, ordinal_position, data_type

from information_schema.columns

|||

THANKS WILL,

WORKED GREAT

Only change I had to do was from

USE HOUAPPS237.CallsAndIncidents.dbo.PROBSUMMARYM1

select table_name, column_name, ordinal_position, data_type

from information_schema.columns

to

USE CallsAndIncidents

select table_name, column_name, ordinal_position, data_type

from information_schema.columns

Thanks a bunch

Return all databases names using query?

Hi, I am trying to get all the databases from MS SQL into a dropdownlist. However, I am not sure how to do that in query ... is there a way to retrieve all databases name using a query in MS SQL? Thanks.

Hi,

SQL Server has built-in proceduresp_databases to do that. You just need to have permissions to execute it.

Return a record set of element names

Hi,

Does anyone know how to return a list of element names from an xml document?

eg.

Code Snippet

<values>
<name>Brian</name>
<lastName>Smith</lastName>
<tel>999-123456</tel>
</values>

the result set I'm after is a table with two columns (lets say col_name and col_value)

col_name col_value
name Brian
lastName Smith
tel 999-123456

my biggest problem is extracting the element name - any ideas

Many Thanks,

Jan.

Here is an example doing that using the XQuery nodes method to shred the XML into nodes and then the local-name XQuery function:

Code Snippet

DECLARE @.x xml;

SET @.x ='<values>

<name>Brian</name>

<lastName>Smith</lastName>

<tel>999-123456</tel>

</values>';

SELECT

T.xcol.value('local-name(.)','nvarchar(20)')AScol_name

,T.xcol.value('.','nvarchar(20)')AS col_value

FROM @.x.nodes('*/*')AS T(xcol);

|||Cheers Martin,

Exactly what I was after !

Retriieve table names

Hi friends,
How to retrieve all the tables in the database that is having primary key. I
also want to retrieve the foreign key tables with their parent table.
Thanks
vanithaoops ..take this link :)
http://www.dandyman.net/SQL/downloads.aspx
Dandy Weyn
[MCSE-MCSA-MCDBA-MCDST-MCT]
http://www.dandyman.net
Check my SQL Server Resource Pages at http://www.dandyman.net/sql
"Vanitha" <Vanitha@.discussions.microsoft.com> wrote in message
news:701F4FBD-7537-44B9-9C8D-600744CB013B@.microsoft.com...
> Hi friends,
> How to retrieve all the tables in the database that is having primary key.
> I
> also want to retrieve the foreign key tables with their parent table.
> Thanks
> vanitha|||Sysobjects contains objects stored in every user database.
You can join with sysconstraints and sysforeignkeyconstraints that are
having relationship with the Sysobjects table on the object_id
On Microsoft Website you can look for the SQL System table help file.
I also created a link for it on my website in the download section.
http://www.dandyman.net/sql/downloads.asp
This file might also be VERY useful for future system table querying
--
Dandy Weyn
[MCSE-MCSA-MCDBA-MCDST-MCT]
http://www.dandyman.net
Check my SQL Server Resource Pages at http://www.dandyman.net/sql
"Vanitha" <Vanitha@.discussions.microsoft.com> wrote in message
news:701F4FBD-7537-44B9-9C8D-600744CB013B@.microsoft.com...
> Hi friends,
> How to retrieve all the tables in the database that is having primary key.
> I
> also want to retrieve the foreign key tables with their parent table.
> Thanks
> vanitha|||Vanitha
try this
sp_msforeachtable @.command1 = "sp_helpconstraint '?' "
Regards
R.D
"Vanitha" wrote:

> Hi friends,
> How to retrieve all the tables in the database that is having primary key.
I
> also want to retrieve the foreign key tables with their parent table.
> Thanks
> vanitha|||Vanitha
SELECT Table_Name
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_NAME
),
'IsPrimaryKey') = 1
"Vanitha" <Vanitha@.discussions.microsoft.com> wrote in message
news:701F4FBD-7537-44B9-9C8D-600744CB013B@.microsoft.com...
> Hi friends,
> How to retrieve all the tables in the database that is having primary key.
> I
> also want to retrieve the foreign key tables with their parent table.
> Thanks
> vanitha|||Vanitha
see message pane besides grid pane for foreign key references when you
execute that
Regards
R.D
"R.D" wrote:
> Vanitha
> try this
> sp_msforeachtable @.command1 = "sp_helpconstraint '?' "
> Regards
> R.D
> "Vanitha" wrote:
>

Retrieving OLAP Cube Names from AM2000

Hello,
I am trying to retrieve the Cube names from Analysis Manager 2000 by using DSO objects in VS2005.NET C# 2.0 ( framework 2.0)

The code is something like that.
DSO.Server srv = new DSO.Server();
srv.Connect("localhost");

after that i do not what to do in order to get the cube names from AM2000.
when i do the following

srv.MDStore.Count

i can get the number of the cubes but i cant get the names.
I tried to use the following method but did not work out.

srv.MDStore.Item(object vntIndexKey)

May be i do not know how to use the above method to get the cube names.

for(int i = 0; i < srv.MDStore.Count; i++)
combobox1.Item.Add(srv.MDStore.Item(i).ToString());

the above code does not add the cube names into combobox, either. (

Please somebody help me with this problem.
I need to get the cube names from the AM2000 to let the user choose what cube he/she wants to work with!?

thanks in advance

best regards

Tunc OVACIK

DSO is the wrong API to use for something ordinary users need to run. DSO is the admin API and will only work for OLAP Administrators.

You should use the ADOMD or ADOMD.NET api, the MDX Sample app has code that does this using the older ADOMD API.

There is a sample in BOL for using ADOMD.NET to get a list of cubes which I have copied out below, the original page is available here

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/adodw9/html/0183dcdc-f2ea-4246-ad00-6e8ccc9d8217.htm

Code Snippet

private string RetrieveCubesAndDimensions()
{
System.Text.StringBuilder result = new System.Text.StringBuilder();

//Connect to the local server
using (AdomdConnection conn = new AdomdConnection("Data Source=localhost;"))
{
conn.Open();

//Loop through every cube
foreach (CubeDef cube in conn.Cubes)
{
//Skip hidden cubes.
if (cube.Name.StartsWith("$"))
continue;

//Write the cube name
result.AppendLine(cube.Name);

//Write out all dimensions, indented by a tab.
foreach (Dimension dim in cube.Dimensions)
{
result.Append("\t");
result.AppendLine(dim.Name);
}
}

//Close the connection
conn.Close();
}

//Return the results
return result.ToString();
}

|||

Hello again,

I have succedded to get the cube names from AM2000 by using DSO API. To do this job with DSO API is very easy.

For further information for the others who may need it I will give the sample code.

string[] cubeNames;

DSO.Server dsoServer = new DSO.Server();

dsoServer.Connect("localhost");

// Count will return the number of cubes on AM2000

cubeNames = new string[dsoServer.MDStore.Count];

int i = 0;

foreach( DSO.MDStore cube in dsoServer.MDStore)

{

cubeNames = cube.Name;

i++;

}

Before going through the code you should add the relevant .dll file into your project from the "Add Reference" menu.

Actually it is possible to get the cube names by using ADOMD classes as well as Darren said. I thank you for your help which was really usefull. So, the next step for me is to go through the cube and get the neccesarry data I need to make report for the user.

thanks for everything

Tunc OVACIK

|||The DSO code will only work for administrators, you can run it because you are an admin, normal users will not be able to run it. Hence the reason I suggested using Adomd.

Retrieving OLAP Cube Names from AM2000

Hello,
I am trying to retrieve the Cube names from Analysis Manager 2000 by using DSO objects in VS2005.NET C# 2.0 ( framework 2.0)

The code is something like that.
DSO.Server srv = new DSO.Server();
srv.Connect("localhost");

after that i do not what to do in order to get the cube names from AM2000.
when i do the following

srv.MDStore.Count

i can get the number of the cubes but i cant get the names.
I tried to use the following method but did not work out.

srv.MDStore.Item(object vntIndexKey)

May be i do not know how to use the above method to get the cube names.

for(int i = 0; i < srv.MDStore.Count; i++)
combobox1.Item.Add(srv.MDStore.Item(i).ToString());

the above code does not add the cube names into combobox, either. (

Please somebody help me with this problem.
I need to get the cube names from the AM2000 to let the user choose what cube he/she wants to work with!?

thanks in advance

best regards

Tunc OVACIK

DSO is the wrong API to use for something ordinary users need to run. DSO is the admin API and will only work for OLAP Administrators.

You should use the ADOMD or ADOMD.NET api, the MDX Sample app has code that does this using the older ADOMD API.

There is a sample in BOL for using ADOMD.NET to get a list of cubes which I have copied out below, the original page is available here

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/adodw9/html/0183dcdc-f2ea-4246-ad00-6e8ccc9d8217.htm

Code Snippet

private string RetrieveCubesAndDimensions()
{
System.Text.StringBuilder result = new System.Text.StringBuilder();

//Connect to the local server
using (AdomdConnection conn = new AdomdConnection("Data Source=localhost;"))
{
conn.Open();

//Loop through every cube
foreach (CubeDef cube in conn.Cubes)
{
//Skip hidden cubes.
if (cube.Name.StartsWith("$"))
continue;

//Write the cube name
result.AppendLine(cube.Name);

//Write out all dimensions, indented by a tab.
foreach (Dimension dim in cube.Dimensions)
{
result.Append("\t");
result.AppendLine(dim.Name);
}
}

//Close the connection
conn.Close();
}

//Return the results
return result.ToString();
}

|||

Hello again,

I have succedded to get the cube names from AM2000 by using DSO API. To do this job with DSO API is very easy.

For further information for the others who may need it I will give the sample code.

string[] cubeNames;

DSO.Server dsoServer = new DSO.Server();

dsoServer.Connect("localhost");

// Count will return the number of cubes on AM2000

cubeNames = new string[dsoServer.MDStore.Count];

int i = 0;

foreach( DSO.MDStore cube in dsoServer.MDStore)

{

cubeNames = cube.Name;

i++;

}

Before going through the code you should add the relevant .dll file into your project from the "Add Reference" menu.

Actually it is possible to get the cube names by using ADOMD classes as well as Darren said. I thank you for your help which was really usefull. So, the next step for me is to go through the cube and get the neccesarry data I need to make report for the user.

thanks for everything

Tunc OVACIK

|||The DSO code will only work for administrators, you can run it because you are an admin, normal users will not be able to run it. Hence the reason I suggested using Adomd.

Retrieving from one table where not in another table

I have two tables, contacts and deleted_contacts. I would like to pull a list of names from contacts where that contact_id does not exist in deleted_contacts.

This what I have tried that does not work:

SELECT first_name, last_name FROM contacts WHERE created_by_user_id = '" + uid + "' AND contact_id NOT EXISTS (SELECT * FROM deleted_contacts);

Any help woudl be greatly appreciated!
Thanks!
-DAGTA

SELECT first_name, last_name FROM contacts WHERE created_by_user_id = '" + uid + "' AND contact_id NOT EXISTS (SELECT * FROM deleted_contacts WHERE contact_id = your_contact_id_here);

NOT EXISTS checks to see whether the subquery that you provide returns at least 1 row. Since you were selecting everything from deleted_contacts, NOT EXISTS (SELECT * FROM deleted_contacts) always returned false. Instead, see if a row exists in deleted_contact that contains the specific contact_id that you're looking for.|||Have you tried:
AND contact_id NOT In(Select contact_id from...)|||Left join them

select Table1.ColumnName, ...
from Table1
left join Table2
on Table1.ID = Table2.ID
where Table2.ID is null

I don't like the Not In subquery thing.|||Thanks for the replies. I haven't tried the left join, yet.

The NOT IN method returns this error:

"Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."

To be clearer on the problem:
We have a contacts table. When a user deletes a contact, the contact is not really deleted. Instead, it's contact_id is placed in a deleted_contacts table.

I'm trying to pull a list of contacts for a user. I don't want to pull any contacts that have been 'deleted'. As such, I do not have a specific contact id. I have a created_by_user_id which is only in the contacts table, not the deleted_contacts table.

Thanks for the help!
-DAGTA|||The LEFT JOIN seems to be working. Thank you Pierre!

-DAGTA|||No problem.

I think the Not In solution is slightly faster for small resultsets, but since the Left Join method is more scalable, I tend to not worry about it too much - and I just use the Left Join method all the time.|||I see that you ended up using a LEFT JOIN, which is fine. But to elaborate a little bit on using the NOT EXISTS functionality, I have this question for you: what is the unique identifier for a given customer? Both the "contacts" and "deleted_contacts" tables have to have a relationship, or else you can't check whether a given contact is also in the "deleted_contacts" table. In other words, in the "contacts" table, you need to have a field that unique differentiates each contact from another (identity column). Suppose that's a "ContactID"field. This field has to also exist in the "deleted_contacts" table. That's how you can check, for any given record in "contacts", does it exist in the "deleted_contacts" table as well! Otherwise, you can't tie the two tables together (that's the "relational" part in Relational Databases).

By the way, use NOT EXISTS rather than NOT IN. NOT EXISTS returns true if no rows are returned by the query defined inside the NOT EXISTS () parentheses. If it returns one or more rows, it evaluates to false.

Hope this helps.

P.S. By all means, use a left join. It really doesn't matter.|||Ah yup, Not Exists is much better than Not In. I really don't like the Not In thing, even for small subqueries.

Not Exists will probably work well even on large resultsets.

Retrieving Database Names via C#

Hello,
I am trying to develop a desktop application by using C#.Net. I am working with .NET Framewrok 2.0.
I need to list the database names which are taking place in Anlaysis Services.
Actually, my application will work on the OLAP Cube which is going to be chosen by the user.
In order to do this, I have to retrieve the Cube names so the user can choose what cube he/she wants to work with!
any help appritiated.

thanks in advance.

best regards

Tunc OVACIK

Check out Analysis Management Objects (AMO).

http://msdn2.microsoft.com/en-us/library/ms124924.aspx

|||Thanks for the link which is very usefull and has good informations about the whole programming stuff of OLAP technology but I guess those classes are for Analysis Manager 2005.
I am using Analysis Manager 2000 and those classes do not support AM 2000 as far as I understand. Because I have tried to implement the sample codes given in the link but
it did not work out.
Do you have any documents or any other side which is explaining how to get database names and such stuff from Analysis Manager 2000.

thanks for your time
best regards

Tunc OVACIK|||

In that case, check out Decision Support Objects (DSO)

http://msdn2.microsoft.com/en-us/library/aa902639(sql.80).aspx

http://msdn2.microsoft.com/en-us/library/ms133828.aspx

Retrieve table names

How can I get a list of the names of the tables inside a database?

The following command will do that trick:

Code Snippet

SELECT * FROM INFORMATION_SCHEMA.TABLES

Retrieve report parameters names and type programmatically

Hi,
I need to retrieve a report parameters names and their type ( being
string, list, date etc ) programmatically so i can dynamically assign them.
Can anyone help?
Thanks,use the web service that comes with reporting services ...
the method GetReportParameters is exactly what u need ...
"Loui Mercieca" wrote:
> Hi,
> I need to retrieve a report parameters names and their type ( being
> string, list, date etc ) programmatically so i can dynamically assign them.
> Can anyone help?
> Thanks,
>
>

Retrieve Recordsets alogn with column name

Hai ,
I want to get the column names along with the rows of a table as a record
set is that possible?
Thanks,
V.BoomesshWhat programming language ?
Jens Suessmeyer.
"Boomessh" <Boomessh@.discussions.microsoft.com> schrieb im Newsbeitrag
news:AFEB1A56-62BE-46E6-AF80-CEE2FA75570D@.microsoft.com...
> Hai ,
> I want to get the column names along with the rows of a table as a record
> set is that possible?
> Thanks,
> V.Boomessh|||Boomessh,
Try this and HTH.Otherwise please provide ddl and sample data.
set nocount on
create table products(Product char(2),Num_Accounts int)
insert products values('P1',20)
insert products values('P2',21)
insert products values('P3',34)
insert products values('P4',56)
insert products values('P5',12)
select col1='product',product,col2='num_account
s',num_accounts from products
drop table products
"Jens Sü?meyer" wrote:

> What programming language ?
> Jens Suessmeyer.
> "Boomessh" <Boomessh@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:AFEB1A56-62BE-46E6-AF80-CEE2FA75570D@.microsoft.com...
>
>|||Hai,
the stated query returns like
product P1 num_accounts 20
so there are 5 products, what i need is, like this...
products, num_accounts
p1 20
p2 21
p3...
..
..
So i need 6 (5 actual values + 1 coulmn name)rows along with the column name
.
i am using VB as my programming language.
Thanks,
V.Boomessh
"ZULFIQAR SYED" wrote:
> Boomessh,
> Try this and HTH.Otherwise please provide ddl and sample data.
> set nocount on
> create table products(Product char(2),Num_Accounts int)
> insert products values('P1',20)
> insert products values('P2',21)
> insert products values('P3',34)
> insert products values('P4',56)
> insert products values('P5',12)
> select col1='product',product,col2='num_account
s',num_accounts from produc
ts
> drop table products
> "Jens Sü?meyer" wrote:
>|||I am doing a program in VB. or is it directly possible in SQL by any SP etc.
.
"Jens Sü?meyer" wrote:

> What programming language ?
> Jens Suessmeyer.
> "Boomessh" <Boomessh@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:AFEB1A56-62BE-46E6-AF80-CEE2FA75570D@.microsoft.com...
>
>|||It shouldn't be necessary to get the column names back as a record as you
already have them if you are using VB6 or VB.NET. If you are using VB6 and A
DO,
then use the recordset object to cycle through the Fields collection retriev
ing
the name. If you are using VB.NET, then cycle through the Columns collection
on
the DataSet/DataTable to get the field names.
Thomas
"Boomessh" <Boomessh@.discussions.microsoft.com> wrote in message
news:F6A470AA-2FF8-4E33-B5D3-58668FBB14C1@.microsoft.com...
>I am doing a program in VB. or is it directly possible in SQL by any SP etc
.
> "Jens Smeyer" wrote:
>

retrieve list of tables

How can I get a list of table names or SPs in my database?
Not having much luck with BOL.
thanksHi
They are stored in sysobjects. BOL has info on that.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"shank" <shank@.tampabay.rr.com> wrote in message
news:uCjpNQCLFHA.3332@.TK2MSFTNGP15.phx.gbl...
> How can I get a list of table names or SPs in my database?
> Not having much luck with BOL.
> thanks
>|||Query system views information_schema.'
Example:
use northwind
go
select
*
from
information_schema.tables
where
table_type = 'base table'
select
*
from
information_schema.routines
where
routine_type = 'procedure'
go
AMB
"shank" wrote:

> How can I get a list of table names or SPs in my database?
> Not having much luck with BOL.
> thanks
>
>|||Go to the index tab in Books Online and type in "INFORMATION_SCHEMA". That
should get you a great start.
You can find information on how to view them by typing this in: "meta data,
obtaining with information schema views"
"shank" <shank@.tampabay.rr.com> wrote in message
news:uCjpNQCLFHA.3332@.TK2MSFTNGP15.phx.gbl...
> How can I get a list of table names or SPs in my database?
> Not having much luck with BOL.
> thanks
>|||SELECT Objects.name FROM dbo.sysobjects Objects
WHERE ((OBJECTPROPERTY(id, 'IsView') = 1) OR (OBJECTPROPERTY(id,
'IsProcedure') = 1) OR (OBJECTPROPERTY(id, 'IsUserTable') = 1))
This will give you the list of all tables, views and stored procedures of
your database.
Thank you
Baiju
"shank" <shank@.tampabay.rr.com> wrote in message
news:uCjpNQCLFHA.3332@.TK2MSFTNGP15.phx.gbl...
> How can I get a list of table names or SPs in my database?
> Not having much luck with BOL.
> thanks
>|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Use the SQL Standard views in the INFORMATION_SCHEMA.
Tables (and Views in some systems):
SELECT TABLE_TYPE, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME NOT LIKE 'sys%' -- avoid system views
ORDER BY TABLE_TYPE, TABLE_NAME
--Views:
SELECT TABLE_NAME as view_name
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME NOT LIKE 'sys%' -- avoid system views
ORDER BY TABLE_NAME
--Stored Procedures:
SELECT ROUTINE_TYPE, ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = 'dbo'
AND ROUTINE_NAME NOT LIKE 'dt_%' -- avoid system routines
ORDER BY ROUTINE_TYPE, ROUTINE_NAME
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQjzMAYechKqOuFEgEQIEBwCfaOyb9ABeCNUL
hWCStiSNslhLlc4AoNAB
bbFnxkmvwTs6FwJ73wVNPqRV
=+MLw
--END PGP SIGNATURE--
Baiju wrote:
> SELECT Objects.name FROM dbo.sysobjects Objects
> WHERE ((OBJECTPROPERTY(id, 'IsView') = 1) OR (OBJECTPROPERTY(id,
> 'IsProcedure') = 1) OR (OBJECTPROPERTY(id, 'IsUserTable') = 1))
> This will give you the list of all tables, views and stored procedures of
> your database.
> Thank you
> Baiju
> "shank" <shank@.tampabay.rr.com> wrote in message
> news:uCjpNQCLFHA.3332@.TK2MSFTNGP15.phx.gbl...
>

Retrieve List of SSAS 2005 KPI Names using MDX?

I this actually possible? All my research to date suggests that it is not. I know it can be done using XMLA or AMO but these are not available from Reporting Services right?

My goal is to retrieve a list of KPI Names to Reporting Services. These names will then be used as the allowed values list of a paremeter for a KPI report. I previously managed to do it for calculated measures using EXCEPT([Measures].AllMembers, [Measures].Members).

I can currently think of three options, none of which I like!

1) Create SQL CLR Proc and use AMO to retrieve KPI Names and return result set

2) Create SQL CLR Proc and use XMLA to retrieve KPI Names and return result set

3) Periodically run some app which uses one of the above methods to populate a "Current Set of KPIs" table

Please, somebody tell me there is another way :)

Eventually I worked out a way to do it. I found that there is a "OLEDb Schema GUID" for KPIs in SSAS. I wrote a SQL CLR Procedure to connect to SSAS via OLEDB but I afterwards realised SQL Server 2005's OPENROWSET would probably have done the trick too. Anyway, the code I used in SQL CLR is:

Code Snippet

// Open the Analysis Server connection

DataTable dt = new DataTable();

SqlMetaData[] metaData;

using (OleDbConnection cnn = new OleDbConnection(cnn_str))

{

cnn.Open();

// Execute the XMLA Schema request, convert rows to SqlDataRecord for sending to the Pipe.

Guid guid = new Guid("{2AE44109-ED3D-4842-B16F-B694D1CB0E3F}"); // The GUID for MDSCHEMA_KPIS

dt = cnn.GetOleDbSchemaTable(guid, null);

}

Yay, I now have a way to list KPIs in Reporting Services.|||

The ASSP project (a .NET stored proc project for SSAS) has a way to do just what you're looking for:

CALL ASSP.Discover("MDSCHEMA_KPIS")

http://www.codeplex.com/ASStoredProcedures/Wiki/View.aspx?title=XmlaDiscover&referringTitle=Home

|||Thankyou kindly furmangg, this is excellent. And to think, I ended up writing a CLR SP using OleDb to query SSAS...

I can't believe I overlooked the ASSP project, it is full of so much useful stuff.

Retrieve List of SSAS 2005 KPI Names using MDX?

I this actually possible? All my research to date suggests that it is not. I know it can be done using XMLA or AMO but these are not available from Reporting Services right?

My goal is to retrieve a list of KPI Names to Reporting Services. These names will then be used as the allowed values list of a paremeter for a KPI report. I previously managed to do it for calculated measures using EXCEPT([Measures].AllMembers, [Measures].Members).

I can currently think of three options, none of which I like!

1) Create SQL CLR Proc and use AMO to retrieve KPI Names and return result set

2) Create SQL CLR Proc and use XMLA to retrieve KPI Names and return result set

3) Periodically run some app which uses one of the above methods to populate a "Current Set of KPIs" table

Please, somebody tell me there is another way :)

Eventually I worked out a way to do it. I found that there is a "OLEDb Schema GUID" for KPIs in SSAS. I wrote a SQL CLR Procedure to connect to SSAS via OLEDB but I afterwards realised SQL Server 2005's OPENROWSET would probably have done the trick too. Anyway, the code I used in SQL CLR is:

Code Snippet

// Open the Analysis Server connection

DataTable dt = new DataTable();

SqlMetaData[] metaData;

using (OleDbConnection cnn = new OleDbConnection(cnn_str))

{

cnn.Open();

// Execute the XMLA Schema request, convert rows to SqlDataRecord for sending to the Pipe.

Guid guid = new Guid("{2AE44109-ED3D-4842-B16F-B694D1CB0E3F}"); // The GUID for MDSCHEMA_KPIS

dt = cnn.GetOleDbSchemaTable(guid, null);

}

Yay, I now have a way to list KPIs in Reporting Services.|||

The ASSP project (a .NET stored proc project for SSAS) has a way to do just what you're looking for:

CALL ASSP.Discover("MDSCHEMA_KPIS")

http://www.codeplex.com/ASStoredProcedures/Wiki/View.aspx?title=XmlaDiscover&referringTitle=Home

|||Thankyou kindly furmangg, this is excellent. And to think, I ended up writing a CLR SP using OleDb to query SSAS...

I can't believe I overlooked the ASSP project, it is full of so much useful stuff.

Retrieve Index names

What is the query that list out all the index names used in the tables?
MadhivananTry this
select Name from sysindexes where id = object_id('Table_Name')
Prad
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1115102277.855498.76730@.f14g2000cwb.googlegroups.com...
> What is the query that list out all the index names used in the tables?
> Madhivanan
>|||Hi
SELECT s1.name, s2.name,
INDEX_COL( s1.name, s2.indid, 1 ),
CASE INDEXPROPERTY( s1.id, s2.name, 'IsClustered' )
WHEN 1 THEN 'Clustered'
ELSE 'Non-clustered'
END
FROM sysobjects s1
INNER JOIN sysindexes s2
ON s1.id = s2.id
WHERE s1.xtype = 'U'
AND s2.indid > 0 AND s2.indid < 255
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1115102277.855498.76730@.f14g2000cwb.googlegroups.com...
> What is the query that list out all the index names used in the tables?
> Madhivanan
>|||Thanks for the replies
This also gives the same
select *
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
Madhivanan|||That only list indexes created through constraints (PK and UNIQUE). The quer
y doesn't list indexes
created using CREATE INDEX.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1115116082.106013.258960@.z14g2000cwz.googlegroups.com...
> Thanks for the replies
> This also gives the same
> select *
> from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
> Madhivanan
>

retrieve columns information on a different database table

Hi guys!
Here is my situation:
I have a stored procedure in my first database and inside it I need to
retrieve column names of a table located in a different database. The
database name and the table name are provided as sysname parameters to my
stored procedure.
How do I query the INFORMATION_SCHEMA.COLUMNS from a different database?
Kind regards,
ConstantinYou can qualify the INFORMATION_SCHEMA.COLUMNS view with a database name.
Linchi
"Constantin Stan" wrote:
> Hi guys!
> Here is my situation:
> I have a stored procedure in my first database and inside it I need to
> retrieve column names of a table located in a different database. The
> database name and the table name are provided as sysname parameters to my
> stored procedure.
> How do I query the INFORMATION_SCHEMA.COLUMNS from a different database?
>
> Kind regards,
> Constantin
>
>|||Hi Linchi!
How may I qualify the name?
I tried something with @.FullSourceTableName but you may not select from that
name. It should be a table. It seems I missed some tsql classes.
Apreciate your help!
PS: this is my stored procedure:
ALTER PROCEDURE [dbo].[usp_CreateReportLocalizationForObject]
@.SourceDB as sysname,
@.SourceTable as sysname,
@.Culture varchar(10)
AS
BEGIN
-- i'm looking for something like this. but it doesn't work
declare @.FullSourceTableName sysname
set @.FullSourceTableName =OBJECT_NAME(OBJECT_ID('INFORMATION_SCHEMA.COLUMNS'), DB_ID(@.SourceDB));
print @.FullSourceTableName
-- open cursor to loop the columns list returned as a resultset
declare ColumnsList cursor for (select COLUMN_NAME from
INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @.SourceTable)
open ColumnsList
-- loop records
declare @.ColumnName sysname
fetch next from ColumnsList into @.ColumnName
while (@.@.FETCH_STATUS = 0)
begin
-- if there is no record for this column, then insert, otherwise do nothing!
-- put a default value for DisplayName and Status
if not exists(select * from ReportLocalization where
((ViewName=@.SourceTable) AND (ColumnName=@.ColumnName) AND
(Culture=@.Culture)))
begin
INSERT INTO [ReportLocalization]
(ViewName,[ColumnName],[Culture],[DisplayName],[DisplayCategory],[Status])
VALUES (@.SourceTable, @.ColumnName, @.Culture, @.ColumnName, '', 'active')
end
fetch next from ColumnsList into @.ColumnName
end
-- close cursor and deallocate resources
close ColumnsList
deallocate ColumnsList
-- insert a record for the view name, with ColumnName as empty string, and
default value for DisplayName as SourceTable
if not exists(select * from ReportLocalization where
((ViewName=@.SourceTable) AND (ColumnName='') AND (Culture=@.Culture)))
begin
INSERT INTO [ReportLocalization]
(ViewName,[ColumnName],[Culture],[DisplayName],[DisplayCategory],[Status])
VALUES (@.SourceTable, '', @.Culture, @.SourceTable, '', 'active')
end
-- Return to caller
--
RETURN 0
END
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:A850A318-5925-4BF1-B0C6-D4E3BB64EB05@.microsoft.com...
> You can qualify the INFORMATION_SCHEMA.COLUMNS view with a database name.
> Linchi
> "Constantin Stan" wrote:
>> Hi guys!
>> Here is my situation:
>> I have a stored procedure in my first database and inside it I need to
>> retrieve column names of a table located in a different database. The
>> database name and the table name are provided as sysname parameters to my
>> stored procedure.
>> How do I query the INFORMATION_SCHEMA.COLUMNS from a different database?
>>
>> Kind regards,
>> Constantin
>>