HOW TO GET ALL COLUMN DATA IN A SINGLE ROW BY USING XML PATH & STUFF TRICK

DECLARE @CodeNameString varchar(100)

SELECT 
   @CodeNameString = STUFF( (SELECT ',' + CodeName 
                             FROM dbo.AccountCodes 
                             ORDER BY Sort
                             FOR XML PATH('')), 
                            1, 1, '')

EXAMPLE: to get dynamic sript in a single column and 

         execute 

        some other way looping throw row by row execution

 If Exists (select * from sysobjects where id = object_id(N'[dbo].[table_list]') 

and OBJECTPROPERTY(id, N'IsUserTABLE') = 1)
DROP TABLE table_list

CREATE TABLE [table_list]
(    
    
    createtab NVARCHAR(MAX)
)
INSERT INTO table_list(createtab)
SELECT ' IF EXISTS(SELECT * FROM  Staging_Data.dbo.sysobjects 

WHERE ID = Object_Id(N''dbo.tbl_tkn_'+ CAST(token_schema_details_id as varchar(5)) + ''' ) 

and OBJECTPROPERTY(id, N''IsUserTable'') = 1)'+CHAR(10)+'BEGIN '
      + 'DROP TABLE Staging_Data.dbo.tbl_tkn_' + CAST(token_schema_details_id as varchar(5)) + ' END '
 +'  CREATE TABLE Staging_Data.dbo.tbl_tkn_' + CAST(token_schema_details_id as varchar(5)) +

 ' ( primary_key_value VARCHAR(50), card_acct_no VARCHAR(100), token_value VARCHAR(100) )'+CHAR(10) AS create_table
 FROM Token_Management.dbo.Token_Schema_Details WITH(NOLOCK)
 
 WHERE  [server] = 'E2PRDDBS01'
 


DECLARE @CodeNameString VARCHAR(max)
SELECT 
   @CodeNameString = STUFF( (SELECT '   ' + createtab 
                             FROM table_list
                             
                             FOR XML PATH('')), 
                            1, 1, '')
                            
select @CodeNameString as create_temptable

--EXEC( @CodeNameString) 

HOW TO GET ALL STORED PROCEDURES DYNAMICALLY IN A SERVER

CREATE TABLE #x(db SYSNAME, s SYSNAME, p SYSNAME);

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'INSERT INTO #x SELECT ''' + name + ''',s.name, p.name
  FROM ' + QUOTENAME(name) + '.sys.schemas AS s
  INNER JOIN ' + QUOTENAME(name) + '.sys.procedures AS p
  ON p.schema_id = s.schema_id;
' FROM sys.databases WHERE database_id > 4:

EXEC sp_executesql @sql;

SELECT db,s,p FROM #x ORDER BY db,s,p;

DROP TABLE #x;

GET ALL TABLES IN A SERVER BY USING SYSTABLES:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'INSERT INTO #x SELECT ''' + name + ''',s.name, p.name
  FROM ' + QUOTENAME(name) + '.sys.schemas AS s
  INNER JOIN ' + QUOTENAME(name) + '.sys.tables AS p
  ON p.schema_id = s.schema_id;
' FROM sys.databases WHERE database_id > 4

 print @sql

impotant sys table to get sp's details:

SELECT [Routine_Name] 
FROM [INFORMATION_SCHEMA].[ROUTINES]
WHERE [ROUTINE_TYPE] = 'PROCEDURE'

 SELECT OBJECT_NAME(object_id) FROM sys.sql_modules 
  WHERE definition LIKE '%ES_PURGE%'

  SELECT OBJECT_NAME(ID) AS SP_NAME FROM syscomments 

 WHERE text LIKE '%ES_PURGE%' 

SELECT [Name] FROM [sys].[procedures]

SELECT [Name] FROM [sys].[objects] WHERE [type] = 'P'

SELECT [Name] FROM [sys].[all_objects] WHERE [Type] = 'P' AND [Is_MS_Shipped] = 0

SELECT [Name] FROM [dbo].[sysobjects] WHERE [XType] = 'P'

HOW TO LOOP ALL DATABASES TO GET REQUIRED COLUMN DETAILS

SET ANSI_WARNINGS OFF;
SET FMTONLY OFF;
SET NOCOUNT ON
DECLARE  @Db_name TABLE( database_name  NVARCHAR(MAX),id INT IDENTITY(1,1))

INSERT INTO @Db_name
SELECT name  FROM sys.databases WHERE name NOT IN  ('SQL_ADMIN_DB','master','EDWARD','Policy_Accounting_Production'
,'Lookup_Tables_Db','ignite_repository','AdventureWorks')

--SELECT * FROM  @Db_name

DECLARE @cnt INT
SELECT@cnt = COUNT(*)  FROM @Db_name

IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp

CREATE TABLE  #temp( database_name  VARCHAR(MAX),table_name  VARCHAR(MAX),column_name VARCHAR(MAX))

--select @cnt
While (@cnt>0)
BEGIN
DECLARE @ddbname VARCHAR(100)
DECLARE @sql NVARCHAR(max) =' '


    SELECT @ddbname=database_name FROM @Db_name WHERE id=@cnt
  
      --SELECT @ddbname
    SET @sql=@sql+' INsert into #temp( database_name,table_name,column_name) Select  table_catalog,table_name,column_name  from   '+  @ddbname+'.information_schema.columns where column_name like ''%card%''  '

      EXEC (@sql )

     DELETE FROM @Db_name WHERE id=@cnt
  
  
SET @cnt=@cnt-1
END

;WITH cte_table AS(
SELECT database_name ,table_name,column_name FROM #temp)
--INSERT INTO Token_Management.dbo.Token_Schema_details
--(Database_name,Table_name,column_name)
SELECT Database_name,Table_name,column_name   FROM cte_table
WHERE table_name NOT LIKE 'sync%'
AND column_name NOT IN ( 'card_account_type','card_type','Card_type_id','pm_card_type','card_category',
'cardholder_name','card_payment_account_id','previous_card_type_id','card_type_cd','card_type_desc'
,'timecard_acknowledged_flag','CreditCardID','CreditCardApprovalCode','card_number_is_scrambled','CardType')


------------------------------Another simple way to use msforeachdb 



Create table #yourcolumndetails(DBaseName varchar(100)
                                                      , TableSchema varchar(50)
                                                     , TableName varchar(100)
                                                     ,ColumnName varchar(100)
                                                     , DataType varchar(100)
                                                     , CharMaxLength varchar(100)
                                                   )

EXEC sp_MSForEachDB @command1='USE [?];
    INSERT INTO #yourcolumndetails SELECT
    Table_Catalog
    ,Table_Schema
    ,Table_Name
    ,Column_Name
    ,Data_Type
    ,Character_Maximum_Length
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE Table_Name like ''%StringMapBase%'''  

select * from #yourcolumndetails
Drop table #yourcolumndetails