Sunday 27 September 2015

Script for Recent changes in SQL Server

SELECT
    SO.Name
    ,SS.name
    ,SO.type_desc
    ,SO.create_date
    ,SO.modify_date
 FROM sys.objects AS SO
INNER JOIN sys.schemas AS SS
    ON SS.schema_id = SO.schema_id
WHERE DATEDIFF(D,modify_date, GETDATE()) < 10
AND TYPE IN ('P','U')

Friday 8 May 2015

search column name in all tables in a database

SELECT t.name AS table_name,SCHEMA_NAME(schema_id) AS schema_name,c.name AS column_nameFROM sys.tables AS tINNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_IDWHERE c.name LIKE '%EmployeeID%'ORDER BY schema_name, table_name;

Wednesday 25 February 2015

Get database name of a table/stored procedure

create table #t (
    DBName sysname not null
)
go
exec sp_msforeachdb 'use [?]; if OBJECT_ID(''schema name.table name/stored procedure name'') is not null insert into #t (DBName) select ''?'''
go
select * from #t
go
drop table #t

Search details of a sql table


To get details of a table

select *
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME like '%tablename%'

To get all dependencies of a table via script

SELECT referencing_schema_name, referencing_entity_name,
 referencing_id, referencing_class_desc
FROM sys.dm_sql_referencing_entities ('dbo.Address', 'OBJECT')
GO

To get stored procedures referencing a table

SELECT name, OBJECT_SCHEMA_NAME(object_id)
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%tablename%'

Monday 16 February 2015

Monday 19 January 2015

Search Text in Stored Procedures and Tables

To Search in stored procedures

SELECT Name as [Stored Procedure Name]
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Product%'
AND OBJECT_DEFINITION(OBJECT_ID) LIKE '%Code%'


To search in tables

https://gallery.technet.microsoft.com/scriptcenter/c0c57332-8624-48c0-b4c3-5b31fe641c58 

Monday 1 December 2014

Execute a SQL function

USE AdventureWorks2012;
GO
-- Declares a variable and sets it to zero.
-- This variable is used to return the results of the function.
DECLARE @ret nvarchar(15)= NULL; 

-- Executes the dbo.ufnGetSalesOrderStatusText function.
--The function requires a value for one parameter, @Status. 
EXEC @ret = dbo.ufnGetSalesOrderStatusText @Status= 5; 
--Returns the result in the message tab.
PRINT @ret;