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];
20 Eylül 2018 Perşembe
Sql'de Tüm database'de kelime aramak
Sql'de Tüm database'de kelime aramak
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
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
Kaydol:
Kayıtlar (Atom)