Database With Fun (While 1 = 1)

declare @T table   (   Col varchar(20)   )   insert into @T   Select 'WO-012345' --'images/test1.jpg'   --union all   --Select 'images/test2.png'   --union all   --Select 'images/test3.jpg'   --union all   --Select 'images/test4.jpeg'   --union all   --Select 'images/test5.jpeg'  Select substring( col,charindex('-',Col)+1,6 ) from @T

SELECT t.name AS table_name, SCHEMA_NAME(schema_id) AS schema_name, c.name AS column_name FROM sys.tables AS t INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID WHERE c.name LIKE '%gradient%' ORDER BY schema_name, table_name;

CREATE PROC SearchAllTables ( @SearchStr nvarchar(100) ) AS BEGIN /* exec SearchAllTables @SearchStr = 'transparent' */ CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630)) SET NOCOUNT ON DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110) SET  @TableName = '' SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''') WHILE @TableName IS NOT NULL BEGIN     SET @ColumnName = ''     SET @TableName =     (         SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))         FROM    INFORMATION_SCHEMA.TABLES         WHERE       TABLE_TYPE = 'BASE TABLE'             AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName             AND OBJECTPROPERTY(             ...

;WITH stored_procedures AS ( SELECT o.name AS proc_name, oo.name AS table_name, ROW_NUMBER() OVER(partition by o.name,oo.name ORDER BY o.name,oo.name) AS row FROM sysdepends d INNER JOIN sysobjects o ON o.id=d.id INNER JOIN sysobjects oo ON oo.id=d.depid WHERE o.xtype = 'P') SELECT proc_name, table_name FROM stored_procedures --WHERE row = 1 where proc_name like 'usp_insTextSize' ORDER BY proc_name,table_name

Issue with BLOB rows- Method 1 Set the SubscriptionStreams parameter of the Distribution Agent to a value of 1. Method 2 You can set the OleDbStreamThreshold parameter of the Distribution Agent to a value that is greater than the largest data size for binary large object (BLOB) columns that have to be replicated. Then, the Distribution Agent will not bind binary large object (BLOB) data as a stream. Starting from SQL Server 2008, binary large object (BLOB) data is bound as a stream when the data size of the binary large object (BLOB) data is less than the value of the OleDbStreamThreshold parameter. This behavior is true by default. USE TestDB; GO EXEC sp_configure 'show advanced options', 1 ; RECONFIGURE ; GO EXEC sp_configure 'max text repl size'; GO -- OUTPUT: -- name                    minimum  maximum  ...

Setting up transactional replication using T-SQL Step 1: Set up a shared folder for snapshots. Step 2: Configure the distributor and publisher: use master exec sp_adddistributor @distributor = N'SSLMATTB2' , @password = N'' GO exec sp_adddistributiondb @database = N'distribution' , @data_folder = N'C:\MSSQL\SQLData' , @log_folder = N'C:\MSSQL\SQLLogs' , @log_file_size = 2 , @min_distretention = 0 , @max_distretention = 72 , @history_retention = 48 , @security_mode = 1 GO use [distribution] if (not exists ( select * from sysobjects where name = 'UIProperties' and type = 'U ')) create table UIProperties(id int) if (exists ( select * from ::fn_listextendedproperty('SnapshotFolder' , 'user' , 'dbo' , 'table' , 'UIProperties' , null, null))) EXEC sp_updateextendedproperty N'SnapshotFolder' , N'C:\MSSQL\SQL_Share' , 'user...

If you get the error message for Tempdb Transaction log is full. Msg 9002, Level 17, State 4, Procedure sp_helpdb, Line 19 The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases Solution: 1 -- First check Tempdb Tran log file size using  dbcc sqlperf(logspace) -- tempdb 999.9922 99.99628 0 USE MASTER GO ALTER DATABASE TEMPDB MODIFY FILE (NAME='templog', SIZE=1500MB) Solution:2   ALTER DATABASE Tempdb  ADD LOG FILE  ( NAME = tempdblog2, FILENAME = 'E:\MSSQL.1\MSSQL\DATA\tempdblog2.ldf', SIZE = 10MB, MAXSIZE = 100MB, FILEGROWTH = 10MB)