I would like to know the total number of rows for each table. And that for all tables at once. Is this possible with H2? I’m thinking of a table: |table name | row size | At the moment I’m using the current version 1.3.170 (2012-11-30). Answer You would need to use a user-defined function, for exa…
Tag: sql
Display default access privileges for relations, sequences and functions in Postgres
After altering the default privileges on a Postgres database object, how can you view them? For instance, if you grant all privileges to role_name for all tables created in the schema schema_name: Answer Using a SQL query Where the value of defaclobjtype is r = relation (table, view), S = sequence, f = functi…
sp_change_users_login ‘auto-fix’, ”
How can I execute the sp_change_users_login so that it will auto-fix all local sql accounts? in other words I can run the command to view all local accounts: I now want those list of users to be used in the sp_change_users_login procedure. Answer You could use a cursor to get the list of names, and then loop …
Problems with SQL syntax? MAX not working
postid is a increasing numeric value (1,2,3…). At the moment, the highest value is 49, but It’s pulling up 1. It seems to be ignoring the MAX statement. otherwise everything else is working great. Answer That’s not valid syntax, which engine is running it? You either need: to get multiple re…
Select one row with MAX(column) for known other several columns without subquery
My table contains votes of users for different items. It has the following fields: I understand how to get the last vote of #user# for #item#, but it uses subquery: It works, but it looks quite stupid to me… There should be a more elegant way to get this one record. I tried the approach suggested here, …
Concat SQL result
I’m quite new to SQL and I am using MS Access’s ListBox component which requires a query to display data. The following are the tables involved to make the query: TABLE: Blocks +—-+——–+ | ID | B_Name | +—-+——–+ | 1 | blockF | | 2 | blockE | . . . TAB…
How to use the group join result to join another tables using LINQ?
I am not able to use the result of group join and then further applying the join with other tables. My SQL query is : and what I have done so far is: but now this result is not at all further used by me to create a join with other tables. I want to ask how to join further?
Execute SQL Asynchronously or change locking from Trigger
I have a complex unit of work from an application that might commit changes to 10-15 tables as a single transaction. The unit of work executes under snapshot isolation. Some of the tables have a trigger which executes a stored procedure to log messages into a queue. The message contains the Table Name, Key an…
Having trouble with WHERE DATEDIFF in my SQL
I think I’m going about this wrong. I want to pull the rows in my database that are less than a day old, in PHP. I’m using Laravel, but that’s not relevant. Here is what I have: However, that isn’t return the right results. It’s return results older than a day. Answer This query …
Division with Aggregate Functions in SQL Not Behaving as Expected
I’m trying to do some crosstabs in SQL Server 2008 R2. That part is alright, however, if I try to get percentages for each cell, I run into a problem. Here is a distilled use case: A survey where people give their favorite color and their favorite fruit. I’d like to know how many like a given frui…