sql-server-2016

How to search SQL column containing JSON array

For doing a search in a JSON array, one needs to use OPENJSON DECLARE @table TABLE (Col NVARCHAR(MAX)) INSERT INTO @table VALUES (‘{“names”:[“Joe”,”Fred”,”Sue”]}’) SELECT * FROM @table WHERE ‘Joe’ IN ( SELECT value FROM OPENJSON(Col,’$.names’)) or as an alternative, one can use it with CROSS APPLY. SELECT * FROM @table CROSS APPLY OPENJSON(Col,’$.names’) WHERE value=”Joe”

SQL Server: Filter output of sp_who2

You could try something like DECLARE @Table TABLE( SPID INT, Status VARCHAR(MAX), LOGIN VARCHAR(MAX), HostName VARCHAR(MAX), BlkBy VARCHAR(MAX), DBName VARCHAR(MAX), Command VARCHAR(MAX), CPUTime INT, DiskIO INT, LastBatch VARCHAR(MAX), ProgramName VARCHAR(MAX), SPID_1 INT, REQUESTID INT ) INSERT INTO @Table EXEC sp_who2 SELECT * FROM @Table WHERE …. And filter on what you require.

How do I move a table into a schema in T-SQL

ALTER SCHEMA TargetSchema TRANSFER SourceSchema.TableName; If you want to move all tables into a new schema, you can use the undocumented (and to be deprecated at some point, but unlikely!) sp_MSforeachtable stored procedure: exec sp_MSforeachtable “ALTER SCHEMA TargetSchema TRANSFER ?” Ref.: ALTER SCHEMA SQL 2008: How do I change db schema to dbo

Create nested JSON arrays using FOR JSON PATH

You can use the following query: SELECT pr.person_id AS [person.id], pr.person_name AS [person.name], ( SELECT pt.pet_id AS id, pt.pet_name AS name FROM @Pets pt WHERE pt.pet_owner=pr.person_id FOR JSON PATH ) AS [person.pet] FROM @Persons pr FOR JSON PATH, ROOT(‘pet owners’) For more information, see https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/10/09/returning-child-rows-formatted-as-json-in-sql-server-queries/