A Little More Dynamic SQL - Condtitional WHERE

/*
TEST: Find Data
*/
--create a table variable to store the source/target mappingsDECLARE
@SourceTarget Table(ID INT Identity (1,1),TargetServer nVarchar(300),TargetDB nVarchar(300),TargetSchema nVarchar(300),TargetTable nVarchar(300),SourceServer Nvarchar (300),SourceDB nVarchar(300),SourceSchema nVarchar(300),SourceTable nVarchar(300),SourceKeyCols nVarchar(300),TrgKeyCols nVarchar(300))
--populate the table with the ETL table mappings for this projectINSERT INTO @SourceTarget(TargetServer ,TargetDB ,TargetSchema ,TargetTable,SourceServer ,SourceDB ,SourceSchema ,SourceTable
)
VALUES(
(
'SERVER','DATABASE','dbo','Table1','Server2','databse2','schema','Table1.1'),'SERVER','DATABASE','dbo','Table2','Server2','databse2','schema','Table2.1')
--Declare the variables used in the testDECLARE
@StartCnt INT = 1,@MaxCnt INT = (SELECT COUNT (1) FROM @SourceTarget),@SourceServer nVarchar(100),@SourceDB nVarchar(100),@SourceSchema nVarchar(100),@SourceTable nVarchar(100),@TargetServer nVarchar(100),@TargetDB nVarchar(100),@TargetSchema
@TargetTable nVarchar(100), nVarchar(100),@Today nVarchar(8),
--used to dynamically determine the column names for the row meta data @Where nVarchar(MAX),
--use for creating the dynamic SQL@sql
nVarchar(MAX)
WHILE @StartCnt <= @MaxCnt
BEGIN--set the values for each four part table name we want to testSET @SourceServer = (SELECT SourceServer FROM @SourceTarget WHERE ID = @StartCnt)SET @SourceDB = (SELECT SourceDB FROM @SourceTarget WHERE ID = @StartCnt)SET @SourceSchema = (SELECT SourceSchema FROM @SourceTarget WHERE ID = @StartCnt)SET @SourceTable = (SELECT SourceTable FROM @SourceTarget WHERE ID = @StartCnt)SET @TargetServer = (SELECT TargetServer FROM @SourceTarget WHERE ID = @StartCnt)SET @TargetDB = (SELECT TargetDB FROM @SourceTarget WHERE ID = @StartCnt)SET @TargetSchema = (SELECT TargetSchema FROM @SourceTarget WHERE ID = @StartCnt)SET @TargetTable = (SELECT TargetTable FROM @SourceTarget WHERE ID = @StartCnt)SET @Today = (SELECT CONVERT(CHAR(8), GetDate(), 112))IF @SourceTable IN ('table1')
BEGIN SET @Where = 'WHERE Created_Date > '''
SET @Where = @Where + @TodaySET @Where = @Where + ''' '
and DATEPART(mm, Created_Date) = DATEPART(mm, Updated_Date)
and DATEPART(yy, Created_Date) = DATEPART(YY, Updated_Date)'SET @Where = @Where + 'and DATEPART(dd, Created_Date) = DATEPART(dd, Updated_Date)

ELSE
IFEND @SourceTable IN ('tabel2')
BEGINSET @Where = 'WHERE created_date > '''
SET @Where = @Where + @Today SET @Where = @Where + ''' '
and DATEPART(mm, Created_date) = DATEPART(mm, Updated_date)
and DATEPART(yy, Created_date) = DATEPART(YY, Updated_date)'SET @Where = @Where + 'and DATEPART(dd, Created_date) = DATEPART(dd, Updated_date)

ELSEEND
BEGINSET @Where = 'WHERE createdDate > '''
SET @Where = @Where + @TodaySET @Where = @Where + ''' '
DATEPART(dd, CreatedDttm) = DATEPART(dd, UpdatedDttm)
AND DATEPART(mm, CreatedDttm) = DATEPART(mm, UpdatedDttm)
AND DATEPART(yy, CreatedDttm) = DATEPART(YY, UpdatedDttm)
)
OR (
DATEPART(dd, CreatedDttm) = DATEPART(dd, DeletedDttm)
AND DATEPART(mm, CreatedDttm) = DATEPART(mm, DeletedDttm)
AND DATEPART(yy, CreatedDttm) = DATEPART(YY, DeletedDttm)
)
OR (
DATEPART(dd, UpdatedDttm) = DATEPART(dd, DeletedDttm)
AND DATEPART(mm, UpdatedDttm) = DATEPART(mm, DeletedDttm)
AND DATEPART(yy, UpdatedDttm) = DATEPART(YY, DeletedDttm)
)
'
SET @Where = @Where + 'AND ( END
SET @sql = ' + @SourceServer + '.' + @SourceDB + '.' + @SourceSchema + '.' + @SourceTable+ space(1) + 'WITH (NOLOCK) '


'+ space(1) + @Where + '


PRINTEXEC sp_executesql @sql OUTPUT @Today PRINT @WhereSET @StartCnt = @StartCnt +1;END

SELECT * FROM '





  
 

DB Stuff: Beyond sp_who

--This is a query designed to return all processes in all dbs on one server.



--declare a table to hold the results. allows for filtering and sorting

 DECLARE @TempTbl TABLE (DBName nVarchar(300), session_id INT, login_time nVarchar(300), host_name nVarchar(300), program_name nVarchar(300), host_process_id nVarchar(300), client_version nVarchar(300), client_interface_name nVarchar(300), security_id nVarchar(300), login_name nVarchar(300), nt_domain nVarchar(300), nt_user_name nVarchar(300), status nVarchar(300), context_info nVarchar(300), cpu_time nVarchar(300), memory_usage nVarchar(300), total_scheduled_time nVarchar(300), total_elapsed_time nVarchar(300), endpoint_id nVarchar(300), last_request_start_time nVarchar(300), last_request_end_time nVarchar(300), reads nVarchar(300), writes nVarchar(300), logical_reads nVarchar(300), is_user_process nVarchar(300), text_size nVarchar(300), language nVarchar(300), date_format nVarchar(300), date_first nVarchar(300), quoted_identifier nVarchar(300), arithabort nVarchar(300), ansi_null_dflt_on nVarchar(300), ansi_defaults nVarchar(300), ansi_warnings nVarchar(300), ansi_padding nVarchar(300), ansi_nulls nVarchar(300), concat_null_yields_null nVarchar(300), transaction_isolation_level nVarchar(300), lock_timeout nVarchar(300), deadlock_priority nVarchar(300), row_count nVarchar(300), prev_error nVarchar(300), original_security_id nVarchar(300), original_login_name nVarchar(300), last_successful_logon nVarchar(300), last_unsuccessful_logon nVarchar(300), unsuccessful_logons nVarchar(300), group_id INT)

INSERT INTO @TempTbl
SELECT DB_NAME(dbid),s.*
FROM sys.dm_exec_sessions s
LEFT OUTER JOIN sys.sysprocesses p
ON S.session_id = p.spid
WHERE dbid > 0SELECT *
FROM @TempTbl
WHERE
      login_name NOT IN ('sa', '<domain>\<server>', '<db_user>')
      AND DBName NOT IN ('master')
ORDER BY DBName, login_name

--This query is sp_who in a filterable fashion

DECLARE @WhoTbl TABLE
(SPID INT,[Status] nVarchar(300),
[Login] nVarchar(300),
HostName nVarchar(50),BlkBy varchar(300),DBName nVarchar(300),Command nVarchar(3000),CPUTime INT,DiskIO INT,LastBatch varchar(300),ProgramName nVarchar(300),SPID2 INT,REQUESTID INT)
INSERT INTO @WhoTbl
EXEC sp_who2

SELECT * FROM @WhoTbl
WHERE
DBName NOT IN ('master', 'msdb')
ORDER BY DBName

Simple Dynamic SQL

Here are a couple of examples to follow.

USE master;

Exec sp_MSforeachdb

' Declare @SQL varchar(max)    
 Set @SQL = ""    
 Select @SQL = @SQL +
 Coalesce(
 "Select Distinct
 *
  from "
    + name 
    + ".information_schema.tables     
    where table_name like ""addressr%""  UNION ","" )    
 from sys.databases where state_desc = "ONLINE"    
 set @SQL = 
 Substring(@SQL, 1, Len(@SQL) - 6)
 + " order by table_catalog  "     exec (@SQL)
 '

USE Master
GO

DECLARE

    @Server nvarchar(255) = 'serverone',
    @Database nvarchar(255) = 'northwind',
    @Schema nvarchar(255) = 'cust%',
    @Table nvarchar(255) = '%address%',
    @Col nVarchar(255) = '%',
    @sql nvarchar(4000)
      
    if @Schema is null
        set @Schema = 'dbo'

    set @sql =    'SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION FROM '
    + space(1) + @Server + '.' + @Database + '.INFORMATION_SCHEMA.COLUMNS'
    + space(1) +    'WHERE TABLE_CATALOG like ''' + @Database + ''''
    + space(1) +    'AND TABLE_SCHEMA like ''' + @Schema + ''''
    + space(1) +    'AND TABLE_NAME like ''' + @Table + ''''
    + space(1) +    'AND COLUMN_NAME like ''' + @Col + ''''
    + space(1) +    'ORDER BY TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME ASC'

    exec sp_executesql @sql output


DECLARE
@A nVarchar(100),
@Cont INT = 1,
@Cnt INT = (SELECT COUNT (1) FROM sys.objects where name like '%address%' and type_desc = 'user_table'),
@SQL nVarchar(4000)
DECLARE @Tbl TABLE (ID INT Identity, Name nVarchar(100));
--need to add dymamic schema identification here
--need to also query both views and tables
INSERT @Tbl
(Name)

SELECT
name FROM sys.objects where name like '%address%' and type_desc = 'user_table'
WHILE
@Cont <= @Cnt
BEGIN
SET
@A = (SELECT NAME FROM @Tbl WHERE ID = @Cont)
SELECT
@A AS 'Table'
SELECT
@SQL = 'SELECT COUNT (*) FROM '
SELECT
@SQL = @SQL + @A
EXEC
(@SQL)
SET
@Cont = @Cont + 1
END