Lets start with DBMS

DBMS - Database Management Systems are computer software application that interacts with users, other applications and data storage (database). A general DBMS allows us to creation, querying, updates and maintenance of Database.

Database - is an integrated and organized collection of logically related records or files that are stored on computer system which consolidates previously stored data.

A database is made up of a collection of tables that stores a specific set of structured data. A table contains a collection of rows- refereed as records or tuples and Columns- refereed as attributes. Each column is designated to store a certain type of information.

. 

Day to day need in Sql

1. Find SP containing text

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_DEFINITION LIKE '%Foo%' 
AND ROUTINE_TYPE='PROCEDURE'

2. Find table with given column and it's length

SELECT Column_Name, Table_Name, Col_Length(Table_Name,Column_Name)
FROM INFORMATION_SCHEMA.Columns 
WHERE Column_Name LIKE '%Name%' 

3. Find SP containing text
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_DEFINITION LIKE '%Foo%' 
AND ROUTINE_TYPE='PROCEDURE'

4. Change column size throughout DB with given Column Name
DEALLOCATE My_Cursor
DECLARE @COLUMN_NAME VARCHAR(100)
DECLARE @TABLE_NAME VARCHAR(100)
DECLARE @tabLen VARCHAR(100)
Declare @sql as VARCHAR(500)=''
DECLARE My_Cursor CURSOR  DYNAMIC OPTIMISTIC FOR
SELECT COLUMN_NAME,
 TABLE_NAME  , COL_LENGTH ( TABLE_NAME , COLUMN_NAME ) As tabLen
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME LIKE '%TIN'
and COL_LENGTH ( TABLE_NAME , COLUMN_NAME )>-1
AND COL_LENGTH ( TABLE_NAME , COLUMN_NAME )<20
AND DATA_TYPE = 'varchar' 

OPEN  My_Cursor
FETCH NEXT FROM My_Cursor INTO @COLUMN_NAME,@TABLE_NAME,@tabLen
WHILE @@FETCH_STATUS <> -1
 BEGIN
  
 SET @sql=''
 SET @sql= 'ALTER TABLE '+@TABLE_NAME +
' ALTER COLUMN '+@COLUMN_NAME+' VARCHAR(20)'
 select @sql
 EXEC 
 ( @sql)
 
  
  
  
FETCH NEXT FROM My_Cursor INTO 
 @COLUMN_NAME,@TABLE_NAME,@tabLen
END




sqlcmd -S <server Name> -U sa -P sapassword -i inputquery_file_name -o outputfile_name

Restore DB from UNC Path

Restore DB from UNC Path

While restoring MS Sql DB from a UNC path we at times face an error saying

 - Can not find the path specified.

Cause :
This error is due to sql server not having access permission.

Issue can be resolved using following :


EXEC sp_configure 'show advanced options', 1
GO

-- Update currently configured values for advanced options.

RECONFIGURE
GO

-- To enable xp_cmdshell

EXEC sp_configure 'xp_cmdshell', 1
GO

-- Update currently configured values for advanced options.
RECONFIGURE
GO

EXEC xp_cmdshell 'NET USE Z: \\Path Password /USER:domain\uName'