How to Determine if the item in geodatabase is a view or not?

[SeifElDinYasser]

Hi, I had a task to make a Python script that would be used as a script tool in ArcGIS Pro, this script should loop through the items in an enterprise geodatabase and check each item if it is a view or not, if the item is view it will be forwarded to a database called "views_db" and if not, it will be forwarded to a database called "items_db".
First, I tried to use Describe function that describes the properties of each item, but I couldn't find any property can recognize if the item in gdb is view or not.
I used another approach based on that, views are not editable at all not like feature classes, tables and so on. This approach is to try to add a new row in the feature class or table, if the feature class or table accepted inserting a new row, the item will not be treated as a view, and vice versa. For my case it worked successfully till now, but in a long-term use, if there's any item that is not editable and not a view, it would be considered as a view.
So, if you know another idea to differentiate between views and other items, please share it here.

[RyanUthoff · Accepted solution]

If you're okay with using Python outside of ArcPy, then I will give you a couple of scripts that can be used to determine the tables and views. Of course, you'll still need to modify the scripts to forward them to your views/items DB, but it should at least give you a starting point. You'd still be able to run this as a script tool in ArcGIS Pro. It uses Python to directly connect to a MS SQL database.

#Output Tables
import pyodbc

def list_tables_mssql(server, database, username, password):
    try:
        # Connection string
        conn_str = (
            f"DRIVER={{ODBC Driver 17 for SQL Server}};"
            f"SERVER={server};"
            f"DATABASE={database};"
            f"UID={username};"
            f"PWD={password}"
        )

        # Connect to SQL Server
        conn = pyodbc.connect(conn_str)
        cursor = conn.cursor()

        # Query to get all user-defined tables
        cursor.execute("""
            SELECT TABLE_SCHEMA, TABLE_NAME 
            FROM INFORMATION_SCHEMA.TABLES 
            WHERE TABLE_TYPE = 'BASE TABLE'
        """)

        tables = cursor.fetchall()

        if tables:
            print("Tables in the SQL Server database:")
            for schema, name in tables:
                print(f" - {schema}.{name}")
        else:
            print("No tables found in the database.")

    except pyodbc.Error as e:
        print(f"SQL Server error: {e}")
    finally:
        if conn:
            conn.close()

# Example usage
if __name__ == "__main__":
    list_tables_mssql(
        server="localhost\\SQLEXPRESS",
        database="YourDatabaseName",
        username="YourUsername",
        password="YourPassword"
    )

#Output Views
import pyodbc

def list_views_mssql(server, database, username, password):
    try:
        # Connection string
        conn_str = (
            f"DRIVER={{ODBC Driver 17 for SQL Server}};"
            f"SERVER={server};"
            f"DATABASE={database};"
            f"UID={username};"
            f"PWD={password}"
        )

        # Connect to the SQL Server
        conn = pyodbc.connect(conn_str)
        cursor = conn.cursor()

        # Query to get all views in the database
        cursor.execute("""
            SELECT TABLE_SCHEMA, TABLE_NAME 
            FROM INFORMATION_SCHEMA.VIEWS
        """)

        views = cursor.fetchall()

        if views:
            print("Views in the SQL Server database:")
            for schema, name in views:
                print(f" - {schema}.{name}")
        else:
            print("No views found in the database.")

    except pyodbc.Error as e:
        print(f"SQL Server error: {e}")
    finally:
        if conn:
            conn.close()

# Example usage
if __name__ == "__main__":
    list_views_mssql(
        server="localhost\\SQLEXPRESS",
        database="YourDatabaseName",
        username="YourUsername",
        password="YourPassword"
    )

[SeifElDinYasser]

I think it's a nice work-around to get the desired results, I was making sure that there's no property from gdb's items' properties that clarify the views and other items. but I think your your workflow would be easier to implement.
Many thanks.

[RyanUthoff]

Yeah, I'm not aware of a property that can be programmatically accessed within Esri to determine if it is a table or view (non-versioned Esri views).

Using the arcpy Describe function, you can determine a base table's registered as versioned view name, but that would only apply to datasets that are registered as versioned. You still wouldn't be able to determine views that are created outside of Esri's versioning.

[AlfredBaldenweck]

Related: Catalog: Add view icon

[SeifElDinYasser]

I was looking for something that can I get it as a property in my python script, this thing shouldn't depend on user activity with the gdb and its feature classes and tables.

[AlfredBaldenweck]

Posted an Idea here: Add property to table objects determining if they ... - Esri Community