finding code that relies on the default schema RRS feed

  • Question

  • I am in the process of converting a database from using SQL Server authentication to Windows Authentication.

    When a user connects using Windows Authentication and using a Login that is defined based on an AD group that user has no default schema. However our database at present relies upon the user having a particular default schema.

    It is easy to convert a table reference to a schema.table. However my question is, is there any way for me to search the database for table references that do not have an explicite schema?


    Friday, October 28, 2011 8:33 PM

All replies

  • Hi Jacob !

    You might need below query;

    SELECT '['+SCHEMA_NAME(schema_id)+'].['+name+']'AS TableWithDefaultSchemaDbo , name AS TableName
    FROM sys.tables
    WHERE SCHEMA_NAME(schema_id) = 'dbo'

    Note : 'Dbo' is Default Schema so tables which don't have explicit schema will be associated with this 'dbo'.


    Please let me know if this doesn’t work for you. Hope I have answered you correctly.


    Friday, October 28, 2011 9:29 PM
  • Sounds like a job for the regular expression - you need to search all your view definitions, functions, triggers, stored procedures for using table name without schema prefix. I don't see it as something simple.
    For every expert, there is an equal and opposite expert. - Becker's Law

    My blog
    Friday, October 28, 2011 9:33 PM