How to get all tables, Procedures, triggers, objects name in SQL server

Intoduction :-
Hii Friends...

This is an easy way to find tables, procedures, triggers which was made by you or your friends and you don't know the table or procedures name then you can simply fire below queries
with remember name like if your table name is tbl_Employee and procedure name is proc_ManageEmployee and trigger name is tgr_updateEmployee then you can fire query like below queries

1. for tables :  select * from sys.tables where name like '%Employee%'
you will get tbl_Employee  as result.
2. for procedures :  select * from sys.procedures where name like '%Employee%'

you will get proc_ManageEmployee as result.
3. for triggers :  select * from sys.triggers where name like '%Employee%'

you will get tgr_updateEmployee as result.

you can also put your own condition with these conditions like AND Id > 10 OR Mobile=123456789

Thank You !

Read More

How to get a table Script

Intoduction :- 

Hii Friends...

Today I explain how to get a table Script when we not get this manually then we use this code. In this code we only change a table name that you want and execute it . You will get that table Script . 

Step : 1   The Code goes here :->

DECLARE @table_name SYSNAME
SELECT @table_name = 'dbo.Tbl_User_LoginDetail'

DECLARE
      @object_name SYSNAME
    , @object_id INT

SELECT
      @object_name = '[' + s.name + '].[' + o.name + ']'
    , @object_id = o.[object_id]
FROM sys.objects o WITH (NOWAIT)
JOIN sys.schemas s WITH (NOWAIT) ON o.[schema_id] = s.[schema_id]
WHERE s.name + '.' + o.name = @table_name
    AND o.[type] = 'U'
    AND o.is_ms_shipped = 0

DECLARE @SQL NVARCHAR(MAX) = ''

;WITH index_column AS
(
    SELECT
          ic.[object_id]
        , ic.index_id
        , ic.is_descending_key
        , ic.is_included_column
        , c.name
    FROM sys.index_columns ic WITH (NOWAIT)
    JOIN sys.columns c WITH (NOWAIT) ON ic.[object_id] = c.[object_id] AND ic.column_id = c.column_id
    WHERE ic.[object_id] = @object_id
)
SELECT @SQL = 'CREATE TABLE ' + @object_name + CHAR(13) + '(' + CHAR(13) + STUFF((
    SELECT CHAR(9) + ', [' + c.name + '] ' +
        CASE WHEN c.is_computed = 1
            THEN 'AS ' + cc.[definition]
            ELSE UPPER(tp.name) +
                CASE WHEN tp.name IN ('varchar', 'char', 'varbinary', 'binary', 'text')
                       THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS VARCHAR(5)) END + ')'
                     WHEN tp.name IN ('nvarchar', 'nchar', 'ntext')
                       THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length / 2 AS VARCHAR(5)) END + ')'
                     WHEN tp.name IN ('datetime2', 'time2', 'datetimeoffset')
                       THEN '(' + CAST(c.scale AS VARCHAR(5)) + ')'
                     WHEN tp.name = 'decimal'
                       THEN '(' + CAST(c.[precision] AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')'
                    ELSE ''
                END +
                CASE WHEN c.collation_name IS NOT NULL THEN ' COLLATE ' + c.collation_name ELSE '' END +
                CASE WHEN c.is_nullable = 1 THEN ' NULL' ELSE ' NOT NULL' END +
                CASE WHEN dc.[definition] IS NOT NULL THEN ' DEFAULT' + dc.[definition] ELSE '' END +
                CASE WHEN ic.is_identity = 1 THEN ' IDENTITY(' + CAST(ISNULL(ic.seed_value, '0') AS CHAR(1)) + ',' + CAST(ISNULL(ic.increment_value, '1') AS CHAR(1)) + ')' ELSE '' END
        END + CHAR(13)
    FROM sys.columns c WITH (NOWAIT)
    JOIN sys.types tp WITH (NOWAIT) ON c.user_type_id = tp.user_type_id
    LEFT JOIN sys.computed_columns cc WITH (NOWAIT) ON c.[object_id] = cc.[object_id] AND c.column_id = cc.column_id
    LEFT JOIN sys.default_constraints dc WITH (NOWAIT) ON c.default_object_id != 0 AND c.[object_id] = dc.parent_object_id AND c.column_id = dc.parent_column_id
    LEFT JOIN sys.identity_columns ic WITH (NOWAIT) ON c.is_identity = 1 AND c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id
    WHERE c.[object_id] = @object_id
    ORDER BY c.column_id
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, CHAR(9) + ' ')
    + ISNULL((SELECT CHAR(9) + ', CONSTRAINT [' + k.name + '] PRIMARY KEY (' +
                    (SELECT STUFF((
                         SELECT ', [' + c.name + '] ' + CASE WHEN ic.is_descending_key = 1 THEN 'DESC' ELSE 'ASC' END
                         FROM sys.index_columns ic WITH (NOWAIT)
                         JOIN sys.columns c WITH (NOWAIT) ON c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id
                         WHERE ic.is_included_column = 0
                             AND ic.[object_id] = k.parent_object_id
                             AND ic.index_id = k.unique_index_id   
                         FOR XML PATH(N''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, ''))
            + ')' + CHAR(13)
            FROM sys.key_constraints k WITH (NOWAIT)
            WHERE k.parent_object_id = @object_id
                AND k.[type] = 'PK'), '') + ')'  + CHAR(13)

PRINT @SQL

Read More

How To Make Tables In Sql

Introduction:-

Hii Friends...

Today I explain how to create tables in sql. The first thing is what is sql ? The sql is the Structured Query Language. And in sql we can create many types of tables so i explain all types of tables.

Step:1    Firstly we create a table then we apply all types of queries on this table and give it a name. I give it  (customer) name.

Step:2    Now we can apply all queries.

1) Select :-

SELECT * FROM Customers;

Syntax :-

SELECT column_name,column_name
FROM table_name;

Examples:-

SELECT CustomerName,City FROM Customers;

Output:- All CustomerName and city will show.


How To Add a column in predefine Table 

 ALTER TABLE tblSubCategory
    ADD   Size varchar(50)

             OR

Alter table tblShopping_SellerProduct
Add EstimatedDays int not null default 0


Output:- Two extra columns are shows.

 How To Make a query for date filtering

 select * from tblMLM_EWalletTransaction where CONVERT(Date,Adddate,103) between CONVERT(Date,'" + txtfromdateEwallet.Text + "',103) and CONVERT(Date,'" + txttodateEwallet.Text + "',103)");


How To Make a Subquery 

select *,(select Firstname from tblShopping_Customer Where Msrno=s.SenderMsrno) as SenderName,
(select Firstname from tblShopping_Customer Where Msrno=s.ReceiverMsrno) as ReceiverName from tblsms as S

Read More