This wiki page contains a collection of useful SQL queries and tips for SQL Server. It includes queries to remove project access, find fields across tables, and lookup customized batch sizes per plugin using XML configuration. The page serves as a quick reference guide and cheatsheet for common SQL tasks.
This query will remove all users from a project except the super user
Here is how you can delete rows where UserId is not equal to 1 in SQL Server:
use prj_dbname_01 -- change to project database name
DELETE FROM VenioPCD.dbo.tbl_pj_UserProjectAssociation
WHERE UserId <> 1
AND ProjectId IN (
SELECT ProjectId
FROM VenioPCD.dbo.tbl_pj_ProjectSetup
WHERE DatabaseInstanceName = DB_NAME()
)
The key steps are:
DELETE statement to delete rows from the tbl_pj_UserProjectAssociation table.WHERE clause to filter for UserId not equal to 1.tbl_pj_ProjectSetup table to only delete for the current database instance. This is done by checking the DatabaseInstanceName matches the current DB name returned by DB_NAME().IN clause selects ProjectIds from tbl_pj_ProjectSetup filtered to the current database.JOIN since the WHERE clause links the two tables on ProjectId.tbl_pj_UserProjectAssociation where the UserId is not 1, only for the current database instance.This script searches a SQL Server database for tables that contain a column name matching a search string pattern.
use <dbname> -- change to database you want to search
SELECT Table_Name,
Column_Name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = DB_NAME()
AND COLUMN_NAME LIKE '%<searchstring>%'; -- change to the field you are searching for
The key steps are:
USE: Change context to the database you want to search.SELECT: Retrieve the Table_Name and Column_Name from the INFORMATION_SCHEMA.COLUMNS system view.WHERE: Filter the results where:TABLE_CATALOG is the current database name returned by DB_NAME()COLUMN_NAME matches the search string pattern using LIKE.'%<searchstring>%' which will match any column name containing the search string.This allows you to easily search for column names matching a pattern across all tables in the current database. The INFORMATION_SCHEMA provides metadata about the database schema.
This query can be used to get a list of all "JobTypes" with respective "Default Batch Size" for a specific station.
This script retrieves customized batch size settings per plugin for a service using the service's XML configuration.
DECLARE @serviceId NVARCHAR(MAX) ='<ServiceId>' --Please provide serviceId. Check table tbl_ds_ServerDetail for serviceId
declare @table table (Name nvarchar(500), BatchSize INT)
INSERT INTO @table VALUES
('CG', 1)
,('Distributed Service Search', 5)
,('Email Analysis', 5000)
,('Entity Extraction',500)
,('File Export', 500)
,('Fulltext Generation', 100)
,('Html-Conversion', 100)
,('Inclusive Email Identification', 500)
,('Indexing', 5000)
,('Ingestion-Legacy', 10)
,('Ingestion', 25)
,('Ingestion For File Replacement-Legacy', 10)
,('Ingestion For File Replacement', 25)
,('Language Identificaiton', 5000)
,('Missing Email Generation', 100)
,('Native Prefetch', 5000)
,('Near Duplicates', 500)
,('OCR', 15)
,('OCR Generated Tiff', 15)
,('OCR Redacted Tiff', 15)
,('Predict', 5000)
,('Print', 500)
,('Production-Copy', 500)
,('Production-Fulltext', 500)
,('Production-Image', 500)
,('Production-Native', 500)
,('Production-PDFConversion', 500)
,('Promote Data-Document Information', 5000)
,('Promote Data-File Copy', 1000)
,('Promote Data-Work Product', 5000)
,('RTF Conversion', 100)
,('Scan',500)
,('TF', 15)
,('TIFF', 15)
,('Tiff Pre-QC', 15)
,('Transcibe', 15)
DECLARE @Settings XML
SELECT @Settings=Settings FROM tbl_ds_ServerDetail WITH(NOLOCK) WHERE ServiceId=@serviceId
SELECT
T.Name, ISNULL(A.[Value],T.BatchSize) AS BatchSize
FROM @table T
LEFT JOIN
(SELECT Logins.L.query('data(../@Name)').value('.', 'varchar(60)') as Plugin, Logins.L.query('data(Key)').value('.', 'varchar(60)') as [Key],Logins.L.query('data(Value)').value('.', 'varchar(60)') as [Value]
FROM @Settings.nodes('/Settings/Plugins/Plugin/Setting') Logins (L))A
ON A.[Key] IN ('Default Batch Size','Default Batch Maximum File Count') AND T.Name=A.Plugin
The key steps are:
In summary, this script uses the XML configuration to return custom or default batch sizes per plugin for a given service. The temp table provides the fallback defaults.