In this article, I will explain some general queries to you. I think every developer should be aware of these queries. These queries are not related to a specific SQL topic. But knowing about such queries can solve some complex tasks and can be used in many scenarios, so I decided to write an article about these queries.
Query 1 - Split a comma separated string
Supposing we have a character string that contains a list of countries separated by commas Ex: 'France, USA, Morocco, Japan' and that we want to have this list by line.
Here is the query that allows you to have that:
CREATE TABLE #Country (CountryName VARCHAR (80)) INSERT #Country VALUES ('France, USA, Morocco, Japan') SELECT LTRIM (RTRIM (mnvalue ('. [1]', 'varchar (8000)'))) AS CountryName FROM (SELECT CAST (' '+ REPLACE (CountryName,', ',' ') +' 'AS XML) AS x FROM #Country) t CROSS APPLY x.nodes (' / XMLRoot / RowData ') m (n)
result:
Query 2 - Last Scripts executed
For a list of the latest scripts run on a database, here is the query:
SELECT execquery.last_execution_time AS [Date Time], execsql.TEXT AS [Script] FROM sys.dm_exec_query_stats AS execquery CROSS APPLY sys.dm_exec_sql_text (execquery.sql_handle) AS execsql ORDER BY execquery.last_execution_time DESC
result:
Query 3 - List of tables with number of records
CREATE TABLE #Tab (Table_Name [varchar] (max), Total_Records int); EXEC sp_MSForEachTable @ command1 = 'Insert Into #Tab (Table_Name, Total_Records) SELECT' '?' ', COUNT (*) FROM?' SELECT * FROM #Tab t ORDER BY t.Total_Records DESC; DROP TABLE #Tab;
result:
Request 4 - List of modified stored procedures in the last N days
SELECT name, modify_date FROM sys.objects WHERE type = 'P' AND DATEDIFF (D, modify_date, GETDATE ()) <100
result:
Request 5 - Get all tables that don't have a primary key
SELECT name AS Table_Name FROM sys.tables WHERE OBJECTPROPERTY (OBJECT_ID, 'TableHasPrimaryKey') = 0 ORDER BY Table_Name;
result:
Request 6 - Get information about table columns
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = 'FactFacture'
result:
Query 7 - Display the contents of a stored procedure
exec sp_helptext @objname = 'getScoringPerfPCID' / * Prod stock Name * /
result:
Request 8 - List of stored procedures associated with a table
SELECT DISTINCT o.name, o.xtype FROM syscomments c INNER JOIN sysobjects o ON c.id = o.id WHERE c.TEXT LIKE '% FactPricingOverview%' / * Table Name * / AND o.xtype = 'P'
result:
Request 9 - List of primary keys and foreign keys of a database
SELECT DISTINCT Constraint_Name AS [Constraint], Table_Schema AS [Schema], Table_Name AS [TableName] FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE GO
result:
Request 10 - Disable all constraints for a table
ALTER TABLE Table_Name NOCHECK CONSTRAINT ALL
Leave a comment