Retrieving All Column Names And Types From SQL Server 2005 For .NET

To Retrive All tables Columns with there data type:-

SELECT   schemas.name AS [Schema],
        tables.name AS [Table],
        columns.name AS [Column],
        CASE
            WHEN columns.system_type_id = 34    THEN 'byte[]'
            WHEN columns.system_type_id = 35    THEN 'string'
            WHEN columns.system_type_id = 36    THEN 'System.Guid'
            WHEN columns.system_type_id = 48    THEN 'byte'
            WHEN columns.system_type_id = 52    THEN 'short'
            WHEN columns.system_type_id = 56    THEN 'int'
            WHEN columns.system_type_id = 58    THEN 'System.DateTime'
            WHEN columns.system_type_id = 59    THEN 'float'
            WHEN columns.system_type_id = 60    THEN 'decimal'
            WHEN columns.system_type_id = 61    THEN 'System.DateTime'
            WHEN columns.system_type_id = 62    THEN 'double'
            WHEN columns.system_type_id = 98    THEN 'object'
            WHEN columns.system_type_id = 99    THEN 'string'
            WHEN columns.system_type_id = 104   THEN 'bool'
            WHEN columns.system_type_id = 106   THEN 'decimal'
            WHEN columns.system_type_id = 108   THEN 'decimal'
            WHEN columns.system_type_id = 122   THEN 'decimal'
            WHEN columns.system_type_id = 127   THEN 'long'
            WHEN columns.system_type_id = 165   THEN 'byte[]'
            WHEN columns.system_type_id = 167   THEN 'string'
            WHEN columns.system_type_id = 173   THEN 'byte[]'
            WHEN columns.system_type_id = 175   THEN 'string'
            WHEN columns.system_type_id = 189   THEN 'long'
            WHEN columns.system_type_id = 231   THEN 'string'
            WHEN columns.system_type_id = 239   THEN 'string'
            WHEN columns.system_type_id = 241   THEN 'string'
            WHEN columns.system_type_id = 241   THEN 'string'
        END AS [Type],
        columns.is_nullable AS [Nullable]


FROM              sys.tables tables
   INNER JOIN    sys.schemas schemas ON (tables.schema_id = schemas.schema_id )
   INNER JOIN    sys.columns columns ON (columns.object_id = tables.object_id)


WHERE     tables.name <> 'sysdiagrams'
   AND   tables.name <> 'dtproperties'

ORDER BY [Schema], [Table], [Column], [Type]

--===============================

To Retrive Individual table columns with there datatypes :-

SELECT OBJECT_NAME(c.id) AS table_name, 
    c.name, t.name AS data_type, 
    c.isnullable, com.text AS default_text, 
    c.length, c.prec AS numeric_precision,
    c.scale AS numeric_scale, c.colorder
FROM sys.syscolumns AS c LEFT OUTER JOIN
  sys.systypes AS t ON c.type = t.type AND 
  c.xtype = t.xtype LEFT OUTER JOIN
 sys.syscomments AS com ON com.id = c.cdefault
WHERE (OBJECT_NAME(c.id) = 'Your_Table_Name')
ORDER BY c.colorder

Use With Clause in Sql

So here I am writing even more documentation for my current Gig, and thinking once again, why not post it to OraFAQ and get double duty out of the document. So here is a discussion of the WITH clause that comes with the SELECT statement now. It is easy to use, and handy as all get out, but seems many people have not yet noticed it. Hmm... a SELECT statement that does not start with SELECT.

I like examples as a learning tools, so lets start off with some seemingly silly code.

WITH
  stupid_is_as_stupid_does as (
                               select *from tbldual
                              )
select *
from stupid_is_as_stupid_does
/

tbldual
----------
X

1 row selected.

Note : You Must be select the With table after the execution o.w. you will get error like ) bracket not closed

How To Create a New Login Name in SQL Server?

To create a new login name, you can use the "CREATE LOGIN" statement in a simple syntax like this:

CREATE LOGIN login_name WITH PASSWORD = 'password'  

How To List All Login Names on the Server?

If you want to see a list of all login names defined on the server, you can use the system view, sys.server_principals as shown in this tutorial exercise:

-- Login with sa    SELECT name, sid, type, type_desc FROM sys.server_principals  WHERE type = 'S';  

How To Change the Password of a Login Name?

If a developer lost the password of his or her login name, you can reset the password with the "ALTER LOGIN" statement as shown in this tutorial example:

-- Login with sa    ALTER LOGIN my_DBA WITH PASSWORD = 'mrurli222'; 

How To Change a Login Name?

If you want to change a login name, you can use the "ALTER LOGIN" statement as shown in this tutorial example:

-- Login with "sa"    -- Change login name  ALTER LOGIN Fyi_Login WITH NAME = Dba_Login;  

How To Disable a Login Name?

If you want temporarily disable a login name, you can use the "ALTER LOGIN" statement with a DISABLE keyword. If you want to enable it later on, you can use the ENABLE keyword. The tutorial exercise below shows how to disable and enable login name "Dba_Login":

-- Login with "sa"    -- Disable a login  ALTER LOGIN Fyi_Login DISABLE;    
-- View login status  SELECT name, type, type_desc, is_disabled   FROM sys.server_principals   WHERE type = 'S';  

How To Delete a Login Name?

If you don't want to keep a login name any more, you should delete it by using the "DROP LOGIN" statement as shown in this tutorial example:

-- Login with "sa"    DROP LOGIN Dba_Login;  

How To Create a User Name in a Database?

User names are security principals at the database level. If you want to allow a login name to access a specific database, you need to create a user name in that database and link it to the login name.

Creating a user name can be done by using the "CREATE USER" statement as shown in this tutorial exercise:

-- Login with "sa"    -- Create a login  CREATE LOGIN Fyi_Login WITH PASSWORD = 'IYF'  GO    -- Select a database  USE FyiCenterData;  GO    -- Create a user and link it to a login  CREATE USER Fyi_User FOR LOGIN Fyi_Login;  GO  

Login name "Fyi_Login" should be able to access database "FyiCenterData" through user name "Fyi_User".

How To List All User Names in a Database?

If you want to see a list of all user names defined in a database, you can use the system view, sys.database_principals as shown in this tutorial exercise:

-- Login with sa    -- Select a database  USE FyiCenterData;  GO    -- List all user names  SELECT name, sid, type, type_desc      FROM sys.database_principals WHERE type = 'S';  

How To Delete an Existing Database User?

If you don't want to keep a database user any more, you should delete the user by using the "DROP USER" statement. This tutorial exercise shows how to delete "Dba_User":

-- Login with "sa"    USE FyiCenterData;  GO    DROP USER Dba_User;  

Stored procedure execution on SQL Server startup

I have studied a very interesting topic in SQL Server and wish to blog the same in my site.
Scenario:
On each SQL Server database startup, I need to execute a procedure in my database. It's a very basicscenario in all places.

Solution:

For this, SQL Server is providing an option of using a system stored procedure sp_procoption

create procedure Murli_Insert_Procedure

as

begin

insert into tblMurli(id,val) values (5,'F')

end

EXEC sp_procoption @ProcName = 'Murli_Insert_Procedure',@OptionName = 'startup',@OptionValue = 'true'

Now, your stored procedure is set as a initial startup which will execute on DB start.

Temporary table Vs Temporary variable in SQL Server

We have seen lot of difference between temporary variable and temporary table. Here is a nice difference in Transaction perspective.

Temporary table is transaction dependent and it abides to the database transaction whereas temporary variable is not transaction bound.


Sample Query:

---------------------Temporary table -------------------------------

drop table #temp

create table #temp (id int, val varchar(100))

begin tran ins

insert into #temp values (1,'Venkat')

rollback tran ins

select * from #temp

We are not getting any records indicating the temporary table will bound to the transaction strategies.

------------------Temporary variable --------------------------

Declare @tempval table(id int, val varchar(100))

begin tran ins

insert into @tempval values (1,'Venkat')

rollback tran ins

select * from @tempval

Even we have provided rollback transaction. Records are available in the table variable.

list column names for a table

To list the column names for a table:

In SQL Server, we can retrieve these details by two methods.

1. This can be taken from the sys.objects and sys.columns table (Used in SQL Server 2000 and higher versions).
2. Another option is to use INFORMATION_SCHEMA.COLUMNS (This is introduced in SQL Server 2005 and higher version)

Below is the query to achieve the same,

drop table venkat_table
go
create table venkat_table (id int,val varchar(100),val1 varchar(100))

First Option:

SELECT *FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='venkat_table'

Second Option:

Joining sys.objects and sys.columns table based on the object Id.

SELECT C.name,O.* FROM sys.objects O INNER JOIN sys.columns CON O.object_id=C.object_id WHERE O.name='venkat_table'

View Log file information in SQL Server

Scenario:

Usually, we will face log space issue in our SQL Server. Most of the time, we dont know the reason for our logfile space. I would suggest to get a third party tool to check it. By reading through some of the forums. I found there is some undocumented DBCC command which is used to dig the log file and get some information to us.

This command won't give a very great information at least we can get some high level information. Let's see the command,

DBCC Log(Databasename, Option number)

Default option number is 0

For instance,

dbcc log ('master',0) - 0 indicates a very minimal information.

The above command will provide the below details,