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]

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"
    )

[AlfredBaldenweck]

Related: Catalog: Add view icon