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:
DatabaseTableColumnObject TypeTypeNullableDefault
TritonINDAROpenInvoiceHeader_ACCINVHDARPONOTableCHAR(16)NOT NULL
TritonINDCreditDebitInvoiceHeader_CRDINVHDOHPONOTableCHAR(16)NOT NULL
TritonINDDailySalesHeader_ORDDLYHDOHPONOTableCHAR(16)NOT NULL
TritonINDDiscountPlanColumn_PRCDSPLCPPONOFTableCHAR(1)NOT NULL
TritonINDItemPriceAll_IND517B99PAPONOFTableCHAR(1)NOT NULL
TritonINDOrderEntryHeader_ORDENTHDOHPONOTableCHAR(16)NOT NULL
TritonINDPickupMemoHeader_ORDPUMHDOHPONOTableCHAR(16)NOT NULL
TritonINDPromotionComboListInventoryIPONORTableDECIMAL(9, 2)NOT NULL
TritonINDSpecialDiscountProfileHeader_PRCDSCPRSPONOFTableSYSNAMENOT NULL
TritonINDTopazInterfaceHeader_XR200PXHPONOTableCHAR(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