20 Eylül 2018 Perşembe

Sql'de Tüm database'de kelime aramak

Sql'de Tüm database'de kelime aramak

declare @SearchTerm nvarchar(12)
set @SearchTerm = 'WORD'
CREATE TABLE #results
  (
    [database]   SYSNAME,
    [schema]     SYSNAME,
    [table]      SYSNAME,
    [column]     SYSNAME,
    ExampleValue NVARCHAR(1000)
  );

  DECLARE
    @DatabaseCommands  NVARCHAR(MAX) = N'', 
    @ColumnCommands NVARCHAR(MAX) = N'';

  SELECT @DatabaseCommands = @DatabaseCommands + N'
    EXEC ' + QUOTENAME(name) + '.sys.sp_executesql
        @ColumnCommands, N''@SearchTerm NVARCHAR(MAX)'', @SearchTerm;'
    FROM sys.databases
    WHERE database_id  > 4  -- non-system databases
      AND[state]      = 0-- online
      AND user_access  = 0; -- multi-user

    SET @ColumnCommands = N'DECLARE @q NCHAR(1),
          @SearchCommands NVARCHAR(MAX);

SELECT @q = NCHAR(39),
  @SearchCommands = N''DECLARE @VSearchTerm VARCHAR(255) = @SearchTerm;'';

    SELECT @SearchCommands = @SearchCommands + CHAR(10) + N''

      SELECT TOP(1)
        [db]     = DB_NAME(),
        [schema] = N'' + @q + s.name + @q + '', 
        [table]  = N'' + @q + t.name + @q + '',
        [column] = N'' + @q + c.name + @q + '',
        ExampleValue = LEFT('' + QUOTENAME(c.name) + '', 1000)
      FROM '' + QUOTENAME(s.name) + ''.'' + QUOTENAME(t.name) + ''
      WHERE '' + QUOTENAME(c.name) + N'' LIKE @'' + CASE
        WHEN c.system_type_id IN(35, 167, 175) THEN ''V'' 
        ELSE '''' END + ''SearchTerm;'' 

    FROM sys.schemas AS s
    INNER JOIN sys.tables AS t
    ON s.[schema_id] = t.[schema_id]
    INNER JOIN sys.columns AS c
    ON t.[object_id] = c.[object_id]
    WHERE c.system_type_id IN (35, 99, 167, 175, 231, 239)
      AND c.max_length >= LEN(@SearchTerm);

PRINT @SearchCommands;
EXEC sys.sp_executesql @SearchCommands,
  N''@SearchTerm NVARCHAR(255)'', @SearchTerm;';

  INSERT #Results
  (
    [database],
    [schema],
    [table],
    [column],
    ExampleValue
  )
  EXEC[master].sys.sp_executesql @DatabaseCommands,
   N'@ColumnCommands NVARCHAR(MAX), @SearchTerm NVARCHAR(255)', 
    @ColumnCommands, @SearchTerm;

  SELECT[Searched for] = @SearchTerm;

  SELECT[database],[schema],[table],[column],ExampleValue
FROM #Results 
    ORDER BY[database],[schema],[table],[column];

19 Eylül 2018 Çarşamba

Sql'de Tablo Oluşturur ve Veri aktarımı yapan Stored Procedure cümlesi


--Kullanım:
--[dbo].[Sp_TabloAktar] @NewDatabaseName='TestDb2',@OldDatabaseName='BORSA',@TableName='Ay'

USE master
GO
/****** Object:  StoredProcedure [dbo].[STPMizanGider]    Script Date: 19.09.2018 16:53:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

alter PROCEDURE [dbo].[Sp_TabloAktar]
      -- Add the parameters for the stored procedure here
      @NewDatabaseName nvarchar(50),
      @OldDatabaseName nvarchar(50),
      @TableName nvarchar(50)
AS
BEGIN
      SET NOCOUNT ON;
      DECLARE @IntVariable int;
      DECLARE @SQLString nvarchar(4000);
      DECLARE @ParmDefinition nvarchar(500);

      /* Build the SQL string one time.*/

      SET @SQLString =
             N' IF OBJECT_ID(N' + '''$db$.dbo.$tbl$''' + ', N''U'') IS NOT NULL
                  BEGIN
                  DROP TABLE $db$.dbo.$tbl$
                  end
                  Select * into $db$.dbo.$tbl$  from  $db1$.dbo.$tbl$ ';

     
      SET @SQLString = REPLACE(@SQLString, '$db$', @NewDatabaseName);
      SET @SQLString = REPLACE(@SQLString, '$db1$', @OldDatabaseName);
      SET @SQLString = REPLACE(@SQLString, '$tbl$', @TableName);
      /* Execute the string with the first parameter value. */
     
      PRINT @SQLString

      EXECUTE sp_executesql @SQLString;
      /* Execute the same string with the second parameter value. */
     
END

Sql'de Kolonlara göre script oluşturma



 Sql'de Kolonlara göre script oluşturma;


Select 'if not exists (select * from syscolumns
  where id=object_id('''+TableName+''') and name='''+ColumnName+''')
    alter table '+TableName+' add '+ColumnName+' '+Datatype+' NULL '+ CHAR(13)+CHAR(10) +'
' as SS, * from (
SELECT
    c.name 'ColumnName',
    Case when t.Name='nvarchar' then t.Name +'(' + CAST(c.max_length AS varchar) +')' else  t.Name end as 'Datatype',
    c.max_length 'MaxLength',
    c.precision ,
    c.scale ,
    c.is_nullable,
    ISNULL(i.is_primary_key, 0) 'PrimaryKey',
       Object_Name(c.object_id) AS TableName,
       c.object_id

FROM   
    sys.columns c
INNER JOIN
    sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN
    sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN
    sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE
c.object_id>100
    --c.object_id = OBJECT_ID('UrunAlimSatim')
      
       ) as T where 
       T.TableName not like ('Grn%')
       and T.TableName not like ('Vw%')
       and T.TableName not like ('plan_%')  
             and T.TableName not like ('Get%')
             and T.TableName not like ('Rhb%')
             and T.TableName not like ('V_%')
             and T.TableName not like ('SL%')
             and T.TableName not like ('rpt%')
                    and T.TableName not like ('sqlagent%')
                           and T.TableName not like ('queue%')
                           and T.TableName not like ('sys%')
                           and T.TableName not like ('filestream%')
                           and T.TableName not like ('file%')
                           and T.TableName not like ('SeansSaatParam1%')
                           and T.TableName not like ('takas_gelen%')
                           and T.TableName not like ('Takas')
                           and T.TableName not like ('TakasSonucTMOIlk')
       and T.TableName not like ('TakasSonucTMO')
       and T.TableName not like ('Takas_')
       and T.TableName not like ('SeansSonuc')
       and T.TableName not like ('TblTmoEmirGelenMurat')
       and T.TableName not like ('Takas')
       ORDER by object_id