June 3, 2013 at 10:42 am
Comments posted to this topic are about the item Locate a column across databases
December 3, 2013 at 8:00 am
Hello,
Getting error: Database 'SWS' does not exist. Make sure that the name is entered correctly.
I copied the script from the article for MASTER.dbo.LocateColumnDefinations
When I execute the following:
EXEC [dbo].[LocateColumnDefinitions]
@Column = '%PONO%',
@Branch = NULL,
@DB = 'TritonIND',
@ViewTableAll = NULL
I get an error:
Msg 911, Level 16, State 1, Line 2
Database 'SWS' does not exist. Make sure that the name is entered correctly.
When I ran the generated sql:
IF DB_NAME() = 'TritonIND' AND EXISTS
(
SELECT 1
FROM sys.columns AS SC
INNER JOIN sys.objects AS SO ON SC.OBJECT_ID = SO.OBJECT_ID
INNER JOIN sys.types AS ST ON SC.system_type_id = ST.system_type_id AND NOT (ST.system_type_id = 231 AND ST.name = 'nvarchar')
WHERE SC.Name Like '%PONO%' AND SO.Type_Desc = 'User_Table'
)
BEGIN
SELECT DB_NAME() AS [Database], OBJECT_NAME( SC.OBJECT_ID ) AS [Table], SC.name AS [Column], CASE SO.Type_Desc WHEN 'User_Table' THEN 'Table' ELSE 'View' END AS [Object Type],
CASE ST.Name
WHEN 'VARCHAR' THEN 'VARCHAR(' + CAST( SC.Max_Length AS VARCHAR ) + ')'
WHEN 'NVARCHAR' THEN 'NVARCHAR(' + CAST( SC.Max_Length AS VARCHAR ) + ')'
WHEN 'CHAR' THEN 'CHAR(' + CAST( SC.Max_Length AS VARCHAR ) + ')'
WHEN 'NCHAR' THEN 'NCHAR(' + CAST( SC.Max_Length AS VARCHAR ) + ')'
WHEN 'INT' THEN 'INT'
WHEN 'BIT' THEN 'BIT'
WHEN 'BIGINT' THEN 'BIGINT'
WHEN 'SYSNAME' THEN 'SYSNAME'
WHEN 'DATETIME' THEN 'DATETIME'
WHEN 'FLOAT' THEN 'FLOAT'
WHEN 'DATE' THEN 'DATE'
WHEN 'DECIMAL' THEN 'DECIMAL(' + CAST( SC.Precision AS VARCHAR ) + ', ' + CAST( SC.Scale AS VARCHAR) + ')'
WHEN 'TIMESTAMP' THEN 'TIMESTAMP'
ELSE ST.Name
END AS Type
, CASE SC.Is_NULLable WHEN 1 THEN 'NULL' ELSE 'NOT NULL' END AS Nullable,
ISNULL( object_definition( SC.default_object_id ), '' ) AS [Default]
FROM sys.columns AS SC
INNER JOIN sys.types AS ST ON SC.system_type_id = ST.system_type_id AND NOT (ST.system_type_id = 231 AND ST.name = 'nvarchar')
INNER JOIN sys.objects AS SO ON SC.OBJECT_ID = SO.OBJECT_ID
INNER JOIN sys.all_objects AS AO ON SC.OBJECT_ID = AO.OBJECT_ID
WHERE SC.Name Like '%PONO%' AND SO.Type_Desc = 'User_Table'
ORDER BY SO.[Type_Desc], OBJECT_NAME( SC.OBJECT_ID )
END
I retrieved the following results:
Database Table Column Object Type Type Nullable Default
TritonIND AROpenInvoiceHeader_ACCINVHD ARPONO Table CHAR(16) NOT NULL
TritonIND CreditDebitInvoiceHeader_CRDINVHD OHPONO Table CHAR(16) NOT NULL
TritonIND DailySalesHeader_ORDDLYHD OHPONO Table CHAR(16) NOT NULL
TritonIND DiscountPlanColumn_PRCDSPLC PPONOF Table CHAR(1) NOT NULL
TritonIND ItemPriceAll_IND517B99P APONOF Table CHAR(1) NOT NULL
TritonIND OrderEntryHeader_ORDENTHD OHPONO Table CHAR(16) NOT NULL
TritonIND PickupMemoHeader_ORDPUMHD OHPONO Table CHAR(16) NOT NULL
TritonIND PromotionComboListInventory IPONOR Table DECIMAL(9, 2) NOT NULL
TritonIND SpecialDiscountProfileHeader_PRCDSCPR SPONOF Table SYSNAME NOT NULL
TritonIND TopazInterfaceHeader_XR200P XHPONO Table CHAR(22) NOT NULL
May 2, 2016 at 12:57 pm
Thanks for the script.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply