Add $ symbol to column values

To add $ symbol to column values and convert the column values to western number system

Example 1.

declare @value int = 4255

select '$ ' + replace(convert(varchar(100), convert(money, @value), 1), '.00', '')

-- output: $ 4,255

Example 2

declare @value Decimal(18,2)= 5488.00

select '$ ' + convert(varchar(100), convert(money, @value), 1)

-- output: $ 5488.00

What are the pros/cons of using a synonym vs. a view?

They are different things. A synonym is an alias for the object directly, a view is a construct over one or more tables.

Some reasons to use a view:

May need to filter, join or otherwise frig with the structure and semantics of the result set

May need to provide legacy support for an underlying structure that has changed but has dependencies that you do not want to re-work.

May provide security where some of the contents of the table should be visible to a class of users but not all. This could involve removing columns with sensitive information or filtering out a subset of the records.

May wish to encapsulate some business logic in a form that is accessible to users for reporting purposes.

You may wish to unify data from more than one source.

Reasons to use a synonym:

You may wish to alias an object in another database, where you can't (or don't want to) hard code the reference to the specific database.

You may wish to redirect to a source that changes over time, such as an archive table.

You want to alias something in a way that does not affect the query optimiser.

syntax: -


Create View vwtablename

as 

Select *  from tablename  where CreatedOn>'2014-01-26'

union

Select *  from tablename  where CreatedOn<='2014-01-26'

go
select * from vwtablename
go
Drop  View vwtablename

Synonym is just a second name of table used for multiple link of database.

View can be created with many tables, and with virtual columns and with conditions. But synonym can be on view.

Alias is temporary and used with one query. Synonym is permanent and not used as alias.


With a view, I can

hide columns
add predicates (WHERE clause) to restrict rows
rename columns
give a column name to a SQL expression
With a synonym, I can:

reference objects in other schemas and databases without qualifying the name

Javacript: Set or Update a URL/QueryString Parameter, and update URL

Method1 :-

// Explicitly save/update a url parameter using HTML5's replaceState().
function updateQueryStringParam(param, value) {
  baseUrl = [location.protocol, '//', location.host, location.pathname].join('');
  urlQueryString = document.location.search;
  var newParam = key + '=' + value,
  params = '?' + newParam;

  // If the "search" string exists, then build params from it
  if (urlQueryString) {
    keyRegex = new RegExp('([\?&])' + key + '[^&]*');
    // If param exists already, update it
    if (urlQueryString.match(keyRegex) !== null) {
      params = urlQueryString.replace(keyRegex, "$1" + newParam);
    } else { // Otherwise, add it to end of query string
      params = urlQueryString + '&' + newParam;
    }
  }
  window.history.replaceState({}, "", baseUrl + params);
}


Method2:-

var Inputurl ="http://www.example.com/MP/Test.aspx?ID=2&Name=Kumar&Dept=N&Loc=Chennai"

// Function Implemented
 var OutputUrl=replaceKeyValue(Inputurl , "Name", "Karthik");

// Output Url
Result Url as below

http://www.example.com/MP/Test.aspx?ID=2&Name=Karthik&Dept=N&Loc=Chennai
 
// Javascript Function

function replaceKeyValue(url, keyName, newValue) {
            var urlParts = url.split("?");
            if (urlParts.length == 2) {
                var queryString = urlParts[1];
                queryString = queryString.split("&");
                var newQueryString = [];
                if (queryString.length > 0) {
                    $.each(queryString, function (i, e) {
                        var keyValue = e.split("=");
                        if (keyValue.length == 2 && $.trim(keyValue[0]) == keyName) {
                            keyValue[1] = newValue
                        }
                        newQueryString.push(keyValue.join("="));
                    })
                }
                return urlParts[0] + "?" + newQueryString.join("&");
            }
            return url;
        }

sp_executesql

Executes a Transact-SQL statement or batch that can be reused many times, or one that has been built dynamically. The Transact-SQL statement or batch can contain embedded parameters.

Syntax:
 EXECUTE sp_executesql 
          N'SELECT * FROM Book 
          WHERE BookID= @bookid', 
          N'@bookId int', 
          @bookId= 100;  

MEAN Stack

 MEAN Stack a combination of open source JavaScript framework

MEAN is actually combination of 3 JavaScript frameworks and a NoSQL or document based Database technology

M is for MongoDB

MongoDB is an open source, document-oriented database designed with both scalability and developer agility in mind. Instead of storing your data in tables and rows as you would with a relational database, in MongoDB you store JSON-like documents with dynamic schemas.

E is for ExpressJS

Express.js is a Node.js web application server framework, designed for building single-page, multi-page, and hybrid web applications. It is the de facto standard server framework for node.js.

A is for AngularJS

AngularJS is a structural framework for dynamic web apps. It lets you use HTML as your template language and lets you extend HTML's syntax to express your application's components clearly and succinctly.Angular's data binding and dependency injection eliminate much of the code you would otherwise have to write.

N is for NodeJS

nodejs.org. Node.js is an open-source, cross-platform runtime environment for developing server-side web applications. Node.js applications are written in JavaScript and can be run within the Node.js runtime on OS X, Microsoft Windows, Linux, FreeBSD, NonStop, IBM AIX, IBM System z and IBM.

MEAN is

The term MEAN stack refers to a collection of JavaScript based technologies used to develop web applications. MEAN is an acronym for MongoDB, ExpressJS, AngularJS and Node.js. From client to server to database, MEAN is full stack JavaScript.

How to Find Database Changes in SQL Server

-- Find Stored Procedure Changes

SELECT create_date, modify_date, name FROM sys.procedures
ORDER BY modify_date DESC

-- Find user Defined function Changes
SELECT name, create_date, modify_date
FROM sys.objects
WHERE type = 'fn' ORDER BY modify_date DESC


SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = N'FUNCTION' and ROUTINE_SCHEMA = N'dbo'
order by  LAST_ALTERED desc

SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = N'FUNCTION' and ROUTINE_SCHEMA = N'dbo'
order by  CREATED desc






-- Find modified and new added columns list from particular table In sql server 2008

 SELECT
    OBJECT_NAME(sc.[object_id]) as [table]
    ,sc.[name] as [column]
    ,so.modify_date
    ,so.create_date
  FROM [sys].[columns] sc
  JOIN [sys].[objects] so
  ON sc.[object_id] = so.[object_id]
  ORDER BY so.modify_date DESC, so.create_date ASC

-- Find Count Of User Defined Table and Stored Procedure in particular Database.

SELECT
    CASE TYPE
        WHEN 'U'
            THEN 'User Defined Tables'
        WHEN 'S'
            THEN 'System Tables'
        WHEN 'IT'
            THEN 'Internal Tables'
        WHEN 'P'
            THEN 'Stored Procedures'
        WHEN 'PC'
            THEN 'CLR Stored Procedures'
        WHEN 'X'
            THEN 'Extended Stored Procedures'
    END,
    COUNT(*)   
FROM SYS.OBJECTS
WHERE TYPE IN ('U', 'P', 'PC', 'S', 'IT', 'X')
GROUP BY TYPE

---
How to identify all stored procedures referring a particular table


select distinct [Table Name] = o.Name, [Found In] = sp.Name, sp.type_desc
  from sys.objects o inner join sys.sql_expression_dependencies  sd on o.object_id = sd.referenced_id
                inner join sys.objects sp on sd.referencing_id = sp.object_id
                    and sp.type in ('P', 'FN')
  where o.name = 'TestTable'
  order by sp.Name



Find all stored procedures that reference a specific column in some table


SELECT DISTINCT OBJECT_NAME(OBJECT_ID),
object_definition(OBJECT_ID)
FROM sys.Procedures
WHERE object_definition(OBJECT_ID) LIKE '%' + 'ColumnName' + '%'
GO

Find all table names with column name?

SELECT DISTINCT OBJECT_NAME(OBJECT_ID),
object_definition(OBJECT_ID)
FROM sys.Procedures
WHERE object_definition(OBJECT_ID) LIKE '%' + 'ColumnName' + '%'
GO


Need to list all triggers in SQL Server database with table name and table's schema

SELECT
     sysobjects.name AS trigger_name
    ,USER_NAME(sysobjects.uid) AS trigger_owner
    ,s.name AS table_schema
    ,OBJECT_NAME(parent_obj) AS table_name
    ,OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate
    ,OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete
    ,OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert
    ,OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter
    ,OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof
    ,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled]
FROM sysobjects

INNER JOIN sysusers
    ON sysobjects.uid = sysusers.uid

INNER JOIN sys.tables t
    ON sysobjects.parent_obj = t.object_id

INNER JOIN sys.schemas s
    ON t.schema_id = s.schema_id

WHERE sysobjects.type = 'TR'
EDIT: Commented out join to sysusers for query to work on AdventureWorks2008.

SELECT
     sysobjects.name AS trigger_name
    ,USER_NAME(sysobjects.uid) AS trigger_owner
    ,s.name AS table_schema
    ,OBJECT_NAME(parent_obj) AS table_name
    ,OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate
    ,OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete
    ,OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert
    ,OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter
    ,OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof
    ,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled]
FROM sysobjects
/*
INNER JOIN sysusers
    ON sysobjects.uid = sysusers.uid
*/
INNER JOIN sys.tables t
    ON sysobjects.parent_obj = t.object_id

INNER JOIN sys.schemas s
    ON t.schema_id = s.schema_id
WHERE sysobjects.type = 'TR'
EDIT 2: For SQL 2000

SELECT
     o.name AS trigger_name
    ,'x' AS trigger_owner
    /*USER_NAME(o.uid)*/
    ,s.name AS table_schema
    ,OBJECT_NAME(o.parent_obj) AS table_name
    ,OBJECTPROPERTY(o.id, 'ExecIsUpdateTrigger') AS isupdate
    ,OBJECTPROPERTY(o.id, 'ExecIsDeleteTrigger') AS isdelete
    ,OBJECTPROPERTY(o.id, 'ExecIsInsertTrigger') AS isinsert
    ,OBJECTPROPERTY(o.id, 'ExecIsAfterTrigger') AS isafter
    ,OBJECTPROPERTY(o.id, 'ExecIsInsteadOfTrigger') AS isinsteadof
    ,OBJECTPROPERTY(o.id, 'ExecIsTriggerDisabled') AS [disabled]
FROM sysobjects AS o
/*
INNER JOIN sysusers
    ON sysobjects.uid = sysusers.uid
*/
INNER JOIN sysobjects AS o2
    ON o.parent_obj = o2.id

INNER JOIN sysusers AS s
    ON o2.uid = s.uid

WHERE o.type = 'TR'

Need to list all triggers in SQL Server database with table name and table's schema

SELECT
     sysobjects.name AS trigger_name
    ,USER_NAME(sysobjects.uid) AS trigger_owner
    ,s.name AS table_schema
    ,OBJECT_NAME(parent_obj) AS table_name
    ,OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate
    ,OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete
    ,OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert
    ,OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter
    ,OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof
    ,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled]
FROM sysobjects

INNER JOIN sysusers
    ON sysobjects.uid = sysusers.uid

INNER JOIN sys.tables t
    ON sysobjects.parent_obj = t.object_id

INNER JOIN sys.schemas s
    ON t.schema_id = s.schema_id

WHERE sysobjects.type = 'TR'
EDIT: Commented out join to sysusers for query to work on AdventureWorks2008.

SELECT
     sysobjects.name AS trigger_name
    ,USER_NAME(sysobjects.uid) AS trigger_owner
    ,s.name AS table_schema
    ,OBJECT_NAME(parent_obj) AS table_name
    ,OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate
    ,OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete
    ,OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert
    ,OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter
    ,OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof
    ,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled]
FROM sysobjects
/*
INNER JOIN sysusers
    ON sysobjects.uid = sysusers.uid
*/ 
INNER JOIN sys.tables t
    ON sysobjects.parent_obj = t.object_id

INNER JOIN sys.schemas s
    ON t.schema_id = s.schema_id
WHERE sysobjects.type = 'TR'
EDIT 2: For SQL 2000

SELECT
     o.name AS trigger_name
    ,'x' AS trigger_owner
    /*USER_NAME(o.uid)*/
    ,s.name AS table_schema
    ,OBJECT_NAME(o.parent_obj) AS table_name
    ,OBJECTPROPERTY(o.id, 'ExecIsUpdateTrigger') AS isupdate
    ,OBJECTPROPERTY(o.id, 'ExecIsDeleteTrigger') AS isdelete
    ,OBJECTPROPERTY(o.id, 'ExecIsInsertTrigger') AS isinsert
    ,OBJECTPROPERTY(o.id, 'ExecIsAfterTrigger') AS isafter
    ,OBJECTPROPERTY(o.id, 'ExecIsInsteadOfTrigger') AS isinsteadof
    ,OBJECTPROPERTY(o.id, 'ExecIsTriggerDisabled') AS [disabled]
FROM sysobjects AS o
/*
INNER JOIN sysusers
    ON sysobjects.uid = sysusers.uid
*/ 
INNER JOIN sysobjects AS o2
    ON o.parent_obj = o2.id

INNER JOIN sysusers AS s
    ON o2.uid = s.uid

WHERE o.type = 'TR'