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:

    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

  • Thanks for the script.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply