Locate a column across databases

  • Comments posted to this topic are about the item Locate a column across databases

  • 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

  • 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