Return name and identity for all tables
-
Little help with returning both the name and the current identity for all tables? If a table doesn't have any rows, instead of the current identity, I'd like to return 0. The database will be in single user mode during the query if that helps. The following query is close but it still returns 1 for tables that don't have any rows. Somehow the CASE expression isn't right. Thanks in advance.
SELECT TABLE_NAME, CASE WHEN COUNT(TABLE_NAME) = 0 THEN 0 ELSE IDENT_CURRENT(TABLE_NAME) END FROM INFORMATION_SCHEMA.TABLES WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasIdentity') = 1 AND TABLE_TYPE = 'BASE TABLE' GROUP BY TABLE_NAME -
Little help with returning both the name and the current identity for all tables? If a table doesn't have any rows, instead of the current identity, I'd like to return 0. The database will be in single user mode during the query if that helps. The following query is close but it still returns 1 for tables that don't have any rows. Somehow the CASE expression isn't right. Thanks in advance.
SELECT TABLE_NAME, CASE WHEN COUNT(TABLE_NAME) = 0 THEN 0 ELSE IDENT_CURRENT(TABLE_NAME) END FROM INFORMATION_SCHEMA.TABLES WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasIdentity') = 1 AND TABLE_TYPE = 'BASE TABLE' GROUP BY TABLE_NAMESELECT CASE WHEN COUNT(TABLE_NAME) = 0 THEN 0 WHEN COUNT(TABLE_NAME) > 0 THEN IDENT_CURRENT(TABLE_NAME) END FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' GROUP BY TABLE_NAME
I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.aktualiteti.com
-
SELECT CASE WHEN COUNT(TABLE_NAME) = 0 THEN 0 WHEN COUNT(TABLE_NAME) > 0 THEN IDENT_CURRENT(TABLE_NAME) END FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' GROUP BY TABLE_NAME
I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.aktualiteti.com
Maybe I'm missing something here. How can that ever return 0? If I have understood it correctly, this is what the query will do: Select the rows from TABLES with type "BASE TABLE" Group them by TABLE_NAME Counts the number of rows for each TABLE_NAME If the count is 0, return 0 If the count is not 0, return the identity of the table But, surely the only way COUNT(TABLE_NAME) can be 0 is if there are no rows in TABLES with this TABLE_NAME and type "BASE TABLE". In which case, this query would never pick up that table name, so it would never appear in the result set. So for any TABLE_NAME that this query picks up, the count will always be greater than 0.