nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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.

Najděte více příspěvků označených jako

Accepted answers

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

All comments

abaldenweck@blm.gov_BLM_EGIS

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

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.

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.

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.

abaldenweck@blm.gov_BLM_EGIS

Related: Catalog: Add view icon

ryanuthoff

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

Připojte se k diskusi

Přihlášení členové mohou přispívat, sledovat aktualizace a další. Jste tu noví? Zaregistrujte si bezplatný účet.

Rychlé odkazy

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.