Most useful sql queries


To get rowcount of each table within a particular database along with some other attributes like schema name :

SELECT '[' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + ']' AS fulltable_name,
SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name, i.rows
FROM sys.tables AS t
INNER JOIN sys.sysindexes AS i ON t.object_id = i.id AND i.indid < 2 order by i.rows desc


To get table having presence of a particular text
declare @text varchar(255)
set @text = 'Vip'
select 'select * from ' + rtrim(tbl.name) + ' where ' +
          rtrim(col.name) + ' like %' + rtrim(@text) + '%'
from sysobjects tbl
inner join syscolumns col on tbl.id = col.id
and col.xtype in (167, 175, 231, 239) -- (n)char and (n)varchar, there may be others to include
and col.length > 30 -- arbitrary min length into which you might store a URL
where tbl.type = 'U'

To get all tables within a database along with each column in each table , its datatype and IsNUll attribute :

SELECT sh.name+'.'+o.name AS TableName ,s.name as ColumnName
        ,CASE
             WHEN t.name IN ('char','varchar') THEN t.name+'('+CASE WHEN s.max_length<0 then 'MAX' ELSE CONVERT(varchar(10),s.max_length) END+')'
             WHEN t.name IN ('nvarchar','nchar') THEN t.name+'('+CASE WHEN s.max_length<0 then 'MAX' ELSE CONVERT(varchar(10),s.max_length/2) END+')'
            WHEN t.name IN ('numeric') THEN t.name+'('+CONVERT(varchar(10),s.precision)+','+CONVERT(varchar(10),s.scale)+')'
             ELSE t.name END AS DataType
,CASE
             WHEN s.is_nullable=1 THEN 'NULL'
            ELSE 'NOT NULL' END AS Nullable
    FROM sys.columns  s
        INNER JOIN sys.types                   t ON s.system_type_id=t.user_type_id
        INNER JOIN sys.objects                 o ON s.object_id=o.object_id and o.type_desc = 'USER_TABLE'
        INNER JOIN sys.schemas                sh on o.schema_id=sh.schema_id
        LEFT OUTER JOIN sys.identity_columns  ic ON s.object_id=ic.object_id AND s.column_id=ic.column_id
        LEFT OUTER JOIN sys.computed_columns  sc ON s.object_id=sc.object_id AND s.column_id=sc.column_id
        LEFT OUTER JOIN sys.check_constraints cc ON s.object_id=cc.parent_object_id AND s.column_id=cc.parent_column_id
    ORDER BY sh.name+'.'+o.name,s.column_id

If you want to import data from excel using OpenRowset and sp_configure throws error:
sp_configure 'show advanced options', 1;
RECONFIGURE;
GO  --Added      
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

If you want to get the details of all hosts that are connected to a db with the program to which they are interacting :
SELECT
    c.session_id, c.net_transport, c.encrypt_option,
    c.auth_scheme, s.host_name, s.program_name,
    s.client_interface_name, s.login_name, s.nt_domain,
    s.nt_user_name, s.original_login_name, c.connect_time,
    s.login_time
FROM sys.dm_exec_connections AS c
JOIN sys.dm_exec_sessions AS s
    ON c.session_id = s.session_id

14 comments:

  1. This comment has been removed by a blog administrator.

    ReplyDelete
  2. Thanks for such a great article here. I was searching for something like this for quite a long time and at last, I’ve found it on your blog. It was definitely interesting for me to read about their market situation nowadays.angularjs best training center in chennai | angularjs training in velachery | angularjs training in chennai | angularjs training in omr

    ReplyDelete
  3. Thank you so much for posting this. I really appreciate your work. Keep it up. Great work!Best software training company with placement in Hyderabad

    ReplyDelete
  4. Hi,this is Very Nice information Regarding your Software Company and Beautiful Blog Also. So Np compete Also one of the Ios, Android, Java, Devops, UX, Ui, Chat Bot, Company in Chennai
    If you want any job Regarding above Positions,, Please give to Your Queries and send your Resume Back to this mail: sales@npcompete.com

    ReplyDelete