use dbname - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

use dbname

Author

Topic

abenitez77
Yak Posting Veteran

53 Posts

Posted - 2011-10-11 : 14:07:28

I have a sproc below that I wanted to use a parameter with the db name so I can use. This way it knows what db to execute this against. I found out that I cannot use the command USE DBNAME in a sproc or func. Is there another way to do this?

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Description: <Returns info about the Colums in a particular table. ie Columntype and size>
-- Usage: usp_GetTableInfo 'TableName'(optional)
-- =============================================

CREATE PROCEDURE usp_GetTableInfo
-- Add the parameters for the stored procedure here
@MyTable as varchar(100) = ''

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

IF Len(RTRIM(@MyTable)) > 1
Begin
SELECT SysObjects.[Name] as TableName,
SysColumns.[Name] as ColumnName,
SysTypes.[Name] AS [ColumnType],
syscolumns.[prec] As [Precision],
syscolumns.[scale] As [Scale],
syscolumns.[colorder]
FROM
SysObjects INNER JOIN SysColumns
ON SysObjects.[Id] = SysColumns.[Id]
INNER JOIN SysTypes
ON SysTypes.[xtype] = SysColumns.[xtype]
WHERE SysObjects.[type] = 'U'
AND SysTypes.[Name] <> 'sysname'
AND sysobjects.[name] = @MyTable
ORDER By SysColumns.[Name]
END
Else
BEGIN
SELECT SysObjects.[Name] as TableName,
SysColumns.[Name] as ColumnName,
SysTypes.[Name] AS [ColumnType],
syscolumns.[prec] As [Precision],
syscolumns.[scale] As [Scale],
syscolumns.[colorder]
FROM
SysObjects INNER JOIN SysColumns
ON SysObjects.[Id] = SysColumns.[Id]
INNER JOIN SysTypes
ON SysTypes.[xtype] = SysColumns.[xtype]
WHERE SysObjects.[type] = 'U'
AND SysTypes.[Name] <> 'sysname'
ORDER By SysObjects.[Name], syscolumns.[name]
END
END
GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-11 : 14:18:04

one way to do it is to use system sp sp_Msforeachdb

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources