sql - advanced queries


--Get all column names from table
select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='TableName';

--Get column names with data type
select COLUMN_NAME,DATA_TYPE from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='TableName';

--Search column name from database
SELECT c.name AS ColName, t.name AS TableName
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE '%columnNameToSearch%'   

--Get all user created tables
SELECT NAME FROM sys.objects WHERE TYPE='U'

--Get all view names
SELECT * FROM sys.views

--Get all stored procedure names
SELECT * FROM sys.procedures

--Counting rows for all tables at once
SELECT
[TableName] = so.name,
    [RowCount] = MAX(si.rows)
FROM
    sysobjects so,
    sysindexes si
WHERE
    so.xtype = 'U'
    AND
    si.id = OBJECT_ID(so.name)
GROUP BY
    so.name
ORDER BY
    2 DESC

--Get Comma Separated List of all columns in table
Select TABLE_SCHEMA, TABLE_NAME
, Stuff(
(
Select ',' + C.COLUMN_NAME
From INFORMATION_SCHEMA.COLUMNS As C
Where C.TABLE_SCHEMA = T.TABLE_SCHEMA
    And C.TABLE_NAME = T.TABLE_NAME
Order By C.ORDINAL_POSITION
For Xml Path('')
), 1, 1, '') As Columns
From INFORMATION_SCHEMA.TABLES As T
WHERE T.TABLE_NAME='TableName'

--Generate ADO .net parameters
SELECT 'cmd.Parameters.AddWithValue("@'+COLUMN_NAME+'", '+COLUMN_NAME+');'
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='TableName';

--Retrieve List of All Database names
SELECT Name FROM dbo.sysdatabases OR EXEC sp_databases

--Check default Language of SQL Server
select @@language  AS DefaultLanguage

--Check server name
select @@SERVERNAME  AS ServerName

--Check column having NULL values only from a table
DECLARE @col varchar(255), @cmd varchar(max)

DECLARE getinfo cursor for
SELECT c.name FROM sys.tables t JOIN sys.columns c ON t.Object_ID = c.Object_ID
WHERE t.Name = 'TableName'

OPEN getinfo

FETCH NEXT FROM getinfo into @col

WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @cmd = 'IF NOT EXISTS (SELECT top 1 * FROM TableName WHERE [' + @col + '] IS NOT NULL) BEGIN print ''' + @col + ''' end'
    EXEC(@cmd)

    FETCH NEXT FROM getinfo into @col
END

CLOSE getinfo
DEALLOCATE getinfo

--Get list of tables without primary key
SELECT SCHEMA_NAME(schema_id) AS SchemaName,name AS TableName
FROM sys.tables
WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0
ORDER BY SchemaName, TableName;

--Get list of tables without having identity column
SELECT 
TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 
WHERE
Table_NAME NOT IN 
( 
SELECT DISTINCT c.TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS c 
INNER 
JOIN sys.identity_columns ic 
on 
(c.COLUMN_NAME=ic.NAME)) 
AND 
TABLE_TYPE ='BASE TABLE'

--Truncate all tables using single query
EXEC sp_MSforeachtable 'TRUNCATE TABLE ?'

--Delete all records from all tables having foreign keys
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable 'DELETE FROM ?'
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

--Drop all tables
EXEC sys.sp_MSforeachtable @command1 = 'Drop Table ?'

--Reset identity seed after deleting records in SQL Server
DBCC CHECKIDENT ('[TableName]', RESEED, 0);

--Rename Database
EXEC sp_renamedb 'oldName', 'newName'

--Rename Table
EXEC sp_rename 'OldTableName', 'NewTableName'

--Rename Table Column
EXEC sp_rename 'TableName.OldColumnName' , 'NewColumnName', 'COLUMN'

--Check SQL Server version
SELECT @@version

--Get list of hard drives with free space
EXEC master..xp_fixeddrives

--Display Text of Stored Procedure, Trigger, View
exec sp_helptext @objname = 'getInfoFromTable'  -- Here getInfoFromTable is my storedprocedure name

--Get List of Primary Key and Foreign Key for a particular table
SELECT  DISTINCT 
Constraint_Name AS [ConstraintName], 
Table_Schema AS [Schema], 
Table_Name AS [Table Name] FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME='TableName'

--Get List of Primary Key and Foreign Key of entire database
SELECT  DISTINCT 
Constraint_Name AS [ConstraintName], 
Table_Schema AS [Schema], 
Table_Name AS [Table Name] FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 

--Disable/Enable Trigger
ALTER TABLE TableName (DISABLE/ENABLE) TRIGGER TriggerName   -- one
ALTER TABLE TableName (DISABLE/ENABLE) TRIGGER ALL           -- all

--Make database offline or online
ALTER DATABASE [ForTesting] SET (OFFLINE/ONLINE)

--Backup Database
BACKUP DATABASE DataBaseName TO DISK='d:\NameOfBackupFile.bak'

--Restore Database
RESTORE DATABASE DataBaseName TO DISK='d:\NameOfBackupFile.bak'

--Search string in a single table
SELECT * FROM TableName WHERE Name LIKE '%TextToSearch%'

--Search string in all tables
DECLARE @SearchStr nvarchar(100)='textToSearch'
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(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )
    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
    BEGIN
        SET @ColumnName =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM     INFORMATION_SCHEMA.COLUMNS
            WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
                AND    QUOTENAME(COLUMN_NAME) > @ColumnName
        )
        IF @ColumnName IS NOT NULL
        BEGIN
            INSERT INTO #Results
            EXEC
            (
                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
                FROM ' + @TableName + 'WITH (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
        END
    END   
END
SELECT ColumnName, ColumnValue FROM #Results


--source: http://www.codingfusion.com/post/75-Important-queries-in-SQL-Server-every-developer-should-know#.Ws2ZzyibD2M.linkedinhttp://www.codingfusion.com/post/75-Important-queries-in-SQL-Server-every-developer-should-know#.Ws2ZzyibD2M.linkedin

Comments

Post a Comment

Popular posts from this blog

Download file from input type file javascript

        <script>             function downloadFile() {                 if (!window.File || !window.FileReader || !window.FileList || !window.Blob) {                     alert('The File APIs are not fully supported in this browser.');                     return;                 }                 input = document.getElementById('fileinput');                 if (!input) {                     alert("Um, couldn't find the fileinput element.");                 }                 else if (!input.files) {           ...
Read more

POST an array of objects with $.ajax to C# WebMethod

//Create JS method invoking your C# WebMethod (example: getNames)     function getFstNames() {         var myObj = [             { 'fstName': 'name 1', 'lastName':'last name 1', 'age': 32 }           , { 'fstName': 'name 2', 'lastName':'last name 1', 'age': 33 }         ];         var postData = JSON.stringify({ lst: myObj });         console.log(postData);         $.ajax({             type: "POST",             url: "http://yourUrl.aspx/getNames",             data: postData,             contentType: "application/json; charset=utf-8",             dataType: "json",             success: function (response) {   ...
Read more

SQL Views - Variables

CREATE VIEW VW_X AS WITH MyVars (AnoInit, AnoEnd)   AS ( SELECT   2010 AS 'AnoInit',   (SELECT TOP 1 AnoLetivo FROM TB_Anos) AS 'AnoEnd'   ) SELECT               AnoX,               (SELECT AnoInit  FROM MyVars) as AnoInit,              (SELECT AnoEnd  FROM MyVars) as AnoEnd  FROM TABLE GO
Read more