February 6, 2015 at 9:11 am
I am working on sql server 2014 and I have a list of functions/proedures I want to create on the server and then use them on any database without having to install it on every database I want to use it. I know I did this in the past but can't recall how to do this. Can someone jug my memory?
thanks
February 6, 2015 at 11:12 am
You can use three part names to include database_name.schema_name.object_name
Or you could use synonyms.
February 6, 2015 at 11:44 am
i do this ALL the time. i have suites of procedures using this ability.
it requires a few things
by doing this, you gain the special ability that the procedure uses the RELATIVE dmv's(like sys.objects) to it's current database context
so my example below,if i was int he Production database in SSMS, by calling "exec sp_find invoi" the procedure would use Production.sys.objects and Production.sys.columns to find tables or columns in the Production database, it would find any tables or columns containing the substring i was searching for.
this is a very powerful utility to leverage.
IF OBJECT_ID('[dbo].[sp_find]') IS NOT NULL
DROP PROCEDURE [dbo].[sp_find]
GO
--#################################################################################################
--developer utility function added by Lowell, used in SQL Server Management Studio
--Purpose: find table/view columns containing search phrase
--exec sp_ms_marksystemobject 'sp_find'
--#################################################################################################
CREATE PROCEDURE [dbo].[sp_find]
@findcolumn VARCHAR(50)
AS
BEGIN
SET NOCOUNT ON
--print object_name(@@PROCID)
SELECT
SchemaName,
TableFound,
ColumnFound,
ObjectType
FROM
(
SELECT
1 AS SortOrder,
schema_name(objz.schema_id) As SchemaName,
objz.name AS TableFound,
'' AS ColumnFound,
objz.type_desc AS ObjectType
FROM sys.objects objz
WHERE objz.name LIKE '%' + @findcolumn + '%'
AND objz.type_desc IN('SYSTEM_TABLE',
'VIEW',
'SQL_TABLE_VALUED_FUNCTION',
'SQL_STORED_PROCEDURE',
'SQL_INLINE_TABLE_VALUED_FUNCTION',
'USER_TABLE',
'SQL_SCALAR_FUNCTION')
UNION ALL
SELECT
2 AS SortOrder,
schema_name(objz.schema_id) As SchemaName,
objz.name AS TableFound,
colz.name AS ColumnFound,
objz.type_desc AS ObjectType
FROM sys.objects objz
INNER JOIN sys.columns colz
ON objz.object_id=colz.object_id
WHERE colz.name LIKE '%' + @findcolumn + '%'
AND objz.type_desc IN('SYSTEM_TABLE',
'VIEW',
'USER_TABLE')
) X
ORDER BY
SortOrder,
ObjectType DESC,
TableFound,
ColumnFound
END --PROC
GO
exec sp_ms_marksystemobject 'sp_find'
GO
IF OBJECT_ID('[dbo].[sp_show]') IS NOT NULL
DROP PROCEDURE [dbo].[sp_show]
GO
--#################################################################################################
--developer utility function added by Lowell, used in SQL Server Management Studio
--Purpose: Preview top 100 rows of a given table
--additional modification: fast count of rows if a TABLE or #Temp (no results on views)
--exec sp_ms_marksystemobject 'sp_show'
--#################################################################################################
CREATE PROCEDURE sp_show
--USAGE: sp_show gmact
@TblName sysname,
@Top INT = 100
--WITH ENCRYPTION
AS
BEGIN
DECLARE @SchemaName sysname,
@TableName sysname,
@TotalRows bigint = 0
--two part?
IF LEN(@TblName) - LEN(REPLACE(@TblName,'.','')) = 1
BEGIN
SET @SchemaName = REPLACE(REPLACE(SUBSTRING(@TblName,1,CHARINDEX('.',@TblName) -1),'[',''),']','')
SET @TableName = REPLACE(REPLACE(SUBSTRING(@TblName,CHARINDEX('.',@TblName)+1,LEN(@TblName)),'[',''),']','')
END
ELSE
BEGIN
SET @TableName = REPLACE(REPLACE(@TblName,'[',''),']','')
--get the schema, in case it's not dbo!
SELECT @SchemaName = schema_name(schema_id) from sys.objects where name = @TableName
END
DECLARE @cmd VARCHAR(MAX)
IF LEFT(@TableName,1) = '#'
BEGIN
SELECT
@TotalRows = ps.row_count
FROM tempdb.sys.indexes AS i
INNER JOIN tempdb.sys.objects AS o
ON i.OBJECT_ID = o.OBJECT_ID
INNER JOIN tempdb.sys.dm_db_partition_stats AS ps
ON i.OBJECT_ID = ps.OBJECT_ID
AND i.index_id = ps.index_id
WHERE i.index_id < 2
AND o.is_ms_shipped = 0
AND o.object_id = OBJECT_ID('tempdb.dbo.' + RTRIM(@TableName)) ;
END
ELSE
BEGIN
SELECT
@TotalRows = ps.row_count
FROM sys.indexes AS i
INNER JOIN sys.objects AS o
ON i.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.dm_db_partition_stats AS ps
ON i.OBJECT_ID = ps.OBJECT_ID
AND i.index_id = ps.index_id
WHERE i.index_id < 2
AND o.is_ms_shipped = 0
AND o.object_id = OBJECT_ID(RTRIM(@TableName))
AND o.schema_id = CASE WHEN @SchemaName IS NULL THEN o.schema_id ELSE schema_id(@SchemaName) END ;
END
SELECT ISNULL(@TotalRows,0) As TotalRows,@SchemaName As SchemaName,@TableName As TableName
SET @cmd = 'SELECT TOP ' + CONVERT(VARCHAR,@Top) + ' * FROM '
+ CASE WHEN @SchemaName IS NULL THEN '' ELSE @SchemaName + '.' END
+ QUOTENAME(@TableName) + ' ORDER BY 1 DESC '
EXEC(@cmd)
END
GO
exec sp_ms_marksystemobject 'sp_show'
GO
Lowell
February 6, 2015 at 12:13 pm
Thanks! haven't done this in so long I couldn't remember.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply