locked
all tables with specific columns RRS feed

  • Question

  • User81789783 posted

    can any one share me how to get all tables in database (via a query) in sql with below type .

    nvarchar(3)

    nvarchar(50)

    Tuesday, September 1, 2020 5:44 PM

All replies

  • User452040443 posted

    Try:

    SELECT C.TABLE_NAME, C.COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS AS C
    INNER JOIN INFORMATION_SCHEMA.TABLES AS T
        ON T.TABLE_NAME = C.TABLE_NAME
    WHERE 
        C.DATA_TYPE = 'nvarchar' AND
        C.CHARACTER_MAXIMUM_LENGTH IN (3, 50) AND
        T.TABLE_TYPE = 'BASE TABLE'

    Hope this help

    Tuesday, September 1, 2020 6:04 PM
  • User81789783 posted

    thanks for reply ,and what about if i need to check same in stored procedure and sql server function

    Wednesday, September 2, 2020 4:47 AM
  • User-1330468790 posted

    Hi erum,

     

    You could use below query to find stored procedure and sql server function.

    Stored procedure:

    SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_TYPE='PROCEDURE'
    

    Function:

    SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_TYPE='FUNCTION'

      

    There are still some other ways that you could fetch information for stored procedure and function.

    For example, you could also check the name from sys.objects

      

    Best regards,

    Sean

    Friday, September 4, 2020 6:04 AM