Listing of all Stored Procedures

Where and how can I list all stored procedures listed in a specific database?
Thank you.
Hello,
The below system procedure can be used to list all procedures in a database
sp_stored_procedures
Thanks
Hari
"Terry" <Terry@.discussions.microsoft.com> wrote in message
news:5D259ABB-FC93-4467-BFE1-082039AD26BE@.microsoft.com...
> Where and how can I list all stored procedures listed in a specific
> database?
> Thank you.
|||Thank you very much!
"Hugo Kornelis" wrote:

> On Wed, 3 Jan 2007 08:16:00 -0800, Terry wrote:
>
> Hi Terry,
> For SQL Server 2005:
> SELECT name
> FROM sys.procedures;
> For SQL Server 2000:
> SELECT name
> FROM sysobjects
> WHERE type = 'P'
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
>

List Tables Associated with Specific Partition Scheme

How do I list the tables associated with a particular partition scheme? Basically I need to join between sys.tables and sys.data_spaces. The sys.tables view has a field for lob_data_space_id, but this seems to apply to the large object fields.

-Darrell

select ps.name as SchemaName, obj.name as TableName from sys.partition_schemes ps

join sys.indexes idx on (ps.data_space_id=idx.data_space_id)

join sys.objects obj on (idx.object_id=obj.object_id)

|||

This tells me which indexes are partitioned on the partition scheme, but not exactly the tables. This only works for tables if you assume that each table has at least one index created on the same partition scheme as the table itself. I want to be able to deal with the scenario where the table may not have any indexes create on the same partition as the base table.

The partitioned table may have no indexes or it may have indexes that are partitioned on a different scheme.

-Darrell

List Tables Associated with Specific Partition Scheme

How do I list the tables associated with a particular partition scheme? Basically I need to join between sys.tables and sys.data_spaces. The sys.tables view has a field for lob_data_space_id, but this seems to apply to the large object fields.

-Darrell

select ps.name as SchemaName, obj.name as TableName from sys.partition_schemes ps

join sys.indexes idx on (ps.data_space_id=idx.data_space_id)

join sys.objects obj on (idx.object_id=obj.object_id)

|||

This tells me which indexes are partitioned on the partition scheme, but not exactly the tables. This only works for tables if you assume that each table has at least one index created on the same partition scheme as the table itself. I want to be able to deal with the scenario where the table may not have any indexes create on the same partition as the base table.

The partitioned table may have no indexes or it may have indexes that are partitioned on a different scheme.

-Darrell

List of fixes in VS SP1

Can someone please supply a link to RS specific features/fixes in Visual Studio SP1? e.g. what fixes have been made to the printing activex control, or the report viewer control?

Thanks

There is a a link to a page will all the bug fixes here. You need to sign in to passport before you click on the link|||I was hoping to see only those related to RS rather than wade through 398 titles. Thanks anyway.

List of columns from tables across databases.

Hey guys,

Couldn't find this anywhere in google.

I want a list of all database column names for a specific table/view
from across database.

I tried this...
----------------
Select *
>From Information_Schema.Columns
----------------

I also tried this...

----------------
select syscolumns.name, sysobjects.name, * from syscolumns, sysobjects
where
sysobjects.id = syscolumns.id
and (sysobjects.xtype='U' or sysobjects.xtype='S')
----------------

These queries return information about the CURRENT database.

But, if I want to do it ACROSS database or across servers.. how can I
do this?

I will express my gratitude to everyone who is kind enough to answer
this question. (I've been stuck with this problem for a while now.)

Thanks!

OhMyGaw!Query other databases using the three-part name:

SELECT *
FROM database_name.information_schema.columns

SELECT C.name, O.name, *
FROM database_name.dbo.syscolumns AS C,
database_name.dbo.sysobjects AS O
WHERE O.id = C.id
AND (O.xtype='U' OR O.xtype='S')

Assuming you have set up a linked server you can query other servers with
the four-part name:

SELECT *
FROM server_name.database_name.information_schema.colum ns

SELECT C.name, O.name, *
FROM server_name.database_name.dbo.syscolumns AS C,
server_name.database_name.dbo.sysobjects AS O
WHERE O.id = C.id
AND (O.xtype='U' OR O.xtype='S')

In each case the tables are distinct objects so if you want to combine
results from multiple databases either use a UNION or write a loop that
cycles through each DB. There is actually an undocumented proc that will
access each DB in turn:

EXEC sp_msforeachdb 'USE ? SELECT DB_NAME()'

This is something you should avoid in persistent code because it won't
necessarily be supported in future but it may help you if this is just a
one-off exercise.

--
David Portas
SQL Server MVP
--|||Query other databases using the three-part name:

SELECT *
FROM database_name.information_schema.columns

SELECT C.name, O.name, *
FROM database_name.dbo.syscolumns AS C,
database_name.dbo.sysobjects AS O
WHERE O.id = C.id
AND (O.xtype='U' OR O.xtype='S')

Assuming you have set up a linked server you can query other servers with
the four-part name:

SELECT *
FROM server_name.database_name.information_schema.colum ns

SELECT C.name, O.name, *
FROM server_name.database_name.dbo.syscolumns AS C,
server_name.database_name.dbo.sysobjects AS O
WHERE O.id = C.id
AND (O.xtype='U' OR O.xtype='S')

In each case the tables are distinct objects so if you want to combine
results from multiple databases either use a UNION or write a loop that
cycles through each DB. There is actually an undocumented proc that will
access each DB in turn:

EXEC sp_msforeachdb 'USE ? SELECT DB_NAME()'

This is something you should avoid in persistent code because it won't
necessarily be supported in future but it may help you if this is just a
one-off exercise.

--
David Portas
SQL Server MVP
--|||David,

Thanks for your response. This is exactly what I was looking for.

SELECT *
FROM database_name.information_sche*ma.columns

I was trying the following

SELECT *
FROM database_name.database_owner.information_sche*ma.c olumns

BTW, where is this information_schema table? I couldn't find it when
I looked for it.

Thanks a bunch.|||David,

Thanks for your response. This is exactly what I was looking for.

SELECT *
FROM database_name.information_sche*ma.columns

I was trying the following

SELECT *
FROM database_name.database_owner.information_sche*ma.c olumns

BTW, where is this information_schema table? I couldn't find it when
I looked for it.

Thanks a bunch.|||Information_schema is a "schema" rather than a table. You can find the
definitions of the info schema views in Master.

In SQL Server 2000 "schema" is synonymous with "owner" and the
information_schema is implemented as a sort of virtual owner name that
points to the views in Master. SQL Server 2005 implements schemas
properly in a way that's consistent with other products and with the
SQL definition of the term.

--
David Portas
SQL Server MVP
--|||Information_schema is a "schema" rather than a table. You can find the
definitions of the info schema views in Master.

In SQL Server 2000 "schema" is synonymous with "owner" and the
information_schema is implemented as a sort of virtual owner name that
points to the views in Master. SQL Server 2005 implements schemas
properly in a way that's consistent with other products and with the
SQL definition of the term.

--
David Portas
SQL Server MVP
--

list of columns

Hi!
How can I use a transact SQL to get a list of columns for a specific
table withing a specific database?
Thank you,
T.
There is a variety of ways. You can use:
EXEC sp_columns 'tblname'
or query the information schema view:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 't' ;
or even
EXEC sp_help tablename ;
Anith
|||One more way (SQL Server 2005):
SELECT O.name AS table_name,
C.name AS column_name,
SCHEMA_NAME(O.schema_id) AS 'schema_name'
FROM sys.objects AS O
JOIN sys.columns AS C
ON O.object_id = C.object_id
WHERE O.type IN ('U')
AND O.name = 'YourTableName';
HTH,
Plamen Ratchev
http://www.SQLStudio.com

list of columns

Hi!
How can I use a transact SQL to get a list of columns for a specific
table withing a specific database?
Thank you,
T.There is a variety of ways. You can use:
EXEC sp_columns 'tblname'
or query the information schema view:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 't' ;
or even
EXEC sp_help tablename ;
--
Anith|||One more way (SQL Server 2005):
SELECT O.name AS table_name,
C.name AS column_name,
SCHEMA_NAME(O.schema_id) AS 'schema_name'
FROM sys.objects AS O
JOIN sys.columns AS C
ON O.object_id = C.object_id
WHERE O.type IN ('U')
AND O.name = 'YourTableName';
HTH,
Plamen Ratchev
http://www.SQLStudio.com